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

[21 Nov 2007 13:03] Jelle Raaijmakers
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
[4 Feb 2009 10:20] Susanne Ebrecht
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.
[15 Nov 2016 18:59] monty solomon
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
[15 Nov 2016 19:04] monty solomon
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
[21 Nov 2016 13:38] Ståle Deraas
Posted by developer:
 
Hi Monty,

We will have another look into this one.
[26 Nov 2016 14:12] Jelle Raaijmakers
@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.
[14 Mar 2017 16:39] monty solomon
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?
[5 Apr 2017 19:56] monty solomon
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
[9 Apr 2017 11:50] Jelle Raaijmakers
Versions 5.6 and 5.7 added to affected versions.
[24 Dec 2017 5:26] MySQL Verification Team
Bug #89041 marked as duplicate of this one
[13 Apr 2018 4:04] Erlend Dahl
Bug#57385 CREATE TABLE default COLLATE/CHARACTER SET depends on order

was marked as a duplicate.