Bug #17294 INSERT DELAYED puting an \n before data
Submitted: 10 Feb 2006 8:04 Modified: 11 Jul 2006 8:34
Reporter: Nicolas de Bari Embriz Garcia Rojas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.0.19-BK, 5.0.18 OS:Linux (Linux, FreeBSD)
Assigned to: Ingo Strüwing CPU Architecture:Any

[10 Feb 2006 8:04] Nicolas de Bari Embriz Garcia Rojas
Description:
when inserting data to a table with the DELAYED option, data will be sored with an \ndata

How to repeat:
INSERT DELAYED INTO table SET `from`='5555555555'
[10 Feb 2006 9:43] Valeriy Kravchuk
Thank you for a problem report. Please, send the SHOW CREATE TABLE resutls for your table. Is it created in 5.0.18 or in any 4.x.y version of MySQL and now simply used after the upgrade?
[10 Feb 2006 18:20] Nicolas de Bari Embriz Garcia Rojas
Hello, this problem began when i updated from mysql 4 to 5.0.18

mysql> show create table kannel \G
*************************** 1. row ***************************
Table       : kannel
Create Table: CREATE TABLE `kannel` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `from` varchar(20) NOT NULL default '',
  `to` varchar(10) NOT NULL default '',
  `body` varchar(160) NOT NULL default '',
  `date` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `from` (`from`),
  KEY `body` (`body`),
  KEY `to` (`to`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 rows in set (1.72 sec)

When i do a simple INSERT with out using the DELAYED options the data is storred correctly but when usig the DELAYED option i get the \n before the data.

I have tryied to do this:

update kannel SET `from` = trim(`from`)  or  rtrim and I can not remove the \n so maybe it is not an \n

regards
[19 Feb 2006 14:27] Valeriy Kravchuk
I had created the table as you described on 4.0.27-BK on Linux, put some smaple data into it, then copied table's files to 5.0.19-BK:

openxs@suse:~/dbs/4.0> cp var/test/kan* ../5.0/var/test
openxs@suse:~/dbs/4.0> cd ../5.0/
openxs@suse:~/dbs/5.0> bin/mysqld_safe &
[1] 28230
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

openxs@suse:~/dbs/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> select * from `kannel`;
+----+---------+---------+------+---------------------+
| id | from    | to      | body | date                |
+----+---------+---------+------+---------------------+
|  1 | a@a.com | a@b.com | abc  | 2006-02-19 00:00:00 |
|  2 | a@a.com | a@b.com | abc  | 2006-02-19 00:00:00 |
|  3 | a@a.com | a@b.com | abc  | 2006-02-19 00:00:00 |
|  4 | a@a.com | a@b.com | abc  | 2006-02-19 00:00:00 |
|  5 | a@a.com | a@b.com | abc  | 2006-02-19 00:00:00 |
|  6 | a@a.com | a@b.com | abc  | 2006-02-19 00:00:00 |
|  7 | a@a.com | a@b.com | abc  | 2006-02-19 00:00:00 |
+----+---------+---------+------+---------------------+
7 rows in set (0.00 sec)

mysql> insert delayed into `kannel` set `from` = '555555555';
Query OK, 1 row affected (0.00 sec)

mysql> select * from `kannel`;
+----+------------+---------+------+---------------------+
| id | from       | to      | body | date                |
+----+------------+---------+------+---------------------+
|  1 | a@a.com    | a@b.com | abc  | 2006-02-19 00:00:00 |
|  2 | a@a.com    | a@b.com | abc  | 2006-02-19 00:00:00 |
|  3 | a@a.com    | a@b.com | abc  | 2006-02-19 00:00:00 |
|  4 | a@a.com    | a@b.com | abc  | 2006-02-19 00:00:00 |
|  5 | a@a.com    | a@b.com | abc  | 2006-02-19 00:00:00 |
|  6 | a@a.com    | a@b.com | abc  | 2006-02-19 00:00:00 |
|  7 | a@a.com    | a@b.com | abc  | 2006-02-19 00:00:00 |
|  8 |  555555555 |         |      | NULL                |
+----+------------+---------+------+---------------------+
8 rows in set (0.00 sec)

mysql> select hex(`from`) from `kannel` where id=8;
+----------------------+
| hex(`from`)          |
+----------------------+
| 09353535353535353535 |
+----------------------+
1 row in set (0.00 sec)

So, in my case it was TAB character (0x09), but data are corrupted, anyway.
[9 Jun 2006 7:36] Dmitry Klimov
The same problem while migrating from 4.1.18 to 5.0.22.

"insert delayed" statement corrupt data on VARCHAR column.
Any solutions?
[9 Jun 2006 8:04] Dmitry Klimov
Solution:

ALTER TABLE tbl_name ENGINE = MyISAM;
[12 Jun 2006 7:00] Ingo Strüwing
The patch for this bug is contained in the patch for Bug #16218.
[16 Jun 2006 11:30] Ingo Strüwing
Patch rejected by Monty.
[26 Jun 2006 19:22] Ingo Strüwing
The patch for this bug is contained in the patch for Bug #16218.
[6 Jul 2006 17:37] Ingo Strüwing
Pushed to mysql-5.1-engines.
[6 Jul 2006 19:50] Ingo Strüwing
Pushed to mysql-5.0-engines.
[8 Jul 2006 17:18] Ingo Strüwing
[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.
[11 Jul 2006 8:34] MC Brown
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)
[13 Jul 2006 3:33] Paul Dubois
5.0.x fix went to 5.0.25 instead.