Modify Collation of a column

Question

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

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