nuBuilder Forum

After nubuilder update I get two error messages in zzzzsys_debug:

[0] : ===USER==========
globeadmin
===PDO MESSAGE===
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'grodt4.zzzzsys_object_list' doesn't exist
===SQL===========
CREATE ALGORITHM=UNDEFINED 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` 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`
===BACK TRACE====
/srv/http/grodt4/core/nusystemupdatelibs.php - line 63 (nuRunQuery)
/srv/http/grodt4/core/nusystemupdate.php - line 83 (nuImportSystemFiles)
/srv/http/grodt4/core/nusystemupdate.php - line 23 (nuRunUpdate)

[0] : ===USER==========
globeadmin
===PDO MESSAGE===
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'information_schema.tables.TABLE_NAME' in 'field list'
===SQL===========
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_object_list` AS SELECT `information_schema`.`tables`.`TABLE_NAME` AS `zzzzsys_object_list_id` FROM `information_schema`.`TABLES` WHERE (`information_schema`.`tables`.`TABLE_SCHEMA` = database())
===BACK TRACE====
/srv/http/grodt4/core/nusystemupdatelibs.php - line 63 (nuRunQuery)
/srv/http/grodt4/core/nusystemupdate.php - line 83 (nuImportSystemFiles)
/srv/http/grodt4/core/nusystemupdate.php - line 23 (nuRunUpdate)

Looking into nubuilder4.sql I found the following statement near line 2419, that causes the second error message:
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_object_list` AS SELECT `information_schema`.`tables`.`TABLE_NAME` AS `zzzzsys_object_list_id` FROM `information_schema`.`TABLES` WHERE (`information_schema`.`tables`.`TABLE_SCHEMA` = database()) ;
Please note the different letter case for the field name tables.
Setting the same letter case in all three locations resolves the problem.

My system setup:
Arch Linux Kernel 6.1.12-arch1-1
MariaDB 10.11.2-MariaDB Arch Linux
Global Variable lower_case_table_names = 0

Maybe it is not "our" problem, but a problem of Mariadb?

Kind regards, Hf

Hi,

My first investigation showed that this might be a phpMyAdmin problem with which I create the SQL dump.
I believe that column names are case sensitive in Windows but are case sensitive in most varieties of Unix.

In any case, the views should still be created when you log in if they do not already exist. Is this the case?

Hi,
thank you for your prompt response. I can only create the view, when table names are given in the same letter case (see mariadb output below), independent if the view exists or not after login.

This problem occurred some weeks ago for the first time. So I looked into nuBuilder' branches on github.
There I found, that up to Tag 4.5.7.2 (Oct,28th 2022) the create views-statement contains the table name in lower case letters only, which gave no error.
Since Tag 4.5.7.3 (Jan,22nd 2023) one of the table name definition changed to upper case letters.

Now I try to repeat the installations beginning with tag 4.5.7.2 ...

Kind regards, Hf


# mariadb -u grodt4 -p grodt4
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.11.2-MariaDB Arch Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [grodt4]> CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_object_list` AS SELECT `information_schema`.`tables`.`TABLE_NAME` AS `zzzzsys_object_list_id` FROM `information_schema`.`TABLES` WHERE (`information_schema`.`tables`.`TABLE_SCHEMA` = database()) ;
ERROR 1054 (42S22): Unknown column 'information_schema.tables.TABLE_NAME' in 'field list'
MariaDB [grodt4]> CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `zzzzsys_object_list` AS SELECT `information_schema`.`tables`.`TABLE_NAME` AS `zzzzsys_object_list_id` FROM `information_schema`.`tables` WHERE (`information_schema`.`tables`.`TABLE_SCHEMA` = database()) ;
Query OK, 0 rows affected (0,059 sec)

MariaDB [grodt4]>

Thank you, problem is solved in latest update of nuBuilder..
Hf