| Bug #74816 | Work around for charset conversion with FKs can result in wrong data | ||
|---|---|---|---|
| Submitted: | 12 Nov 2014 12:28 | Modified: | 13 Nov 2014 5:55 |
| Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.6.21, 5.6.23, 5.7.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | character set, foreign keys, utf8 | ||
[12 Nov 2014 12:28]
Daniël van Eeden
[12 Nov 2014 12:32]
Daniël van Eeden
Also affects joins.
mysql [test] > select * from t2 inner join t1 on t1.name=t2.fname;
Empty set (0.00 sec)
mysql [test] > explain extended select * from t2 inner join t1 on t1.name=t2.fname\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: name
key: name
key_len: 303
ref: func
rows: 1
filtered: 100.00
Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)
mysql [test] > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`fname` AS `fname`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`name` = convert(`test`.`t2`.`fname` using utf8))
1 row in set (0.00 sec)
mysql [test] > select * from t2 inner join t1 on t1.name=CONVERT(t2.fname USING binary);
+----+-------+-------------+----+----------+
| id | name | fname | id | name |
+----+-------+-------------+----+----------+
| 2 | test1 | wrocÅ‚aw | 2 | wrocław |
+----+-------+-------------+----+----------+
1 row in set (0.00 sec)
[13 Nov 2014 5:55]
MySQL Verification Team
Hello Daniël, Thank you for the bug report and test case. Thanks, Umesh
[13 Nov 2014 5:56]
MySQL Verification Team
// 5.6.23
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.23 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.23-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> create table t1 (id int auto_increment primary key, name varchar(100) unique);
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2 (id int auto_increment primary key, name varchar(100), fname varchar(100), foreign key (fname) references t1 (name) on update cascade on delete cascade);
Query OK, 0 rows affected (0.04 sec)
mysql> set foreign_key_checks=off;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t1 convert to character set utf8;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set foreign_key_checks=on;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1(name) values('test1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2(name,fname) values('test1','test1');
Query OK, 1 row affected (0.01 sec)
mysql> update t1 set name='wroclaw' where name='test1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | wroclaw |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from t2;
+----+-------+-------------+
| id | name | fname |
+----+-------+-------------+
| 1 | test1 | wrocław |
+----+-------+-------------+
1 row in set (0.00 sec)
mysql> delete from t2;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(name) values('test1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2(name,fname) values('test1','test1');
Query OK, 1 row affected (0.00 sec)
mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> set innodb_strict_mode=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set name='wroclaw' where name='test1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 2 | wroclaw |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from t2;
+----+-------+-------------+
| id | name | fname |
+----+-------+-------------+
| 2 | test1 | wrocław |
+----+-------+-------------+
1 row in set (0.00 sec)
[13 Nov 2014 5:56]
MySQL Verification Team
// 5.7.6
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.6 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.6-m16-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> drop table t1,t2;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> create table t1 (id int auto_increment primary key, name varchar(100) unique);
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2 (id int auto_increment primary key, name varchar(100), fname varchar(100), foreign key (fname) references t1 (name) on update cascade on delete cascade);
Query OK, 0 rows affected (0.06 sec)
mysql> set foreign_key_checks=off;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t1 convert to character set utf8;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set foreign_key_checks=on;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1(name) values('test1');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2(name,fname) values('test1','test1');
Query OK, 1 row affected (0.00 sec)
mysql> update t1 set name='wroclaw' where name='test1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | wroclaw |
+----+----------+
1 row in set (0.02 sec)
mysql> select * from t2;
+----+-------+-------------+
| id | name | fname |
+----+-------+-------------+
| 1 | test1 | wrocław |
+----+-------+-------------+
1 row in set (0.00 sec)
mysql> delete from t2;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(name) values('test1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2(name,fname) values('test1','test1');
Query OK, 1 row affected (0.01 sec)
mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> set innodb_strict_mode=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set name='wroclaw' where name='test1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 2 | wroclaw |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from t2;
+----+-------+-------------+
| id | name | fname |
+----+-------+-------------+
| 2 | test1 | wrocław |
+----+-------+-------------+
1 row in set (0.00 sec)
[17 Aug 2015 16:45]
Daniel Price
Posted by developer: The following content has been added to the ALTER TABLE documentation: https://dev.mysql.com/doc/refman/5.6/en/alter-table.html When foreign_key_checks is enabled, which is the default setting, character set conversion is not permitted on tables that include a character string column used in a foreign key constraint. The workaround is to disable foreign_key_checks before performing the character set conversion. You must perform the conversion on both tables involved in the foreign key constraint before re-enabling foreign_key_checks. If you re-enable foreign_key_checks after converting only one of the tables, an ON DELETE CASCADE or ON UPDATE CASCADE operation could corrupt data in the referencing table due to implicit conversion that occurs during these operations (Bug #45290, Bug #74816).
