Page 1 of 1
V4.6 latest error creating user
Posted: Sat Apr 05, 2025 10:03 am
by Costa
good morning everyone,
I installed the latest version and created my project with various forms. After that I started to create the users but I get this sql error.
Code: Select all
[0] : ===PDO MESSAGE===
SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'Sql1751210_4.zzzzsys_access.sal_code' which is not in SELECT list; this is incompatible with DISTINCT
===SQL===========
SELECT DISTINCT CONCAT(sal_code,' : ',sal_description) FROM `zzzzsys_user`
INNER JOIN zzzzsys_access ON zzzzsys_access_id = sus_zzzzsys_access_id
WHERE (
(('#nuBrowseTitle9_select#' LIKE '#%' OR '#nuBrowseTitle9_select#' = '') AND (sus_expires_on > NOW() OR sus_expires_on IS NULL)) OR
('#nuBrowseTitle9_select#' = 'Expired' AND sus_expires_on < NOW()))
ORDER BY sal_code
===BACK TRACE====
/web/htdocs/www.loggiaeinstein.it/home/bibliotecagoi/core/nudatabase.php - line 473 (nuRunQuery)
/web/htdocs/www.loggiaeinstein.it/home/bibliotecagoi/core/nucommon.php(1501) : eval()'d code - line 47 (nuEncodeQueryRowResults)
/web/htdocs/www.loggiaeinstein.it/home/bibliotecagoi/core/nucommon.php - line 1501 (eval)
/web/htdocs/www.loggiaeinstein.it/home/bibliotecagoi/core/nucommon.php - line 1539 (nuEvalSafe)
/web/htdocs/www.loggiaeinstein.it/home/bibliotecagoi/core/nuform.php - line 27 (nuEval)
/web/htdocs/www.loggiaeinstein.it/home/bibliotecagoi/core/nuform.php - line 1110 (nuBeforeBrowse)
/web/htdocs/www.loggiaeinstein.it/home/bibliotecagoi/core/nuform.php - line 436 (nuBrowseColumns)
/web/htdocs/www.loggiaeinstein.it/home/bibliotecagoi/core/nuform.php - line 158 (nuGetFormFinalize)
Not understanding if the problem was with the forms, I did a new fresh installation completely separate from the previous one with a different database and if I try to create a new user I always get the same sql error. In addition, the users are created in the db but not shown in the user list.
Db is mysql v8
Any suggestions?
Thanks for any help
Re: V4.6 latest error creating user
Posted: Sat Apr 05, 2025 10:33 am
by kev1n
Run the code below (e.g. in phpMyAdmin) and check if that fixes your issue:
Code: Select all
UPDATE `zzzzsys_php` SET `sph_php` = '$getDistinctUserColumnQuery = function($column) {\r\n\r\n $sqlWhereExpired = \"( \r\n ((\'#nuBrowseTitle9_select#\' LIKE \'#%\' OR \'#nuBrowseTitle9_select#\' = \'\') AND (sus_expires_on > NOW() OR sus_expires_on IS NULL)) OR\r\n (\'#nuBrowseTitle9_select#\' = \'Expired\' AND sus_expires_on < NOW())\r\n ) \";\r\n\r\n return \"\r\n SELECT DISTINCT IFNULL(`$column`, \'\') AS col\r\n FROM `zzzzsys_user`\r\n WHERE IFNULL(`$column`, \'\') <> \'\'\r\n AND $sqlWhereExpired\r\n ORDER BY col\r\n \";\r\n\r\n};\r\n\r\n$sqlPosition = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_position\');\r\n};\r\n\r\n$sqlTeam = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_team\');\r\n};\r\n\r\n$sqlDepartment = function() use ($getDistinctUserColumnQuery) {\r\n $x = $getDistinctUserColumnQuery(\'sus_department\');\r\n return $x;\r\n};\r\n\r\n$sqlLanguage = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_language\');\r\n};\r\n\r\n$sqlAccessLevel = function() {\r\n\r\n $sqlWhereExpired = \"(\r\n ((\'#nuBrowseTitle9_select#\' LIKE \'#%\' OR \'#nuBrowseTitle9_select#\' = \'\') AND (sus_expires_on > NOW() OR sus_expires_on IS NULL)) OR\r\n (\'#nuBrowseTitle9_select#\' = \'Expired\' AND sus_expires_on < NOW()))\";\r\n\r\n $sql = \" SELECT DISTINCT CONCAT(sal_code,\' : \',sal_description) FROM `zzzzsys_user`\r\n INNER JOIN zzzzsys_access ON zzzzsys_access_id = sus_zzzzsys_access_id\r\n WHERE $sqlWhereExpired\r\n ORDER BY sal_code \";\r\n return $sql;\r\n};\r\n\r\n$position = nuEncodeQueryRowResults($sqlPosition(), [], [\'\']);\r\n$team = nuEncodeQueryRowResults($sqlTeam(), [], [\'\']);\r\n$department = nuEncodeQueryRowResults($sqlDepartment(), [], [\'\']);\r\n$language = nuEncodeQueryRowResults($sqlLanguage(), [], [\'\']);\r\n$accessLevel = nuEncodeQueryRowResults($sqlAccessLevel(), [], [\'\']);\r\n\r\n$filterJS = \"\r\n function getData(data) {\r\n return JSON.parse(atob(data));\r\n }\r\n function getPosition() {\r\n return getData(\'$position\');\r\n }\r\n function getTeam() {\r\n return getData(\'$team\');\r\n }\r\n\r\n function getDepartment() {\r\n return getData(\'$department\');\r\n }\r\n function getLanguage() {\r\n return getData(\'$language\');\r\n }\r\n\r\n function getAccessLevel() {\r\n return getData(\'$accessLevel\');\r\n }\r\n\";\r\n\r\n\r\n$addCode = $_SESSION[\'nubuilder_session_data\'][\'USER_CODE_LABEL\'] ?? \'\';\r\n\r\n$addCodeJS = \"\r\n\r\n if (\'$addCode\' !== \'\') {\r\n $(\'#nusort_5\').html(\'$addCode\')\r\n };\r\n\r\n\";\r\n\r\n\r\nnuAddJavaScript($filterJS . $addCodeJS);' WHERE `zzzzsys_php`.`zzzzsys_php_id` = 'nuuser_BB'
MySQL implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.
Re: V4.6 latest error creating user
Posted: Sat Apr 05, 2025 5:13 pm
by Costa
Hi kev1n,
sorry but it doesnt work.
Still same error as soon as i open the form to add a new user
after the query the error message is :
Code: Select all
[0] : ===PDO MESSAGE===
SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'Sql1751210_5.zzzzsys_access.sal_code' which is not in SELECT list; this is incompatible with DISTINCT
===SQL===========
SELECT DISTINCT CONCAT(sal_code,' : ',sal_description) FROM `zzzzsys_user`
INNER JOIN zzzzsys_access ON zzzzsys_access_id = sus_zzzzsys_access_id
WHERE (
(('#nuBrowseTitle9_select#' LIKE '#%' OR '#nuBrowseTitle9_select#' = '') AND (sus_expires_on > NOW() OR sus_expires_on IS NULL)) OR
('#nuBrowseTitle9_select#' = 'Expired' AND sus_expires_on < NOW()))
ORDER BY sal_code
===BACK TRACE====
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nudatabase.php - line 473 (nuRunQuery)
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nucommon.php(1501) : eval()'d code - line 52 (nuEncodeQueryRowResults)
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nucommon.php - line 1501 (eval)
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nucommon.php - line 1539 (nuEvalSafe)
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nuform.php - line 27 (nuEval)
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nuform.php - line 1110 (nuBeforeBrowse)
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nuform.php - line 436 (nuBrowseColumns)
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nuform.php - line 158 (nuGetFormFinalize)
/web/htdocs/www.loggiaeinstein.it/home/biblioteca/core/nuapi.php - line 96 (nuGetFormObject)
Re: V4.6 latest error creating user
Posted: Sat Apr 05, 2025 5:26 pm
by kev1n
There was another SQL that needed fixing. Run this SQL to update the code:
Code: Select all
REPLACE INTO `zzzzsys_php` (`zzzzsys_php_id`, `sph_code`, `sph_description`, `sph_group`, `sph_php`, `sph_run`, `sph_zzzzsys_form_id`, `sph_system`, `sph_global`, `sph_demo`, `sph_template`, `sph_hide`, `sph_json`) VALUES ('nuuser_BB', 'nuuser_BB', 'System PHP', 'nubuilder', '$getDistinctUserColumnQuery = function($column) {\r\n\r\n $sqlWhereExpired = \"(\r\n ((\'#nuBrowseTitle9_select#\' LIKE \'#%\' OR \'#nuBrowseTitle9_select#\' = \'\') AND (sus_expires_on > NOW() OR sus_expires_on IS NULL)) OR\r\n (\'#nuBrowseTitle9_select#\' = \'Expired\' AND sus_expires_on < NOW())\r\n ) \";\r\n\r\n return \"\r\n SELECT DISTINCT IFNULL(`$column`, \'\') AS col\r\n FROM `zzzzsys_user`\r\n WHERE IFNULL(`$column`, \'\') <> \'\'\r\n AND $sqlWhereExpired\r\n ORDER BY col\r\n \";\r\n\r\n};\r\n\r\n$sqlPosition = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_position\');\r\n};\r\n\r\n$sqlTeam = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_team\');\r\n};\r\n\r\n$sqlDepartment = function() use ($getDistinctUserColumnQuery) {\r\n $x = $getDistinctUserColumnQuery(\'sus_department\');\r\n return $x;\r\n};\r\n\r\n$sqlLanguage = function() use ($getDistinctUserColumnQuery) {\r\n return $getDistinctUserColumnQuery(\'sus_language\');\r\n};\r\n\r\n$sqlAccessLevel = function() {\r\n\r\n $sqlWhereExpired = \"(\r\n ((\'#nuBrowseTitle9_select#\' LIKE \'#%\' OR \'#nuBrowseTitle9_select#\' = \'\') AND (sus_expires_on > NOW() OR sus_expires_on IS NULL)) OR\r\n (\'#nuBrowseTitle9_select#\' = \'Expired\' AND sus_expires_on < NOW()))\";\r\n\r\n$sql = \"SELECT DISTINCT CONCAT(sal_code, \' : \', sal_description) AS display\r\n FROM `zzzzsys_user`\r\n INNER JOIN zzzzsys_access ON zzzzsys_access_id = sus_zzzzsys_access_id\r\n WHERE $sqlWhereExpired\r\n ORDER BY CONCAT(sal_code, \' : \', sal_description)\";\r\nreturn $sql;\r\n\r\n};\r\n\r\n$position = nuEncodeQueryRowResults($sqlPosition(), [], [\'\']);\r\n$team = nuEncodeQueryRowResults($sqlTeam(), [], [\'\']);\r\n$department = nuEncodeQueryRowResults($sqlDepartment(), [], [\'\']);\r\n$language = nuEncodeQueryRowResults($sqlLanguage(), [], [\'\']);\r\n$accessLevel = nuEncodeQueryRowResults($sqlAccessLevel(), [], [\'\']);\r\n\r\n$filterJS = \"\r\n function getData(data) {\r\n return JSON.parse(atob(data));\r\n }\r\n function getPosition() {\r\n return getData(\'$position\');\r\n }\r\n function getTeam() {\r\n return getData(\'$team\');\r\n }\r\n\r\n function getDepartment() {\r\n return getData(\'$department\');\r\n }\r\n function getLanguage() {\r\n return getData(\'$language\');\r\n }\r\n\r\n function getAccessLevel() {\r\n return getData(\'$accessLevel\');\r\n }\r\n\";\r\n\r\n\r\n$addCode = $_SESSION[\'nubuilder_session_data\'][\'USER_CODE_LABEL\'] ?? \'\';\r\n\r\n$addCodeJS = \"\r\n\r\n if (\'$addCode\' !== \'\') {\r\n $(\'#nusort_5\').html(\'$addCode\')\r\n };\r\n\r\n\";\r\n\r\n\r\nnuAddJavaScript($filterJS . $addCodeJS);', NULL, NULL, '1', '0', '1', '0', NULL, NULL)
Re: V4.6 latest error creating user
Posted: Sat Apr 05, 2025 5:36 pm
by Costa
Kev1n,
always top solutions!!
Thanks