MySQL Bugs: #41844: Foreign keys: failure with varbinary update cascade

Bug #41844	Foreign keys: failure with varbinary update cascade
Submitted:	3 Jan 2009 20:16	Modified:	17 Feb 2009 18:38
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	6.1.0-alpha	OS:	Linux (SUSE 10.0 / 32-bit)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I compiled without debug. That is, this is 6.1.0-alpha not 6.1.0-alpha-debug.
I start the server with mysqld --foreign-key-all-engines=1.

I create a primary-key table and a foreign-key table with VARBINARY columns.
The foreign-key table has an UPDATE CASCADE.
I insert some values.
I do some updates.
The foreign-key table has a row which is not in the primary-key table.

This happens only if engine=falcon.
This happens only if I add 0x00s.
This happen unreliably but is repeatable immediately after a fresh install.

How to repeat:
create table t1 (s1 varbinary(5) primary key) engine=falcon;
create table t2 (s1 varbinary(5) references t1(s1) on update cascade) engine=falcon;
insert into t1 values (0x00),('');
insert into t2 values (0x00),('');
update t1 set s1 = concat(s1,s1);
update t1 set s1 = concat(s1,0x00);
update t1 set s1 = concat(s1,0x00);
select hex(s1) from t1;
select hex(s1) from t2;

Example run:

pgulutzan@linux:~> /usr/local/mysql/bin/mysql --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.1.0-alpha Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> create table t1 (s1 varbinary(5) primary key) engine=falcon;
Query OK, 0 rows affected (0.14 sec)

mysql> create table t2 (s1 varbinary(5) references t1(s1) on update cascade) engine=falcon;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 values (0x00),('');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (0x00),('');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update t1 set s1 = concat(s1,s1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> update t1 set s1 = concat(s1,0x00);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update t1 set s1 = concat(s1,0x00);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select hex(s1) from t1;
+----------+
| hex(s1)  |
+----------+
| 00000000 |
| 0000     |
+----------+
2 rows in set (0.00 sec)

mysql> select hex(s1) from t2;
+----------+
| hex(s1)  |
+----------+
| 00000000 |
| 00       |
+----------+
2 rows in set (0.00 sec)

Thank you for the bug report. Verified as described:

c:\dbs>6.1\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.1.0-alpha-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (s1 varbinary(5) primary key) engine=falcon;
Query OK, 0 rows affected (0.13 sec)

mysql> create table t2 (s1 varbinary(5) references t1(s1) on update cascade) engine=falcon;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t1 values (0x00),('');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (0x00),('');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update t1 set s1 = concat(s1,s1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> update t1 set s1 = concat(s1,0x00);
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> update t1 set s1 = concat(s1,0x00);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select hex(s1) from t1;
+----------+
| hex(s1)  |
+----------+
| 00000000 |
| 0000     |
+----------+
2 rows in set (0.03 sec)

mysql> select hex(s1) from t2;
+----------+
| hex(s1)  |
+----------+
| 00000000 |
| 00       |
+----------+
2 rows in set (0.00 sec)

mysql>

Hello!

Analysis shows that this bug is caused by the fact that last update statement fails to find row with 0x00 value in the child table while performing cascading actions.

The same effect can be observed if one replaces this last update with the following select "select hex(s1) from t2 where s1 = 0x00".

Moreover the same effect can be observed in the following case where there are no cascading updates at all:

create table t1 (s1 varbinary(5) primary key) engine=falcon;
create table t2 (s1 varbinary(5) references t1(s1) on update cascade) engine=falcon;
insert into t1 values (0x00), (''), (0x0000), (0x000000);
insert into t2 values (0x00),('');
update t2 set s1= concat(s1,s1);
update t2 set s1= concat(s1,0x00);
select hex(s1) from t1;
select hex(s1) from t2;
select hex(s1) from t2 where s1 = 0x00;
# Returns empty set!

Therefore it is likely that this problem has nothing to do with new foreign key implementation.

Since symptoms of this bug look very similar to those of bug #42196 I will wait until fix for the latter appears in mysql-6.1-fk and check if problem is repeatable with it.

Update. Merging changes from mysql-6.0-falcon which is supposed to contain fix for bug #42196 doesn't help. But OTOH I have found bug #34478 "Falcon: search failure with varbinary = 0x00" which seems to describe similar problem.

I've performed test merge of changes from mysql-6.0-falcon-team tree which contains fix for bug #34478 with mysql-6.1-fk and confirmed that this bug goes away. This and results of prior investigation confirm that this bug is duplicate of bug #34478.