How to change the collation of a MySQL database

Written by Claudio Kuenzler - 1 comments

Published on December 11th 2013 - Listed in MySQL Database

---

Sometimes databases have a different character collation than its tables. This could happen under several circumstances, for example the database is created without definition of the collation and then the tables were created with a collation setting:

mysql> CREATE DATABASE test;
mysql> CREATE TABLE table CHARACTER SET latin1 COLLATE latin1_german2_ci;

If the default collation is not set in the database config (e.g. in my.cnf as default-collation) or during the database creation, the MySQL default is taken, which is latin1_swedish_ci. This can be verified with the following command:

mysql> show collation where Charset = 'latin1';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+
8 rows in set (0.00 sec)

This could result in the following problem: The database itself has a collation of latin1_swedish_ci while the tables run with collation latin1_german2_ci. If stored procedures or triggers come into the database, they're stored as latin1_swedish_ci but try to handle data from the tables, which are latin1_german2_ci. This (could) results in an error. To solve this, the database collation has to be changed.

First, let's get the current database collation:

mysql> use test;
Database changed
mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_german2_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Now let's change (ALTER) the database collation:

mysql> ALTER DATABASE test DEFAULT CHARACTER SET = latin1 DEFAULT COLLATE = latin1_german2_ci;

... and verify the collation variables again:

mysql> use test;
Database changed
mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_german2_ci |
| collation_server     | latin1_german2_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Success! After deleting and re-creating the stored function, it is now working correctly.

Add a comment

Show form to leave a comment

Comments (newest first)

ck from Switzerland wrote on Mar 27th, 2015:

In my.cnf the configuration looks like this:

character_set_server = latin1
collation_server = latin1_german2_ci

Successfully tested on MariaDB Galera Server 10.

---