V4.6 latest error creating user Topic is solved

[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

[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.

[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)

[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)

[Costa]

Kev1n,

always top solutions!!

Thanks