MySQL: Created table did not inherit default collation of database

Written by Claudio Kuenzler - 0 comments

Published on August 29th 2013 - Listed in Database MySQL

---

I've been running a couple of tests on a MySQL 5.1 server today and found some pretty strange behavior. 

When a new table was created, the collation of the new table was always latin1_swedish_ci, even though the default collation of the database was set to latin1_german2_ci. Only by appending  the COLLATE syntax to the CREATE TABLE command this could be overwritten.

In case you ask: Yes, MySQL starts up with a default character set latin1 and default collation latin1_german2_ci.

First I created a new database claudiotest2 on the command line and defined the default collation:

mysql -e "CREATE DATABASE claudiotest2 DEFAULT CHARACTER SET = latin1 DEFAULT COLLATE = latin1_german2_ci"

Then verified how MySQL has interpreted that:

mysql> SHOW CREATE DATABASE claudiotest2;
+--------------+--------------------------------------------------------------------+
| Database     | Create Database                                                    |
+--------------+--------------------------------------------------------------------+
| claudiotest2 | CREATE DATABASE `claudiotest2`                                     |
|              | /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci */ |
+--------------+--------------------------------------------------------------------+

So that looks pretty OK.... Let's create a table:

mysql> use claudiotest2;
Database changed

mysql> CREATE TABLE IF NOT EXISTS `domain` (
) NOT NULL AUTO_INCREMENT,
  `custid` int(4) NOT NULL,
  `tld`    ->    `domainid` int(5) NOT NULL AUTO_INCREMENT,
ENT=1 ;    ->   `custid` int(4) NOT NULL,
    ->   `tld` varchar(100) NOT NULL,
    ->   PRIMARY KEY (`domainid`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.01 sec)

Note that I only defined the default charset (latin1) but not a collation.
Given that the database was create with the default collation latin1_german2_ci, I expected the table would inherit this collation.
Verification (I have cut irrelevant cols):

mysql> show table status;
+--------+--------+---------------------+-------------------+
| Name   | Engine | Create_time         | Collation         |
+--------+--------+---------------------+-------------------+
| domain | MyISAM | 2013-08-29 10:30:42 | latin1_swedish_ci |  
+--------+--------+---------------------+-------------------+

Argh... the table was created with a collation set to latin1_swedish_ci.

Only by manually defining the collation in CREATE TABLE this could be overwritten:

mysql> CREATE TABLE IF NOT EXISTS `domain2` (
) NOT NULL,
  `tld` varc    -> har(10  `domainid` int(5) NOT NULL AUTO_INCREMENT,
erman2_ci AUTO_INCREMENT=1 ;    ->   `custid` int(4) NOT NULL,
    ->   `tld` varchar(100) NOT NULL,
    ->   PRIMARY KEY (`domainid`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE latin1_german2_ci AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.03 sec)

mysql> show table status;
+---------+--------+---------------------+-------------------+
| Name    | Engine | Create_time         | Collation         |
+---------+--------+---------------------+-------------------+
| domain  | MyISAM | 2013-08-29 10:30:42 | latin1_swedish_ci |
| domain2 | MyISAM | 2013-08-29 10:33:53 | latin1_german2_ci |
+---------+--------+---------------------+-------------------+

The official MySQL documentation (on Table Character Set and Collation) defines the collation:

If CHARACTER SET X is specified without COLLATE, character set X and its default collation are used. To see the default collation for each character set, use the SHOW COLLATION statement. 

So in the CREATE TABLE command for table domain, I only defined the charset (latin1). MySQL then looks up the default collation for this charset.
This can be verified manually:

mysql> SHOW COLLATION LIKE '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 |
+-------------------+---------+----+---------+----------+---------+

Well yes... latin1_swedish_ci is set as the default collation. Great.

Now I know at least WHY latin1_swedish_ci is used if no table collation was defined.
But how does one change the default collation on a charset?

Yes, I'm actually asking you people out there ;-).

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.