linux, mariadb, lower_case_table_names and nubuilder4.sql
Posted: Tue Feb 21, 2023 4:32 pm
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
[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