zzzzsys_run_list table and view not found

[Fike]

I am getting the following error in 'zzzzsys_debug' table when I open teh 'nuobject' form to edit any given form object:

===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'database_name.zzzzsys_run_list' doesn't exist

===SQL===========

SELECT
id,
code,
CONCAT(run, ' - ', description)
FROM zzzzsys_run_list
WHERE
`id` = ''

It seems that when the system is updated (by clicking the 'Update' button) the 'zzzzsys_run_list' and 'zzzzsys_report_data' tables/views are dropped from the database, but they are not created again.

Is there any php script or sql query that I can run to create these tables/views again (with the needed records) on my existing database?




Edit: I think I found the sql querys in nubuilder4.sql file:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 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')) 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` <> '1') union select concat('TABLE:',`zzzzsys_table`.`zzzzsys_table_id`) AS `id`,'nuTABLE' AS `code`,`zzzzsys_table`.`zzzzsys_table_id` AS `description` from `zzzzsys_table` ;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 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` 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` ;

I ran both querys on my database and the error has gone.


Kind regards

[toms]

Fike,

Just remove this string from nubuilder4.sql (two occurrences) every time before you run an update.

Code: Select all

ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER

OR:

Run these two queries if you notice that these two views are missing after an update:

Code: Select all

CREATE 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')) 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` <> '1') union select concat('TABLE:',`zzzzsys_table`.`zzzzsys_table_id`) AS `id`,'nuTABLE' AS `code`,`zzzzsys_table`.`zzzzsys_table_id` AS `description` from `zzzzsys_table` ;

CREATE  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` 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` ;

Because you run the update without root permissions, the views will not be created.

This issue has been discussed in this thread:
http://forums.nubuilder.cloud/viewtopic.p ... THM#p15463

Discussion on stackoverflow with other approaches to the solution:

https://stackoverflow.com/questions/995 ... -algorithm

[Fike]

Thank's a lot toms!

Kind regrads

[admin]

.