Description:
If a client connects to a MySQL server specifying a collation that the server doesn't know, the server silently switches it to the server's default collation.
This happens, for example, if a mysql cli version 8.0 (that by default uses collation utf8mb4_0900_ai_ci) connects to a database server running version 5.7 .
How to repeat:
Use a mysql cli version 8.0 and connect to a database server running 5.7 and configured with default charset latin1.
$ mysql --version
mysql Ver 8.0.17 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
$ mysql -u sbtest -psbtest -h 127.0.0.1 -P3306 --default-character-set=utf8mb4 -e 'SELECT * FROM performance_schema.session_variables WHERE variable_name LIKE "%coll%" OR variable_name LIKE "version"';
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+-----------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------+-----------------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| version | 5.7.32-0ubuntu0.18.04.1-log |
+----------------------+-----------------------------+
Suggested fix:
If MySQL server doesn't know the collation required by the client, it shouldn't just ignore the collation and allow the connection to successfully complete.
It should instead return an error to the client.
