While adding a new Report I got this Error: General error: 1271

[Espo]

I don't know how to fix this Issue.
Can someone help me, how to fix this error?
=========
Database: mariadb.org binary distribution 11.7.2-MariaDB-ubu2404
PHP: 7.4.33
nuBuilder DB: V.4.7-2025.05.09.00
nuBuilder Files: V.4.7-2025.05.09.00
=========



[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(1521) : eval()'d code - line 63 (nuRunQuery)

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

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

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

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

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

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

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

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

[kev1n]

Hi,

This error happens when MySQL tries to UNION results from different tables or sources, but the text columns have incompatible collations (e.g. utf8_general_ci, utf8mb4_unicode_ci, latin1_swedish_ci, etc.).
MySQL doesn't know how to compare them, so it throws error 1271.

Your Query Has:

1. SELECT from

   Code: Select all

   zzzzsys_php

2. UNION with SELECT from

   Code: Select all

   zzzzsys_select

3. UNION with SELECT from

   Code: Select all

   information_schema.tables

Each source probably uses different collations.

Solution: Force Same Collation
Add

Code: Select all

CONVERT(... USING utf8mb4) COLLATE utf8mb4_general_ci

to each text column.

Example Fixed Query:

Code: Select all

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,
CONVERT(zzzzsys_php.sph_code USING utf8mb4) COLLATE utf8mb4_general_ci AS srd_code,
CONVERT(zzzzsys_php.sph_description USING utf8mb4) COLLATE utf8mb4_general_ci AS srd_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 zzzzsys_php_id,
'nuSQL' COLLATE utf8mb4_general_ci,
CONVERT(zzzzsys_select.sse_description USING 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 zzzzsys_php_id,
'nuTABLE' COLLATE utf8mb4_general_ci,
CONVERT(TABLE_NAME USING utf8mb4) COLLATE utf8mb4_general_ci
FROM information_schema.tables
WHERE
TABLE_SCHEMA = database()
AND TABLE_NAME NOT LIKE '\_%' ESCAPE '\'