Bug #16494 Updates that set a column to NULL fail sometimes
Submitted: 13 Jan 2006 20:09 Modified: 18 Aug 2006 17:07
Reporter: Björn Steinbrink Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S1 (Critical)
Version:5.0.18/5.0.19 BK/5.0.24 OS:Linux (Linux)
Assigned to: Jim Winstead CPU Architecture:Any

[13 Jan 2006 20:09] Björn Steinbrink
Description:
When executing an UPDATE statement on a federated table, it sometimes fails with a strange error message. The failure does only happen on some rows in the table, but for those it seems to be permanent.

mysql> desc ns1_records;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| domain_id   | int(11)      | NO   | MUL | 0       |                |
| name        | varchar(255) | YES  |     | NULL    |                |
| type        | varchar(6)   | YES  |     | NULL    |                |
| content     | varchar(255) | YES  |     | NULL    |                |
| ttl         | int(11)      | YES  |     | NULL    |                |
| prio        | int(11)      | YES  |     | NULL    |                |
| change_date | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
8 rows in set (0.03 sec)

This row works fine:
---------------------
mysql> UPDATE ns1_records SET prio = 1 WHERE id = 2232;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE ns1_records SET prio = NULL WHERE id = 2235;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE ns1_records SET prio = 1 WHERE id = 2235;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE ns1_records SET prio = NULL WHERE id = 2235;
Query OK, 0 rows affected (0.07 sec)
Rows matched: 0  Changed: 0  Warnings: 0

This one fails:
--------------
mysql> UPDATE ns1_records SET prio = NULL WHERE id = 2232;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'prio1' in 'where clause'' from FEDERATED

mysql> UPDATE ns1_records SET prio = 5 WHERE id = 2232;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE ns1_records SET prio = NULL WHERE id = 2232;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'prio5' in 'where clause'' from FEDERATED

Note that the mentioned fields, prio1 and prio5 don't appear in any where clause at all and the numbers in them are equal to the value that the prio column is set to when the statement is executed.
Unfortunately, I have not found a way to get a row into 'corrupted' state.

How to repeat:
Unknown
[13 Jan 2006 22:14] MySQL Verification Team
Could you please provide a dump with create table and inserts that it will
permit to reproduce the behavior reported ?

Thanks in advance.
[13 Jan 2006 23:01] Björn Steinbrink
Hm, the last time I tried to reproduce it, I failed, so I'm not sure that this will work for you, it did on two different boxes here though (well, 4 including the remote databases).

On the remote box:
--------------------
mysql> create database bug;
Query OK, 1 row affected (0.02 sec)

mysql> use bug
Database changed

mysql> create table foo (id int(11) NOT NULL auto_increment, val int(11) default NULL, PRIMARY KEY (id)) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON bug.foo TO 'user'@'%';
Query OK, 0 rows affected (0.06 sec)

On the local box:
-----------------
mysql> create database bug;
Query OK, 1 row affected (0.04 sec)

mysql> create table foo (id int(11) NOT NULL auto_increment, val int(11) default NULL, PRIMARY KEY (id)) ENGINE=FEDERATED CONNECTION='mysql://user:pass@remote/bug/foo';
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO foo VALUES (1, 0);
Query OK, 1 row affected (0.14 sec)

mysql> INSERT INTO foo VALUES (2, 0);
Query OK, 1 row affected (0.02 sec)

mysql> UPDATE foo SET val = NULL WHERE id = 1;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'val0' in 'where clause'' from FEDERATED

It only fails when the column value is not NULL. On the remote database, I can still set the value to NULL and when I do this, the statement works on the federated table, too.

Remote
--------
mysql> UPDATE foo SET val = NULL WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Local
-----
mysql> UPDATE foo SET val = NULL WHERE id = 1;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE foo SET val = 1 WHERE id = 1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE foo SET val = NULL WHERE id = 1;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'val1' in 'where clause'' from FEDERATED
[14 Jan 2006 9:53] MySQL Verification Team
Thank you for the bug report.

mysql> create database bug;
Query OK, 1 row affected (0.05 sec)

mysql> use bug;
Database changed
mysql> create table foo (id int(11) NOT NULL auto_increment, val int(11) default
    -> NULL, PRIMARY KEY (id)) ENGINE=FEDERATED
    -> CONNECTION='mysql://user:nana@192.168.0.119/bug/foo';
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO foo VALUES (1, 0);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO foo VALUES (2, 0);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE foo SET val = NULL WHERE id = 1;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1054: Unknown column 'val0' in 'where clause'' from FEDERATED
mysql>
[14 Jan 2006 20:39] Björn Steinbrink
Patch to fix the bug

Attachment: mysql-5.0.18-federated-NULL.patch (application/octet-stream, text), 1.21 KiB.

[14 Jan 2006 20:43] Björn Steinbrink
The bug was caused by the where clause being dependent on the new value of the field and not only on the old value. The above patch fixes that. What I still wonder about is the fact that it actually suceeded for some rows, from my understanding it should've always failed, but this was the first time I touched the MySQL source code, so I might be wrong here ;)
[11 Mar 2006 10:49] dna
Hello,

t've the same bug here.
Is this patch ok or not?

Why is there no activity since 3 months for this critical bug?
[25 Mar 2006 13:42] Björn Steinbrink
I just noticed that my initial example of a case in which the bug does not appear was broken. In the given updates, no rows were actually affected by the update, ie. the used where clause matched no rows. So this bug is always happening.
Besides that, I'm quite surprised that there still is not a single comment since the bug was confirmed. I know that there might be other important issues, but I already supplied a patch for this one and I'd at least expect a comment that says whether the patch is ok or not. If it is not, well, fine, I had no prior experience with the MySQL source code, so that might happen, but please tell me if this is the case.
If I missed any rules for submitting patches, or should have sent the patch elsewhere, I'd also appreciate a comment telling me about my mistake.
[26 Jun 2006 23:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8277
[10 Jul 2006 7:20] Ingo Strüwing
Pushed to 5.0.24.
[23 Jul 2006 3:52] Paul DuBois
Noted in 5.0.24, 5.1.12 changelogs.

Updating a column of a FEDERATED table to NULL sometimes failed.
[18 Aug 2006 7:35] Björn Steinbrink
The patch did not actually get into the 5.0.24 release, we finally got around to update the MySQL server (dotdeb package) and were pretty frustrated to see that it still failed. So I fetched the source release from mysql.com and saw that the file in question is still unchanged :( (I assume that the binaries on mysql.com match the source release and did not test them specifically, and if they differ that's even worse).
[18 Aug 2006 15:46] Jim Winstead
Sorry, because of how 5.0.24 was released to get a security fix out, this patch didn't actually make it into that release. It will be in 5.0.25.
[18 Aug 2006 17:07] Paul DuBois
I have moved the 5.0.x changelog from 5.0.24 to 5.0.25.