2

How to set the value for "default_collation_for_utf8mb4" to "utf8mb4_general_ci", globally , or when the mysql 8.0 server started ?

I tried

[mysqld]
init_connect='SET default_collation_for_utf8mb4=utf8mb4_general_ci'
collation_server = utf8mb4_general_ci
character_set_server = utf8mb4

but still not working. The value still showing "utf8mb4_0900_ai_ci".

 >show variables like "%collation%";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
2
  • This is hardcoded (?) charset property value - see SHOW CHARSET;. Add according additional SQL statement executed during the connection establishing to the connection property.
    – Akina
    Commented Apr 27, 2022 at 10:18
  • init_connect is not executed as "root" (or other SUPER users) connect.
    – Rick James
    Commented Apr 27, 2022 at 22:20

1 Answer 1

1
mysql> show variables like "default_collation_for_utf8mb4";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
1 row in set (0.00 sec)

mysql> SET PERSIST default_collation_for_utf8mb4='utf8mb4_general_ci';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                |
+---------+------+--------------------------------------------------------------------------------------------------------+
| Warning | 1681 | Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release. |
+---------+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root@0273bbf363a9:/etc/mysql/mysql.conf.d# /etc/init.d/mysql status
 * Percona Server 8.0.29-21 is running
root@0273bbf363a9:/etc/mysql/mysql.conf.d# /etc/init.d/mysql restart
 * Stopping Percona Server 8.0.29-21
..
 * Percona Server 8.0.29-21 is stopped
 * Re-starting Percona Server 8.0.29-21
..
 * Percona Server 8.0.29-21 is started
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "default_collation_for_utf8mb4";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+
1 row in set (0.01 sec)

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.