we recently ran into an issue that we couldn't explain ourselves.
We use a MySQL 8.0.21 database that our JVM based backend connects to (via JDBC).
Some of these columns are used to compare texts. These are set to the charset utf8mb4 and collate utf8mb4_0900_as_ci. We want them to be case insensitive here.
However, all other columns (non user generated data) are supposed to be set to the collate utf8mb4_0900_as_cs, so that e.g. our generated IDs (which contain lower and uppercase letters) can be compared on a case sensitive basis.
During our most recent database migration, we noticed that the collation_server is still set to utf8mb4_0900_ai_ci. This is the default our cloud provider has set. Because this isn't the safe default we want it to be, we changed it to ut8mb4_0900_as_cs. Given that the collations are explicitly set on all tables, we did not imagine this to be an issue, because the documentation states:
The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose.
Source: https://dev.mysql.com/doc/refman/8.0/en/charset-server.html
We expected special characters like ü,ä,ö and emojis to work. However, changing this one variable collation_server breaks that functionality.
We always thought the collation isn't responsible for actually formatting the data that is being read and written. Also, our expectation was that these collates are interoperable, given they are both based on utf8mb4.
Tl;dr: We changed the collation_server MySQL variable from utf8mb4_0900_ai_ci to utf8mb4_0900_as_cs. This breaks reading & saving of any emojis or special characters inside the db.
The actual question: Why does this happen? How does the collate influence how special characters are read from or written into the db?