Bug #102265 collation silently switched to server default during mysql_real_connect
Submitted: 18 Jan 0:27 Modified: 18 Jan 7:59
Reporter: Rene' Cannao' Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: C API (client library) Severity:S2 (Serious)
Version:5.6 5.7, 5.7.32 OS:Any
Assigned to: CPU Architecture:Any

[18 Jan 0:27] Rene' Cannao'
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 -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.
[18 Jan 0:45] Rene' Cannao'
http://bugs.mysql.com/102266 is also relevant
[18 Jan 7:59] MySQL Verification Team
Hello Rene,

Thank you for the report and test case.
Verified as described.