Bug #29609 0 length string update does not work on varchar variables;
Submitted: 7 Jul 2007 8:23 Modified: 7 Aug 2007 11:46
Reporter: Farzin Rezaeian Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.15-nt-log OS:Microsoft Windows (show variables included)
Assigned to: CPU Architecture:Any

[7 Jul 2007 8:23] Farzin Rezaeian
Description:
combination of infile read into table and subsequent insert and update of values with 0 length string result in inconsistency. 
On consistent updates with 0 length string causes mysqld crashes.
Complete test case is included.

How to repeat:
mysql> create table testb (a BIGINT(20) unsigned NOT NULL default 0,b VARCHAR (255) DEFAULT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> select * from testb;
Empty set (0.00 sec)

mysql> load data infile 'D:\\testbug.txt' into table testb Fields terminated by '_#_' Lines terminated by '_LINEBREAK_' Ig
nore 1 Lines (a,b);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from testb;
+--------+------+
| a      | b    |
+--------+------+
|      1 | NULL |
| 123456 | NULL |
+--------+------+
2 rows in set (0.00 sec)

mysql> insert into testb (a) values(100);
Query OK, 1 row affected (0.02 sec)

mysql> select * from testb;
+--------+------+
| a      | b    |
+--------+------+
|      1 | NULL |
| 123456 | NULL |
|    100 | NULL |
+--------+------+
3 rows in set (0.00 sec)

mysql> update testb set b="";
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from testb;
+--------+------+
| a      | b    |
+--------+------+
|      1 |      |
| 123456 |      |
|    100 | NULL |
+--------+------+
3 rows in set (0.00 sec)

mysql>

testb.txt source:
a_#_b_LINEBREAK_1_#_NULL_LINEBREAK_123456_#_NULL_LINEBREAK_

Suggested fix:
Please help with this bug as I am currently stalling on my project because of this bug, or kindely tell me how to avoid it.
[7 Jul 2007 8:25] Farzin Rezaeian
source file

Attachment: testbug.txt (text/plain), 59 bytes.

[7 Jul 2007 8:26] Farzin Rezaeian
variables of system

Attachment: show variables.txt (text/plain), 21.34 KiB.

[7 Jul 2007 11:46] Sveta Smirnova
Thank you for the report.

But version 5.0.15 is quite old. Additionally I can not repeat described behavior with current development sources. Please upgrade to current version 5.0.41, try with it and say us result.
[7 Aug 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".