MySQL Bugs: #65648: Foreign key and uppercase / lowercase values

Bug #65648	Foreign key and uppercase / lowercase values
Submitted:	18 Jun 2012 8:07	Modified:	4 Jul 2013 13:14
Reporter:	Fab Gan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.63, 5.5.24	OS:	Any (Linux, Windows)
Assigned to:		CPU Architecture:	Any
Tags:	collation, foreign key, lowercase, uppercase

Description:
Good morning,
I have an application where the user ids were stored lowercase.
Some batch import, in the user table some users stored  a uppercase
id, and for some applicative logic, in other tables that have a
foreign key to the user table, their user ids are stored lowercase.
MySQL didn't throw any error probalby because the collation used is
"case insensitive".
My problem is that the application is Java and java strings are case
sensitive, so now I want to set user ids to lowercase EVERYWHERE.

I supposed that I could execute with ease these commands:
- update mytable1 set USER_ID = LOWER(USER_ID);
- update mytable2 set USER_ID = LOWER(USER_ID);
- update mytable3 set USER_ID = LOWER(USER_ID);

But for some tables I got some Foreign key constraint to throw an
error. (but why they didn't throw an error on the insert but just on
the update?)

How to repeat:
SET collation_connection = utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

show variables like '%colla%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

____________________________________________________-

CREATE SCHEMA BUG_TEST;

USE BUG_TEST;

CREATE TABLE `TBL_USER` (
  `USER_ID` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `TBL_COMMENT` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `USER_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK4F6E52581590B46E` (`USER_ID`),
  CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO TBL_USER (USER_ID) VALUES ('GIUSEPPE');
INSERT INTO TBL_COMMENT (USER_ID) VALUES ('GIUSEPPE');
INSERT INTO TBL_COMMENT (USER_ID) VALUES ('Giuseppe');
INSERT INTO TBL_COMMENT (USER_ID) VALUES ('giuseppe');

update TBL_USER set USER_ID = 'giuseppe' WHERE USER_ID = 'GIUSEPPE';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`BUG_TEST`.`TBL_COMMENT`, CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`))

Same problem with utf8_general_ci

mysql> drop schema BUG_TEST;
Query OK, 2 rows affected (0.11 sec)

mysql> CREATE SCHEMA BUG_TEST CHARACTER SET utf8 COLLATE = utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> USE BUG_TEST;
Database changed
mysql> show variables like '%colla%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE `TBL_USER` (
    ->   `USER_ID` varchar(50) COLLATE utf8_general_ci NOT NULL,
    ->   PRIMARY KEY (`USER_ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE `TBL_COMMENT` (
    ->   `ID` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `USER_ID` varchar(50) COLLATE utf8_general_ci DEFAULT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `FK4F6E52581590B46E` (`USER_ID`),
    ->   CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO TBL_USER (USER_ID) VALUES ('GIUSEPPE');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO TBL_COMMENT (USER_ID) VALUES ('GIUSEPPE');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO TBL_COMMENT (USER_ID) VALUES ('Giuseppe');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO TBL_COMMENT (USER_ID) VALUES ('giuseppe');
Query OK, 1 row affected (0.05 sec)

mysql> update TBL_USER set USER_ID = 'giuseppe' WHERE USER_ID = 'GIUSEPPE';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`BUG_TEST`.`TBL_COMMENT`, CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`))
mysql>

Same problem with character set latin1 and collation latin1_swedish_ci

______________________________________________

mysql> CREATE SCHEMA BUG_TEST CHARACTER SET latin1 COLLATE = latin1_swedish_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use  BUG_TEST;
Database changed
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | utf8_unicode_ci   |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> set collation_connection = latin1_swedish_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | utf8_unicode_ci   |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE `TBL_USER` (
    ->   `USER_ID` varchar(50) COLLATE latin1_swedish_ci NOT NULL,
    ->   PRIMARY KEY (`USER_ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE `TBL_COMMENT` (
    ->   `ID` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `USER_ID` varchar(50) COLLATE latin1_swedish_ci DEFAULT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   KEY `FK4F6E52581590B46E` (`USER_ID`),
    ->   CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO TBL_USER (USER_ID) VALUES ('GIUSEPPE');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO TBL_COMMENT (USER_ID) VALUES ('GIUSEPPE');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO TBL_COMMENT (USER_ID) VALUES ('Giuseppe');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO TBL_COMMENT (USER_ID) VALUES ('giuseppe');
Query OK, 1 row affected (0.05 sec)

mysql> update TBL_USER set USER_ID = 'giuseppe' WHERE USER_ID = 'GIUSEPPE';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`BUG_TEST`.`TBL_COMMENT`, CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`))

While this is easy to repeat and looks confusing (it seems that _ci collations take case into account), but I think this is not a bug formally. Please, read http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html:

"The action InnoDB takes for any UPDATE or DELETE operation that attempts to update or delete a candidate key value in the parent table that has some matching rows in the child table is dependent on the referential action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. When the user attempts to delete or update a row from a parent table, and there are one or more matching rows in the child table, InnoDB supports five options regarding the action to be taken. If ON DELETE or ON UPDATE are not specified, the default action is RESTRICT."

and then later:

"RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause."

If we redefine FOREIGN KEY with ON UPDATE CASCADE, it works:

mysql> alter table tbl_comment drop foreign key `FK4F6E52581590B46E`;
Query OK, 3 rows affected (0.41 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table tbl_comment add constraint `FK4F6E52581590B46E` foreign key (
`USER_ID`) references `tbl_user`(`USER_ID`) on update cascade;
Query OK, 3 rows affected (0.23 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbl_user;
+----------+
| USER_ID  |
+----------+
| GIUSEPPE |
+----------+
1 row in set (0.02 sec)

mysql> select * from tbl_comment;
+----+----------+
| ID | USER_ID  |
+----+----------+
|  1 | GIUSEPPE |
|  2 | Giuseppe |
|  3 | giuseppe |
+----+----------+
3 rows in set (0.00 sec)

mysql> update TBL_USER set USER_ID = 'giuseppe' WHERE USER_ID = 'GIUSEPPE';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbl_user;
+----------+
| USER_ID  |
+----------+
| giuseppe |
+----------+
1 row in set (0.00 sec)

mysql> select * from tbl_comment;
+----+----------+
| ID | USER_ID  |
+----+----------+
|  1 | giuseppe |
|  2 | giuseppe |
|  3 | giuseppe |
+----+----------+
3 rows in set (0.00 sec)

What you wrote is a workaround, not a proof this is not bug.
If I change the "ON UPDATE", I'm altering completely the scenario.

If my application executes:

update TBL_USER set USER_ID = 'mario' WHERE USER_ID = 'GIUSEPPE';

I might have a very unexpected behaviour (in a production environment) from an application that always expected to receive an error trying to do such a thing.

OK, let's call it a bug then: InnoDB does NOT detect that for _ci collation this update of PK ('GIUSEPPE' to 'Giuseppe') is a "no-op" update actually, rows in other table with foreign key will still have corresponding PK value.

[3 Jul 2013 20:04] Jimmy Yang:

This is not a bug per se. A few facts:

1) utf8_unicode_ci is case insensitive, so does other unicode collations. So
the behavior is correct, that is insertion is allowed. The update triggers a
delete + insert, the delete is disallowed.

2) The proper way to do such thing is:

mysql> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.03 sec)

mysql>  update TBL_USER set USER_ID = 'giuseppe' WHERE USER_ID = 'GIUSEPPE';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SET foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

This is what foreign_key_checks designed for

3) We have no plan to compare the before and after image of update for any
no-op operation, this adds unnecessary cost to all updates.