MySQL Bugs: #16611: INSERT DELAYED corrupts data

Bug #16611	INSERT DELAYED corrupts data
Submitted:	18 Jan 2006 16:50	Modified:	11 Jul 2006 8:58
Reporter:	Nickolaus Wing	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.19-BK, 5.0.18	OS:	Linux (Linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
I'm using Perl + DBI to communicate, running the following:

$dbh->do(qq|INSERT INTO usersessionpaths (sessionid, ipaddr, url, accesstime) VALUES (?, ?, ?, ?)|, undef, $sessid, $ENV{'REMOTE_ADDR'}, $url, $timestamp);

This works as expected.

Since this is non-critical logging, I decided to switch to INSERT DELAYED.  Now data in the sessionid column begins with a non-standard junk character. I fill $sessid with 20 random alphanumeric characters, and when I query it back out, I receive 21, the first of which is not alphanumeric.  In fact, I cannot reprint the character in this text box because it messes up.

I switched back to a non-delayed insert and the problem disappeared.  INSERT DELAYED caused the error consistently for about a week before I noticed.

Here are the results of show create table:
CREATE TABLE `usersessionpaths` (`id` int(11) NOT NULL auto_increment, `sessionid` varchar(40) NOT NULL default '', `ipaddr` tinytext NOT NULL,  `url` longtext NOT NULL, `accesstime` bigint(14) NOT NULL default '0', PRIMARY KEY  (`id`), KEY `sessionid` (`sessionid`), KEY `accesstime` (`accesstime`)) ENGINE=MyISAM DEFAULT CHARSET=latin1

How to repeat:
I tried to reproduce the error in a fresh table with queries sent through phpMyAdmin but was unsuccessful.  The table in which the problem arose has over a million rows, which may make a difference.

Thank you for a problem report. Was that problematic table created in pre-5 version of MySQL? (4.1.x, for example) If yes, then, please, describe your upgrade procedure. If no, please, try to CHECK that table and inform about the results.

We reproduced this bug today with mysql 5.0.18, using a table created with a pre-5 mysql (we do not know the specific version though, sadly).

CHECK, OPTIMIZE, REPAIR, didn't fix the problem. When copying the data to another table (using CREATE TABLE... followed by INSERT SELECT..., the problematic rows are skipped. 

We fixed the problem by using ALTER TABLE tablename type=myisam. I'll attach a .frm file of the table before alteration.

You can download our bogus .frm file here: http://devteam.skyblog.com/article_7.frm (9.0k)

Looks very similar to http://bugs.mysql.com/bug.php?id=13707, although, that one should be fixed...

Frank,

So, your table was InnoDB and you fixed it by moving it to MyISAM? This sounds like something different from the original bug report (it was for MyISAM).

We did upgrade from 4, I'm not sure what version created the table, it was created sometime in early 2004.

When we updated to mysql 5.0, we copied the files directly to another server, completely reinstalled the server software (also switched OS to CentOS 4, it had been Red Hat 7 or so), and then copied the files back.

Hello, i'm Frank co-worker, i will answer for him about this problem.

The table was already a MyISAM table. Not an InnoDB.

We were making this table under mysql 4.0 or 4.1 we dont remember.
For migration we juste update mysql server from 4.1 to 5.0.18 on our gentoo linux, and restart the server with same files.

We had the idea to make a ALTER TABLE to redefine the storage engine because we had compare 2 .frm file of the same table structure, one with the boggus table created with mysql 4.x server, and the other with the mysql 5.0.18.
We compare the 2 files using unix command "strings" on each .frm fle.
In the MySQL 4.x frm file, a string "MyISAM" was missing in the file.

The problem can be reproduice with few raws (with an empty table), using MySQL CLI or PhpMyAdmin.

Exemple :

Query :
INSERT DELAYED INTO `article_0` ( `id` , `id_skynaute` , `pseudo` , `title` , `text` , `image` , `small_image` , `created_on` , `created_from` , `modified_on` , `modified_from` , `onLineBloger` , `onLineAdmin` , `image_align` , `validated` , `show_home` ) 
VALUES (
'1', '2', 'test3', 'test4', 'test5', 'test6', 'test7', '0000-00-00 00:00:00', 'test8', '0000-00-00 00:00:00', 'test9', '0', '1', '0', '0', '1'
);

Dump of the datas :
INSERT INTO `article_0` VALUES (1, 2, 'test3', 'test4', 'test5', 'test6', 'test7', '0000-00-00 00:00:00', 'test8', '0000-00-00 00:00:00', 'test9', 0, 1, 0, 0, 1);

Migration to mysql 5.0.x is almost impossible to do for us with this problem, because we don't have enought time to ALTER TABLE all our 10 production tables (on 2 servers) in MyIsam pre-5, 10GB of data each, near 178 000 000 rows at all.

Test case to reproduice this bug :
1/ Create a very simple myisam table with only one varchar field on a mysqld 4.x.y (also tested with tables created on mysqld 4.1.13, 4.1.16 and 4.0.22). ( CREATE TABLE `test1` (`test` VARCHAR(16) NOT NULL) TYPE = MYISAM; )
2/ Copy tables files (.frm .MYD .MYI) somewhere on a MySQL 5.0.18 datadir.
3/ Execute a DELAYED INSERT query on this table with MySQL 5.0.18.
4/ Having a look on corrupted results. :(

This bug don't seem to depend on the OS. I also reproduce with the win32 release of mysqld 5.0.18.

bash-3.00$ mysql -uroot -pXXXXXX
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-nt

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

mysql> use test;
Database changed
mysql> insert delayed into test1 values ('toto');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------------------+
| test             |
+------------------+
| ♦toto♦           |
+------------------+
1 row in set (0.00 sec)

Verified with 4.1.16 migrated to latest 5.0.19-BK (ChangeSet@1.1992, 2006-01-19 16:13:04+01:00) as described by Patrice:

[root@Fedora 5.0]# /etc/init.d/mysql start
Starting MySQL.. SUCCESS!
[root@Fedora 5.0]# mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-standard

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

mysql> show variables like 'data%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test1` (`test` VARCHAR(16) NOT NULL) TYPE
    -> = MyISAM;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> exit
Bye
[root@Fedora 5.0]# /etc/init.d/mysql stop
Shutting down MySQL. SUCCESS!
[root@Fedora 5.0]# cp /var/lib/mysql/test/test1.* var/test
[root@Fedora 5.0]# chown openxs var/test/test1.*
[root@Fedora 5.0]# exit
exit
[openxs@Fedora 5.0]$ bin/mysqld_safe &
[1] 6936
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

[openxs@Fedora 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19

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

mysql> insert delayed into test1 values ('abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert delayed into test1 values ('abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert delayed into test1 values ('abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+
| test |
+------+
| ♥abc |
| ♥abc |
| ♥abc |
+------+
3 rows in set (0.00 sec)

Please, note although, that the only proper way to migrate from pre-5 to 5.0.x now is with dump and restore.

Linked to this bug with, there is a security issue with the currupted data using a pre5 myisam table :

mysql> truncate table test1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert delayed into test1 values ('0123456789abcdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert delayed into test1 values ('Z');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------------------+
| test             |
+------------------+
| ►0123456789abcde |
| ☺Z123456789abcde |
+------------------+
2 rows in set (0.00 sec)

the 2nd row contain the data of the precedent longest row.

And on win32 mysqld 5.0.18, delaying an insert of a string longuest than the field size (16 in my test table) made mysqld crash.

mysql> truncate table test1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert delayed into test1 values ('0123456789abcdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert delayed into test1 values ('z');
Query OK, 1 row affected (0.00 sec)

(here the data are still corrupt the same way.)

mysql> insert delayed into test1 values ('0123456789abcdefg');
ERROR 2013 (HY000): Lost connection to MySQL server during query

and the server's gone.

Corruption of data to tables that are not dumped / restored when migrating from 4.* to 5.0 is expected behaviour.

It is documented in our manual under non-compatible changes in 5.0.

The only bug here is a crash of the server.

Sure this sort of comment will help users and system administrator to migrate their production plateform from mysqld 4.x to 5... 

I just have a look on mysql manual, and i read that MySQL "recommand" to dump/restore, it's dont seem to be a need.

So the conclusion now is "stay on mysql 4.x, when upgrading on mysql 5, you will have to dump and reinject all your data ! have fun !".
And i'm pretty sure i will have to shutdown my service during more than one week to dump and insert all my 178 000 000 rows of articles, and 276 000 000 of comments stored in my MySQL 4.1.x database... hope you will support mysql 4.1.x a long time.

Sorry, i'm really sad of this answer from a mysqldev :/

The patch for this bug is contained in the patch for Bug #16218.

Patch rejected by Monty.

The patch for this bug is contained in the patch for Bug #16218.

Pushed to mysql-5.1-engines.

Pushed to mysql-5.0-engines.

[Same patch and same comment as for Bug #16218.]

  INSERT DELAYED crashed in 5.0 on a table with a varchar that 
  could be NULL and was created pre-5.0 (Bugs 16218 and 13707).
  INSERT DELAYED corrupted data in 5.0 on a table with varchar 
  fields that was created pre-5.0 (Bugs 17294 and 16611).
  
  In case of INSERT DELAYED the open table is copied from the
  delayed insert thread to be able to create a record for the 
  queue. When copying the fields, a method was used that did 
  convert old varchar to new varchar fields and did not set up 
  some pointers into the record buffer of the table.
  
  The field conversion was guilty for the misinterpretation of 
  the record contents by the delayed insert thread. The wrong
  pointer setup was guilty for the crashes.
  
  For Bug 13707 (Server crash with INSERT DELAYED on MyISAM table)
  I fixed the above mentioned method to set up one of the pointers.
  For Bug 16218 I set up the other pointers too.
  
  But when looking at the corruptions I got aware that converting
  the field type was totally wrong for INSERT DELAYED. The copied
  table is used to create a record that is to be sent to the
  delayed insert thread. Of course it can interpret the record
  correctly only if all field types are the same in both table
  objects.
  
  So I revoked the fix for Bug 13707 and changed the new_field() 
  method so that it can suppress conversions.

Pushed to 5.1.12 and 5.0.24.

Documented in the 5.0 and 5.1 changelog:

Using tables from MySQL 4.x in MySQL 5.x, in particular those with <literal>VARCHAR</literal> fields and using <literal>INSERT DELAYED</literal> to update data in the table would result in either data corruption or a server crash. (Bug #16611, Bug #16218, Bug #17294)

5.0.x fix went to 5.0.25 instead.