0

mysql Ver 8.0.25

Got this error in apache log:

Error query: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' In query: SELECT idaction, type, name FROM matomo_log_action WHERE ( hash = CRC32(?) AND name = ? AND type = ? ) [...]

This is the table's info:

mysql> SHOW FULL COLUMNS FROM matomo_log_action;
+------------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field      | Type             | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| idaction   | int unsigned     | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name       | varchar(4096)    | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| hash       | int unsigned     | NULL               | NO   |     | NULL    |                | select,insert,update,references |         |
| type       | tinyint unsigned | NULL               | YES  | MUL | NULL    |                | select,insert,update,references |         |
| url_prefix | tinyint          | NULL               | YES  |     | NULL    |                | select,insert,update,references |         |
+------------+------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

Now I think I need to modify the Collation of field type to utf8mb4_unicode_ci, but don't know how:

mysql> ALTER TABLE matomo_log_action MODIFY type tinyint unsigned CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci' at line 1

3
  • 1
    The column type is numeric, and charset/collation is not applicable to it. Now I think I need to modify the Collation of field type to utf8mb4_unicode_ci ?? Why do you think so? The error message shows that you'd modify the column name.
    – Akina
    Dec 29, 2022 at 10:55
  • 1
    .. or, if the collation explicitly assigned to name is critical, modify the string literal which is provided as query parameter. Like .. AND name = ? COLLATE utf8mb4_unicode_ci AND ..
    – Akina
    Dec 29, 2022 at 11:08
  • Thx for these insights! Will try out your second proposal since the column is huge (>20GB) and also the assigned collation may be critical as you said.
    – dr0i
    Dec 29, 2022 at 11:16

1 Answer 1

1

As this is about matomo using the db I ended up fixing this with this matomo command:

./console core:convert-to-utf8mb4

This command i.a. did log this:

ALTER TABLE `matomo_log_action` CONVERT TO CHARACTER SET utf8mb4;

That changed the collation of matomo_log_action.name to utf8mb4_0900_ai_ci and the error did not appear again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.