MySQL Bugs: #32571: CREATE TABLE options are dependant on the order of specification

Bug #32571	CREATE TABLE options are dependant on the order of specification
Submitted:	21 Nov 2007 13:03	Modified:	9 Apr 2017 11:50
Reporter:	Jelle Raaijmakers	Email Updates:
Status:	In progress	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.37, 5.1, 5.6, 5.7	OS:	Linux (Windows, Ubuntu)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	character, collate, create, Options, order, SET, specification, table

Description:
When you create a table and specify both COLLATE and CHARACTER SET in the table options, the COLLATE option is ignored and set to the default collation when the CHARACTER SET option is positioned after the COLLATE option in the CREATE TABLE statement.

How to repeat:
CREATE TABLE `...` (
   ...
) ENGINE=MyISAM COLLATE=utf8_unicode_ci CHARACTER SET=utf8;

-- at this point, collate is utf8_general_ci

CREATE TABLE `...` (
   ...
) ENGINE=MyISAM CHARACTER SET=utf8 COLLATE=utf8_unicode_ci;

-- at this point, collate is utf8_unicode_ci, as expected

Veryfied as described by using 5.1 bzr tree (5.1.32)

create table t(v varchar(100))ENGINE=MyISAM COLLATE=utf8_unicode_ci CHARACTER SET=utf8;

create table t2(v varchar(100))ENGINE=MyISAM CHARACTER SET=utf8 COLLATE=utf8_unicode_ci;

show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `v` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `v` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

Collation was ignored in table t.

I suggest updating the documentation for CREATE TABLE to specify the importance of the order of CHARACTER SET and COLLATE.

http://dev.mysql.com/doc/refman/5.7/en/create-table.html

Also update the Database Character Set and Collation documentation. It discusses using both or either but not that the order matters when using both.

http://dev.mysql.com/doc/refman/5.7/en/charset-database.html

Posted by developer:
 
Hi Monty,

We will have another look into this one.

@monty

Documentation does not require any specific order of the table options, so this is not a documentation bug but rather a bug in the way table options are parsed or handled.

Since this bug has been open for almost ten (10) years and it is still an issue in 5.6 and 5.7 it seems prudent to add a warning to the documentation until the bug is fixed.

Can you update the versions affected by this bug to include 5.6 and 5.7?

Here are links to examples of notes in the documentation about known bugs.

https://dev.mysql.com/doc/refman/5.5/en/alter-table.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

Versions 5.6 and 5.7 added to affected versions.

Bug #89041 marked as duplicate of this one

Bug#57385 CREATE TABLE default COLLATE/CHARACTER SET depends on order

was marked as a duplicate.