Bug #3255 Collation get lost in CREATE TABLE (...) DEFAULT CHARACTER SET xxx COLLATE yyy
Submitted: 22 Mar 2004 2:19 Modified: 30 Mar 2004 9:43
Reporter: Alexander Barkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.2 bk current OS:Any (all)
Assigned to: Alexander Barkov CPU Architecture:Any

[22 Mar 2004 2:19] Alexander Barkov 
Description:
Subject: COLLATE in CREATE TABLE
From: <programmer@bardware.de>
Date: Thu, 18 Mar 2004 11:00:02 +0100
 
Hi,
 
I want to CREATE a TABLE an specify a collation.
 
If I do
CREATE TABLE ... DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
SHOW TABLE STATUS shows me Collation: latin1_swedish_ci
If I omit the keyword DEFAULT the requested collation latin1_german1_ci
is shown by SHOW TABLE STATUS.
 
So, what does DEFAULT before CHARACTER SET means?
 
If I don't use SHOW TABLE STATUS but SHOW CREATE TABLE the result is
interesting too.
For the table I applied the DEFAULT keyword the generated CREATE
TABLE-statement uses the DEFAULT keyword too, but does not show the
COLLATE part of the CREATE TABLE statement.
For the table I omited the DEFAULT keyword, the generated
CREATE-statement uses the DEFAULT keyword - though omited in my
statement -, and shows the COLLATE part of the CREATE TABLE statement.
It's in fact my first statement.....

Is this regular behaviour?
 
I'm running MySQL 4.1.1a on an NT4-Box

Best wishes, Bernhard

How to repeat:
Run the above.
[22 Mar 2004 9:29] Bernhard Doebler 
Hi,

now that my problem is a reported bug, I wanted to post my orignal statements and their results.

Statements:
create table test_default(text char(5)) DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
SHOW CREATE TABLE test_default;
create table test_nodefault(text char(5)) CHARACTER SET latin1 COLLATE latin1_german1_ci;
SHOW CREATE TABLE test_nodefault;
show table status like 'test%';

And the log:
mysql> create table test_default(text char(5)) DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW CREATE TABLE test_default;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                      |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| test_default | CREATE TABLE `test_default` (
  `text` char(5) character set latin1 collate latin1_german1_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> create table test_nodefault(text char(5)) CHARACTER SET latin1 COLLATE latin1_german1_ci;
Query OK, 0 rows affected (0.09 sec)

mysql> SHOW CREATE TABLE test_nodefault;
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                             |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_nodefault | CREATE TABLE `test_nodefault` (
  `text` char(5) collate latin1_german1_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> show table status like 'test%';
+----------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name           | Type   | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+----------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| test_default   | MyISAM | Fixed      |    0 |              0 |           0 |     25769803775 |         1024 |         0 |           NULL | 2004-03-22 18:23:08 | 2004-03-22 18:23:08 | NULL       | latin1_swedish_ci |     NULL |                |         |
| test_nodefault | MyISAM | Fixed      |    0 |              0 |           0 |     25769803775 |         1024 |         0 |           NULL | 2004-03-22 18:23:08 | 2004-03-22 18:23:08 | NULL       | latin1_german1_ci |     NULL |                |         |
+----------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
2 rows in set (0.02 sec)

In C:\winnt\my.ini the following values are set

set-variable=default-character-set=latin1
set-variable=default-collation=latin1_german1_ci

Thanks for paying attention to my report,
Bernhard
[30 Mar 2004 9:43] Alexander Barkov 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html