MySQL: Created table did not inherit default collation of database

Written by - 0 comments

Published on - 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.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder