Default server collation on a new Azure Database for MySQL

asked 2022-06-05T17:40:00.19+00:00
Francois Botha 1 Reputation point

Hi,

If I download and install MySQL server on my localhost, for any version 8 or above, the default server collation is one of the utf8 variants. This is also documented as the default collation at https://dev.mysql.com/doc/refman/8.0/en/charset.html .

For MySQL v5.7 the default server collation was latin1_swedish_ci.

When I install a create a new instance of Azure Database for MySQL, it reports itself as v8.0.15, however, the default server collation is still latin1_swedish_ci.

Is this "discrepancy" intentional and if so, why? I would have expected that Azure Database for MySQL v8+ to follow the same default settings as a standard localhost install.

This subtle difference in collation has now bitten us when we migrated an existing database to Azure.

Note that I already know how to change the server collation. I'm interested in the reason behind the choice of default server collation setting.

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
438 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2022-06-06T06:11:34.543+00:00
    GeethaThatipatri-MSFT 14,941 Reputation points Microsoft Employee

    Hi @Francois Botha Welcome to Microsoft Q&A, Thanks for posting your query.
    In a single server for all versions the default collation was set to latin1_swedish_ci, this was set to make sure that all versions have the same default settings.
    We have received feedback on this and in the flexible server offering we are setting the default collation to match the community edition.
    For a single server, you can change it by changing the server parameter (collation_server) if you want to change it however as you mentioned you are aware of how to change.
    For flexible server
    8.0 --> utf8mb4_0900_ai_ci
    5.7 --> latin1_swedish_ci
    Please let me know if you need additional information

    Regrads
    Geetha