Bug #1302 Silent failure updating a blob and a non-blob column

• Submitted: 16 Sep 2003 8:35
• Modified: 16 Sep 2003 9:42
• Reporter: Karoly Lorentey
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.14
• OS: Linux (Debian GNU/Linux unstable)
• CPU Architecture: Any

[16 Sep 2003 8:35] Karoly Lorentey

Description:
MySQL 4.0.14 silently fails when I try to change the value of
both a blob and a non-blob column in a single UPDATE statement.

Sometimes the given values are replaced with garbage, sometimes
they are not changed at all.

The server makes absolutely no indication about the failure.

The bug seems not to be related to the table type.  I could reproduce
it with both InnoDB and MyISAM tables.

How to repeat:
mysql> create table test (a int, b blob) type=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1, 'foo');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+
| a    | b    |
+------+------+
|    1 | foo  |
+------+------+
1 row in set (0.00 sec)

mysql> update test set a = 2 and b = 'bar' where a = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+------+------+
| a    | b    |
+------+------+
|    0 | foo  |      <---------- CORRUPT DATA!
+------+------+
1 row in set (0.00 sec)

[16 Sep 2003 9:42] Alexander Keremidarski

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You use wrong syntax.

update test set a = 2 and b = 'bar' where a = 1;

MySQL parses this as:

update test set a = (2 and b = 'bar') where a = 1;

b = 'bar' -> false i.e. 0
2 and 0 -> false i.e. 1

Therefore a becomes 0

Proper way to update multiple columns at once is to use comma as separator:

update test set a = 2, b = 'bar' where a = 1;

[16 Sep 2003 10:07] Alexander Keremidarski

Sorry for mistake.

2 and 0 -> false i.e. 0 

not 1 as I originaly wrote

[16 Sep 2003 10:50] Karoly Lorentey

But of course!

Sorry for my brainfart, and thanks for the quick reply! :-)