Database View not working after upgrade

[Sue Frost]

We're upgrading from Geneva to Istanbul.

A database view that hasn't been changed in over two years is now not returning any results. I've confirmed that there are no differences between the version in our Geneva instance and the one in our Istanbul instance.

I can't find any release notes for Helsinki or Istanbul to indicate there were changes to database views. There are records that should be showing in both instances.

If someone could point me in the right direction or offer an explanation, that would be very much appreciated.

[nayanawadhiya1]

Hello Sue,

I also faced similar type of issue.

Issue resolved by adding some READ ACLs on that custom table and on Database View.

[Sue Frost]

Nayan & amlanpal ,

I should have mentioned that I did confirm the ACLs exist.

The ACL issue came up when we moved from Eureka to Geneva - it was introduced with Fuji.

[Mwatkins]

I don't see any known issues or changes to Database View behavior from Geneva to Istanbul. I do not know why this would break after the upgrade.

Was there a change to the underlying data? Is some of the data gone? Can you do a test:

1. Turn on System Diagnostics > Session Debug > Enable All

2. Reproduce the issue

3. See if the database is querying the right tables in the right way

It should be doing something like this:

SELECT ... FROM u_attachments attach JOIN u_case cs ON attach.u_task = cs.sys_id JOIN u_communications cm ON cm.u_case = attach.u_task

Also, look in the debug output for any Exceptions from the database.

[Sue Frost]

Matthew,

I validated my data in the Istanbul instance by going directly to the table and matching the same criteria. There is definitely data that should be displaying. (The data would be different from the Geneva instance, but there should BE data.)

I didn't find anything searching on "exception".

Here are the debug statements from both instances.and the only difference I see is that there are two statements in the Geneva version and the " /*...*/ " before the SELECT statement.

So ... a bit of progress but I'm still stumped.

Istanbul:

13:58:37.982: Time: 0:00:00.005 for: ecodevchild3_1[glide.23] SELECT ... FROM (((u_attachments attach INNER JOIN task cs ON cs.`sys_id` = attach.`u_task` AND cs.`sys_class_name` IN ('u_case','u_upcoming_abeyances','u_case_task') ) INNER JOIN task cm ON cm.`a_ref_1` = attach.`u_task` AND cm.`sys_class_name` = 'u_communications' ) LEFT JOIN task task1 ON cm.`a_ref_1` = task1.`sys_id` ) WHERE attach.`u_active` = 1 AND task1.`number` LIKE 'CASE%' AND cs.`u_entity` = 'u_case

Geneva:

13:00:28.2: Time: 0:00:00.006 for: ecostaging_2[glide.1] /*...*/ SELECT ... FROM (((u_attachments attach INNER JOIN task cs ON cs.`sys_id` = attach.`u_task` AND cs.`sys_class_name` IN ('u_case','u_case_task','u_upcoming_abeyances') ) INNER JOIN task cm ON cm.`a_ref_1` = attach.`u_task` AND cm.`sys_class_name` = 'u_communications' ) LEFT JOIN task task1 ON cm.`a_ref_1` = task1.`sys_id` ) WHERE attach.`u_active` = 1 AND task1.`number` LIKE 'CASE%' AND cs.`u_entity` = 'u_case'

13:00:28.20: Time: 0:00:00.008 for: ecostaging_2[glide.3] /*...*/ SELECT ... FROM (((u_attachments attach INNER JOIN task cs ON cs.`sys_id` = attach.`u_task` AND cs.`sys_class_name` IN ('u_case','u_case_task','u_upcoming_abeyances') ) INNER JOIN task cm ON cm.`a_ref_1` = attach.`u_task` AND cm.`sys_class_name` = 'u_communications' ) LEFT JOIN task task1 ON cm.`a_ref_1` = task1.`sys_id` ) WHERE attach.`u_active` = 1 AND task1.`number` LIKE 'CASE%' AND cs.`u_entity` = 'u_case' ORDER BY attach.`u_file_name` limit 0,50