| Bug #75320 | Alter table xxx CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1 should faile | ||
|---|---|---|---|
| Submitted: | 28 Dec 2014 3:08 | Modified: | 17 Apr 2015 9:40 |
| Reporter: | Daniel Black (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.6.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Dec 2014 6:34]
MySQL Verification Team
Hello Daniel, Thank you for the report and contribution. Could you please sign the OCA, so that we can consider taking your patch? Please see the following for more details: http://www.oracle.com/technetwork/community/oca-486395.html Thanks, Umesh
[29 Dec 2014 6:34]
MySQL Verification Team
// 5.6.23
mysql> create table t1(id int not null, name varchar(100), dt datetime)engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> Alter table t1 CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1(id int not null, name varchar(100), dt datetime)engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> Alter table t1 CONVERT TO CHARACTER SET latin1, CHARACTER SET utf8;
ERROR 1302 (HY000): Conflicting declarations: 'CHARACTER SET latin1' and 'CHARACTER SET utf8'
mysql>
[29 Dec 2014 12:11]
Daniel Black
cross ref: https://mariadb.atlassian.net/browse/MDEV-7387 oca submitted.
[6 Mar 2015 10:27]
Praveenkumar Hulakund
Posted by developer: The contribution patch looks correct. We can use the patch and add test coverage for it. As this issue is not a major issue, we can target it got for only trunk.
[16 Apr 2015 18:26]
Paul DuBois
Noted in 5.8.0 changelog. A statement of the following form converted the table data to latin1, but also changed the table default character set to latin1 and ignored the utf8 clause: ALTER TABLE tbl_name CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1; Thanks to Daniel Black for the patch.
[17 Apr 2015 9:40]
Daniel Black
Thank you. https://github.com/MariaDB/server/commit/bc902a2bfc46add0708896c07621e3707f66d95f was improved a bit to handle when one of these is DEFAULT and the other isn't.
Description: Both these terms set the default_table_charset attribute internally so whenever used they should be the same value. Alter table xxx CONVERT TO CHARACTER SET latin1, CHARACTER SET utf8 has protection mechanism that result in an error. However Alter table xxx CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1 will ignore the utf8 and just use the latin1 for the default charset and conversion. How to repeat: Alter table xxx CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1 on any table. The resting table will have DEFAULT CHARSET=latin1 Suggested fix: This is from mariadb-10 however should still apply. I am the author of this, as much as copying/reworking the sql_yacc.yy for DEFAULT CHARSET and moving it to the parser for CONVERT, and give permission to use this however you want. After patch: MariaDB [test]> alter ONLINE table seller_counters CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1, LOCK=SHARED; ERROR 1302 (HY000): Conflicting declarations: 'CHARACTER SET utf8' and 'CHARACTER SET latin1' === modified file 'sql/sql_yacc.yy' --- sql/sql_yacc.yy 2014-11-19 16:23:39 +0000 +++ sql/sql_yacc.yy 2014-12-28 00:43:36 +0000 @@ -7646,9 +7646,20 @@ MYSQL_YYABORT; } LEX *lex= Lex; - lex->create_info.table_charset= - lex->create_info.default_table_charset= $5; - lex->create_info.used_fields|= (HA_CREATE_USED_CHARSET | + HA_CREATE_INFO *cinfo= &Lex->create_info; + if ((cinfo->used_fields & HA_CREATE_USED_DEFAULT_CHARSET) && + cinfo->default_table_charset && $5 && + !my_charset_same(cinfo->default_table_charset,$5)) + { + my_error(ER_CONFLICTING_DECLARATIONS, MYF(0), + "CHARACTER SET ", cinfo->default_table_charset->csname, + "CHARACTER SET ", $5->csname); + MYSQL_YYABORT; + } + + cinfo->table_charset= + cinfo->default_table_charset= $5; + cinfo->used_fields|= (HA_CREATE_USED_CHARSET | HA_CREATE_USED_DEFAULT_CHARSET); lex->alter_info.flags|= Alter_info::ALTER_CONVERT; }