bug - Fast Report

[homa]

Now, after taking my first steps in nuBuilder forte, I wanted to generate my first report, but I get the following message and a broken interface when I click on Fast Report in the main menu.

2025-09-22 11_02_35-nuBuilder - Fast Report – Mozilla Firefox.png

How can I fix this? My version is from GITHUB dated 18 September 2025.

[homa]

Apologies, I found the error myself! I didn't use the release version. I'm now back on nuBuilder-RELEASE_4.9.2.0 and there are STILL errors.

Darn, I was too trusting. The error BUG at the top in red remains, only the display is now error-free.

So what could it be?

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation 'UNION'

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


INSERT INTO zzzzsys_report_data ( 
 `zzzzsys_report_data_id`,
 `srd_code`,
 `srd_description`)

    SELECT
     CONCAT(
     'PROCEDURE:',
     `zzzzsys_php`.`zzzzsys_php_id`
     ) AS `zzzzsys_php_id`,
     `zzzzsys_php`.`sph_code` ,
     `zzzzsys_php`.`sph_description`
    FROM
     `zzzzsys_php`
    WHERE
     (
     (`zzzzsys_php`.`sph_system` <> '1') 
     AND (LOCATE('#TABLE_ID#', `zzzzsys_php`.`sph_php`) > '0') AND (`zzzzsys_php`.`sph_template` <> 1)
     )
     
    UNION
    
    SELECT
     CONCAT(
     'SQL:',
     `zzzzsys_select`.`zzzzsys_select_id`
     ) AS `id`,
     'nuSQL',
     `zzzzsys_select`.`sse_description`
    FROM
     `zzzzsys_select`
    WHERE
     (
     (
     `zzzzsys_select`.`sse_system` IS NULL
     ) OR(`zzzzsys_select`.`sse_system` = '')
     )
     
    UNION
    
    SELECT
     CONCAT(
     'TABLE:',
     `TABLE_NAME`
     ) AS `id`,
     'nuTABLE',
     `TABLE_NAME` 
    FROM 
     `information_schema`.`tables` 
    WHERE 
     `TABLE_SCHEMA` = database()
     AND `TABLE_NAME` NOT LIKE '\\_%' ESCAPE '\\'


===BACK TRACE====

/var/www/html/core/nucommon.php(1535) : eval()'d code - line 63 (nuRunQuery)

/var/www/html/core/nucommon.php - line 1535 (eval)

/var/www/html/core/nucommon.php - line 1585 (nuEvalSafe)

/var/www/html/core/nuform.php - line 26 (nuEval)

/var/www/html/core/nuform.php - line 818 (nuBeforeBrowse)

/var/www/html/core/nuform.php - line 418 (nuGetLookupValues)

/var/www/html/core/nuform.php - line 233 (nuGetFormModifyObject)

/var/www/html/core/nuform.php - line 166 (nuGetFormProcessObjects)

/var/www/html/core/nuapi.php - line 118 (nuGetFormObject)

[kev1n]

The error you posted just occurs under certain circumstances: It happens when a UNION tries to combine text columns or string literals that are defined with different collations. MySQL requires that all corresponding columns in a UNION (the same column positions across each SELECT) use the same character set and collation. If they don’t, MySQL raises the Illegal mix of collations error.

Run the query below in phpMyAdmin and see if the error is gone.

Code: Select all

REPLACE INTO `zzzzsys_php` (`zzzzsys_php_id`, `sph_code`, `sph_description`, `sph_group`, `sph_category`, `sph_status`, `sph_php`, `sph_javascript`, `sph_javascript_active`, `sph_run`, `sph_zzzzsys_form_id`, `sph_system`, `sph_global`, `sph_demo`, `sph_template`, `sph_hide`, `sph_json`) VALUES ('nubuildtable_BB', 'nubuildtable_BB', 'System PHP', 'nubuilder', NULL, '1', '$select = \"\r\n SELECT\r\n CONCAT(\'PROCEDURE:\', `zzzzsys_php`.`zzzzsys_php_id`) AS `zzzzsys_php_id`,\r\n CAST(`zzzzsys_php`.`sph_code` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,\r\n CAST(`zzzzsys_php`.`sph_description` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci\r\n FROM `zzzzsys_php`\r\n WHERE\r\n (`zzzzsys_php`.`sph_system` <> \'1\') \r\n AND (LOCATE(\'{TABLE_ID}\', `zzzzsys_php`.`sph_php`) > \'0\') \r\n AND (`zzzzsys_php`.`sph_template` <> 1)\r\n \r\n UNION\r\n \r\n SELECT\r\n CONCAT(\'SQL:\', `zzzzsys_select`.`zzzzsys_select_id`) AS `id`,\r\n CAST(\'nuSQL\' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,\r\n CAST(`zzzzsys_select`.`sse_description` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci\r\n FROM `zzzzsys_select`\r\n WHERE\r\n (`zzzzsys_select`.`sse_system` IS NULL OR `zzzzsys_select`.`sse_system` = \'\')\r\n \r\n UNION\r\n \r\n SELECT\r\n CONCAT(\'TABLE:\', `TABLE_NAME`) AS `id`,\r\n CAST(\'nuTABLE\' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,\r\n CAST(`TABLE_NAME` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci\r\n FROM `information_schema`.`tables` \r\n WHERE \r\n `TABLE_SCHEMA` = DATABASE()\r\n AND `TABLE_NAME` NOT LIKE \'\\\\\\\\_%\' ESCAPE \'\\\\\\\\\'\r\n\";\r\n\r\n\r\n$select = str_replace(\"{TABLE_ID}\", \'#\'.\'TABLE_ID\'.\'#\', $select);\r\n\r\n$insert = \"\r\nINSERT INTO zzzzsys_report_data (\r\n `zzzzsys_report_data_id`,\r\n `srd_code`,\r\n `srd_description`)\r\n\".$select;\r\n\r\nnuRunQuery(\'DELETE FROM `zzzzsys_report_data`\');\r\nnuRunQuery($insert);', NULL, NULL, NULL, NULL, '1', '0', '1', '0', NULL, NULL)

[homa]

I ran your code. Two records were affected. But this message remains:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation 'UNION'

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


INSERT INTO zzzzsys_report_data (
 `zzzzsys_report_data_id`,
 `srd_code`,
 `srd_description`)

 SELECT
 CONCAT('PROCEDURE:', `zzzzsys_php`.`zzzzsys_php_id`) AS `zzzzsys_php_id`,
 CAST(`zzzzsys_php`.`sph_code` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,
 CAST(`zzzzsys_php`.`sph_description` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci
 FROM `zzzzsys_php`
 WHERE
 (`zzzzsys_php`.`sph_system` <> '1') 
 AND (LOCATE('#TABLE_ID#', `zzzzsys_php`.`sph_php`) > '0') 
 AND (`zzzzsys_php`.`sph_template` <> 1)
 
 UNION
 
 SELECT
 CONCAT('SQL:', `zzzzsys_select`.`zzzzsys_select_id`) AS `id`,
 CAST('nuSQL' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,
 CAST(`zzzzsys_select`.`sse_description` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci
 FROM `zzzzsys_select`
 WHERE
 (`zzzzsys_select`.`sse_system` IS NULL OR `zzzzsys_select`.`sse_system` = '')
 
 UNION
 
 SELECT
 CONCAT('TABLE:', `TABLE_NAME`) AS `id`,
 CAST('nuTABLE' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,
 CAST(`TABLE_NAME` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci
 FROM `information_schema`.`tables` 
 WHERE 
 `TABLE_SCHEMA` = DATABASE()
 AND `TABLE_NAME` NOT LIKE '\\_%' ESCAPE '\\'


===BACK TRACE====

/var/www/html/core/nucommon.php(1535) : eval()'d code - line 45 (nuRunQuery)

/var/www/html/core/nucommon.php - line 1535 (eval)

/var/www/html/core/nucommon.php - line 1585 (nuEvalSafe)

/var/www/html/core/nuform.php - line 26 (nuEval)

/var/www/html/core/nuform.php - line 818 (nuBeforeBrowse)

/var/www/html/core/nuform.php - line 418 (nuGetLookupValues)

/var/www/html/core/nuform.php - line 233 (nuGetFormModifyObject)

/var/www/html/core/nuform.php - line 166 (nuGetFormProcessObjects)

/var/www/html/core/nuapi.php - line 118 (nuGetFormObject)

I also can't select tables in the Fast Report Designer, as I can with the SQL Designer. Could this have something to do with starting with the MASTER from GitHub and then going back to the RELEASE version? Otherwise, I can also completely empty the database and start over. Everything is still a TEST and LEARNING experience, and I'm AMAZED at what a great system has been created here.

[kev1n]

Could you try again with this update ?

Code: Select all

REPLACE INTO `zzzzsys_php` (`zzzzsys_php_id`, `sph_code`, `sph_description`, `sph_group`, `sph_category`, `sph_status`, `sph_php`, `sph_javascript`, `sph_javascript_active`, `sph_run`, `sph_zzzzsys_form_id`, `sph_system`, `sph_global`, `sph_demo`, `sph_template`, `sph_hide`, `sph_json`) VALUES ('nubuildtable_BB', 'nubuildtable_BB', 'System PHP', 'nubuilder', NULL, '1', '$select = \"\r\n SELECT\r\n CAST(CONCAT(\'PROCEDURE:\', `zzzzsys_php`.`zzzzsys_php_id`) AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci AS `zzzzsys_php_id`,\r\n CAST(`zzzzsys_php`.`sph_code` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,\r\n CAST(`zzzzsys_php`.`sph_description` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci\r\n FROM `zzzzsys_php`\r\n WHERE\r\n (`zzzzsys_php`.`sph_system` <> \'1\') \r\n AND (LOCATE(\'{TABLE_ID}\', `zzzzsys_php`.`sph_php`) > \'0\') \r\n AND (`zzzzsys_php`.`sph_template` <> 1)\r\n \r\n UNION\r\n \r\n SELECT\r\n CAST(CONCAT(\'SQL:\', `zzzzsys_select`.`zzzzsys_select_id`) AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci AS `id`,\r\n CAST(\'nuSQL\' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,\r\n CAST(`zzzzsys_select`.`sse_description` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci\r\n FROM `zzzzsys_select`\r\n WHERE\r\n (`zzzzsys_select`.`sse_system` IS NULL OR `zzzzsys_select`.`sse_system` = \'\')\r\n \r\n UNION\r\n \r\n SELECT\r\n CAST(CONCAT(\'TABLE:\', `TABLE_NAME`) AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci AS `id`,\r\n CAST(\'nuTABLE\' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci,\r\n CAST(`TABLE_NAME` AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_general_ci\r\n FROM `information_schema`.`tables` \r\n WHERE \r\n `TABLE_SCHEMA` = DATABASE()\r\n AND `TABLE_NAME` NOT LIKE \'\\\\\\\\_%\' ESCAPE \'\\\\\\\\\'\r\n\";\r\n\r\n$select = str_replace(\"{TABLE_ID}\", \'#\'.\'TABLE_ID\'.\'#\', $select);\r\n\r\n$insert = \"\r\nINSERT INTO zzzzsys_report_data (\r\n `zzzzsys_report_data_id`,\r\n `srd_code`,\r\n `srd_description`)\r\n\".$select;\r\n\r\nnuRunQuery(\'DELETE FROM `zzzzsys_report_data`\');\r\nnuRunQuery($insert);\r\n', NULL, NULL, NULL, NULL, '1', '0', '1', '0', NULL, NULL)

And to fix the layout issue:

Code: Select all

REPLACE INTO `zzzzsys_form` (`zzzzsys_form_id`, `sfo_type`, `sfo_code`, `sfo_description`, `sfo_group`, `sfo_status`, `sfo_breadcrumb_title`, `sfo_table`, `sfo_primary_key`, `sfo_browse_redirect_form_id`, `sfo_browse_target`, `sfo_browse_row_height`, `sfo_browse_rows_per_page`, `sfo_browse_title_multiline`, `sfo_browse_autoresize_columns`, `sfo_browse_sql`, `sfo_javascript`, `sfo_browse_javascript`, `sfo_edit_javascript`, `sfo_style`, `sfo_mobile_view`, `sfo_json`) VALUES ('nufrlaunch', 'launch', 'nufrlaunch', 'Fast Report', 'nubuilder', '1', NULL, '', '', '', NULL, '0', '0', '0', NULL, '', '$(\'#wrdaddable\').css({\'font-size\' : \'14px\', \'font-weight\' : 700, \'padding\' : 5});\r\n$(\'#frwrd\').css({\'font-size\' : \'14px\', \'font-weight\' : 700, \'padding\' : 5});\r\n$(\'#nufr\').css({\'text-align\' : \'left\', \'height\' : 410, \'background-color\': \'#ebebeb\'});\r\n\r\n$(\'#list\').addClass(\'nuScroll\').removeClass(\'nuReadonly\');\r\n\r\n$(\'.nuActionButton\').hide();\r\nnuAddActionButton(\'BuildFastReport\');\r\n\r\n\r\nfunction nuAddReportField(t){\r\n\r\n var f = nuPad3($(\"[data-nu-label=\'Field Name\']\").length - 1);\r\n\r\n $(\'#fast_report_sf\' + f + \'field\').val($(t).html()).change();\r\n $(\'#fast_report_sf\' + f + \'width\').val(100).change();\r\n $(\'#fast_report_sf\' + f + \'sum\').val(\'no\').change();\r\n $(\'#fast_report_sf\' + f + \'title\').val($(t).html()).change().trigger( \"select\" );\r\n \r\n}\r\n\r\n\r\nfunction nuBeforeSave(){\r\n \r\n if($(\'#table\').val() === \'\'){\r\n \r\n nuMessage(nuTranslate(\'Validation Error\'), [\'<b>Table Data</b>\', nuTranslate(\'Cannot be left blank...\')])\r\n return false;\r\n \r\n }\r\n \r\n \r\n if($(\'#orderby\').val() === \'\'){\r\n \r\n nuMessage(nuTranslate(\'Validation Error\'), [\'<b>Order By</b>\', nuTranslate(\'Cannot be left blank...\')])\r\n return false;\r\n \r\n }\r\n \r\n nuBuildFastReport();\r\n \r\n return true;\r\n \r\n}\r\n\r\n\r\nfunction nuFRSetData() {\r\n\r\n if($(\'#fieldlist\').val() !== \'\'){\r\n \r\n var s = String($(\'#fieldlist\').val());\r\n var ds = s.replaceAll(\'[\',\'\').replaceAll(\']\',\'\').replaceAll(\'\\\\\',\'\').replaceAll(\'\"\',\'\');\r\n var fl = ds.split(\',\');\r\n var fu = [];\r\n \r\n $(\'#orderby\').find(\'option\').remove();\r\n \r\n for(var i = 0 ; i < fl.length ; i++){\r\n \r\n if(fl[i] != \'KEEP EXACT HEIGHT\'){\r\n \r\n fu[i] = \'<tr><td><div style=\"overflow:hidden;width:285px;text-align:left;padding:2px\" onclick=\"nuAddReportField(this);\" class=\"nuCalculatorButton nu_input\">\' + fl[i] + \'</div></td></tr>\';\r\n \r\n $(\'#orderby\').append(\'<option value=\"\' + fl[i] + \'\">\' + fl[i] + \'</option>\');\r\n \r\n }\r\n \r\n }\r\n \r\n $(\'#nufr\').html(\'<table>\' + fu.join(\'\') + \'</table>\');\r\n $(\'#orderby\').val(fl[0]);\r\n \r\n }\r\n\r\n}', NULL, NULL, NULL, NULL, NULL)

[homa]

Quick response: the first statement helped, I can now select tables... The test will have to wait, I'm on the road right now.
The second statement is/was unnecessary, as I went back to the release and the layout was fine there. See also my reply above.

Thanks! See you later.