Page 1 of 2
My reports disappeared!
Posted: Tue May 09, 2023 4:11 pm
by luca.ongaro
I was adding a new button in my nuuserhome to open a new report, but (suddenly) in the Run tab of the new object none of my reports show in the list - only forms. So I cannot go on and add this new button.
The other open-report buttons in my nuuserhome work normally, and the reports are there as expected, but if I examine the properties of these buttons the Run field appears to be empty also.
Did I make some mistake?
Thanks
Re: My reports disappeared!
Posted: Tue May 09, 2023 4:39 pm
by kev1n
Check if the reports still exist in the table zzzzsys_report
Re: My reports disappeared!
Posted: Tue May 09, 2023 6:18 pm
by luca.ongaro
Yes they still exist in zzzzsys_report
Re: My reports disappeared!
Posted: Tue May 09, 2023 6:39 pm
by luca.ongaro
... but they don't exist in zzzzsys_run_list, where I suppose they should be.
Re: My reports disappeared!
Posted: Wed May 10, 2023 7:11 am
by kev1n
Try running the nuBuilder update process without downloading updated files to restore the system's forms, code, and tables
Re: My reports disappeared!
Posted: Wed May 10, 2023 8:43 am
by luca.ongaro
Done, but it doesn't solve the issue.
To add details, the problem occurred after I cloned an existing report, doing some minor changes.
Re: My reports disappeared!
Posted: Wed May 10, 2023 8:54 am
by kev1n
The SQL of the zzzzsys_run_list view looks like this:
Code: Select all
SELECT
`zzzzsys_form`.`zzzzsys_form_id` AS `id`,
'Form' AS `run`,
`zzzzsys_form`.`sfo_code` AS `code`,
`zzzzsys_form`.`sfo_description` AS `description`
FROM
`zzzzsys_form`
UNION
SELECT
`zzzzsys_report`.`zzzzsys_report_id` AS `id`,
'Report' AS `run`,
`zzzzsys_report`.`sre_code` AS `code`,
`zzzzsys_report`.`sre_description` AS `description`
FROM
`zzzzsys_report`
UNION
SELECT
`zzzzsys_php`.`zzzzsys_php_id` AS `id`,
'Procedure' AS `run`,
`zzzzsys_php`.`sph_code` AS `code`,
`zzzzsys_php`.`sph_description` AS `description`
FROM
`zzzzsys_php`
WHERE
`zzzzsys_php`.`sph_system` <> 1
ORDER BY
`code`
Are you saying that the report in question appears in the "zzzzsys_report" file, but when running this SQL code, it does not show up?
Code: Select all
SELECT
`zzzzsys_report`.`zzzzsys_report_id` AS `id`,
'Report' AS `run`,
`zzzzsys_report`.`sre_code` AS `code`,
`zzzzsys_report`.`sre_description` AS `description`
FROM
`zzzzsys_report`
Re: My reports disappeared!
Posted: Wed May 10, 2023 9:23 am
by luca.ongaro
I re-created the zzzzsys_run_list view using your SQL, and apparently the problem is solved.
This is the SQL code for zzzzsys_run_list in the dump I used when moving my site from test to production (where I made the clone):
Code: Select all
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_run_list`
AS SELECT `zzzzsys_form`.`zzzzsys_form_id` AS `id`, 'Form' AS `run`, `zzzzsys_form`.`sfo_code` AS `code`,
`zzzzsys_form`.`sfo_description` AS `description` FROM `zzzzsys_form` ;
so probably the problem was already there...
Am I safe now, kev1n?
Re: My reports disappeared!
Posted: Thu May 11, 2023 6:52 pm
by luca.ongaro
Update.
I had a similar problem again, but now I know how to deal with. It is somehow related vith views.
This time, after a export/import sequence of the entire DB, the zzzzsys_report_data view was empty.
Here is the SQL code of the view in the good DB (I think you know it...):
Code: Select all
select concat('PROCEDURE:',`zzzzsys_php`.`zzzzsys_php_id`) AS `id`,`zzzzsys_php`.`sph_code` AS `code`,`zzzzsys_php`.`sph_description`
AS `description` from `zzzzsys_php` where `zzzzsys_php`.`sph_system` <> '1' and locate('#TABLE_ID#',`zzzzsys_php`.`sph_php`) > '0' union select
concat('SQL:',`zzzzsys_select`.`zzzzsys_select_id`) AS `id`,'nuSQL' AS `code`,`zzzzsys_select`.`sse_description` AS `description` from
`zzzzsys_select` where `zzzzsys_select`.`sse_system` is null or `zzzzsys_select`.`sse_system` = '' union select
concat('TABLE:',`zzzzsys_object_list`.`zzzzsys_object_list_id`) AS `id`,'nuTABLE' AS `code`,`zzzzsys_object_list`.`zzzzsys_object_list_id` AS
`description` from `zzzzsys_object_list`
and this is the SQL instruction in the dump file that creates the view:
Code: Select all
CREATE ALGORITHM=UNDEFINED DEFINER=`myusername`@`127.0.0.1` SQL SECURITY DEFINER VIEW `zzzzsys_report_data`
AS SELECT concat('PROCEDURE:',`zzzzsys_php`.`zzzzsys_php_id`) AS `id`, `zzzzsys_php`.`sph_code` AS `code`, `zzzzsys_php`.`sph_description`
AS `description` FROM `zzzzsys_php` WHERE `zzzzsys_php`.`sph_system` <> '1' AND locate('#TABLE_ID#',`zzzzsys_php`.`sph_php`) > '0' ;
At a first glance it seems that the UNION SELECT instructions are stripped away by the export...
What do you think?
Thank you
Re: My reports disappeared!
Posted: Fri May 12, 2023 3:18 am
by kev1n
How do you export?