[Solved] SQL Error when search ticket

[mbory]

Hi, I'm experiencing a problem that I can't seem to solve. When I want to search for a ticket in the dashboard, for example by "first and last name", I get an SQL error.

I've checked my tables and the database, I'm in utf8mb4, I've tried to modify the queries by adding COLLATE 'utf8mb4_unicode_ci' etc... But the error persists.

Could you please give me an idea?

Thanks

Code: Select all

Impossible d'exécuter SQL: SELECT COUNT(*) AS `cnt`, `status`, IF (`owner` = 2, 1, IF (`owner` = 0, 0, IF (`assignedby` = 2, 3, 2) ) ) AS `assigned_to`, IF (`due_date` < NOW(), 2, IF (`due_date` BETWEEN NOW() AND (NOW() + INTERVAL 7 DAY), 1, 0) ) AS `due` FROM `hesk_tickets` WHERE 1 AND 1 AND `name` LIKE '%arguin%' COLLATE 'utf8mb4_unicode_ci' GROUP BY `assigned_to`, `due`, `status`

MySQL a dit:
COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8mb3'

[Klemen]

What is the $hesklang['_COLLATE'] set to in your /language/(language name)/text.php file?

Should be

Code: Select all

$hesklang['_COLLATE']='utf8_general_ci';

or similar.

Also, not sure how you ended up on utf8mb3 charset, Hesk installation files set it to utf8?

[mbory]

What is the $hesklang['_COLLATE'] set to in your /language/(language name)/text.php file?

In folder en is :

Code: Select all

$hesklang['_COLLATE']='utf8_unicode_ci';

In folder fr is :

Code: Select all

$hesklang['_COLLATE']='utf8_general_ci';

I try to set :

Code: Select all

$hesklang['_COLLATE']='utf8mb4_unicode_ci';

and :

Code: Select all

$hesklang['_COLLATE']='utf8mb4_general_ci';

But it doesn't change anything.

In phpMyAdmin, my tables are in utf8mb4.

[Klemen]

How did you install Hesk? With the original installation files, the charset should not be set to utf8mb4.

What version of MySQL are you using?
What version of PHP?

What I would try is:
- backup the database
- convert all tables and columns from utf8mb4 to utf8
- try again with original Hesk charsets

[mbory]

I try this SQL request :

Code: Select all

SET FOREIGN_KEY_CHECKS = 0;

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabase'
    AND table_type = 'BASE TABLE';

SET FOREIGN_KEY_CHECKS = 1;

But the mistake is always there.
EDIT : I tried several things, the SQL query, I modified directly from phpMyAdmin the encoding of all tables and columns in utf8mb3, I modified in the source code the COLLATE, I deleted the "mb4", etc.... But the error is still there.

The version of Hesk I have installed is 3.4.3 with the standard installation provided by Hesk.

Code: Select all

Impossible d'exécuter SQL: SELECT COUNT(*) AS `cnt`, `status`, IF (`owner` = 2, 1, IF (`owner` = 0, 0, IF (`assignedby` = 2, 3, 2) ) ) AS `assigned_to`, IF (`due_date` < NOW(), 2, IF (`due_date` BETWEEN NOW() AND (NOW() + INTERVAL 7 DAY), 1, 0) ) AS `due` FROM `hesk_tickets` WHERE 1 AND 1 AND `name` LIKE '%arguin%' COLLATE 'utf8mb4_general_ci' GROUP BY `assigned_to`, `due`, `status`

MySQL a dit:
COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8mb3'

[Klemen]

Your SQL code changes the default table character set (for new columns), but the existing ones are probably still utf8mb3

Does it work for you if you try utf8mb3_general_ci as the collation?

If not, the only thing I can think off is that the entire database, including all tables and all columns, will have to be converted to utf8 from utf8mb3

[mbory]

Your SQL code changes the default table character set (for new columns), but the existing ones are probably still utf8mb3

Does it work for you if you try utf8mb3_general_ci as the collation?

If not, the only thing I can think off is that the entire database, including all tables and all columns, will have to be converted to utf8 from utf8mb3

I modified the entire database, all tables and columns with utf8mb3, but I had not modified the $hesklang['_COLLATE'] line in language.

I set it to :

Code: Select all

$hesklang['_COLLATE']='utf8mb3_unicode_ci';

And it works, I don't get the ticket search error anymore, I'll keep testing, but it looks good, thanks for your help!