MySQL Bugs: #17294: INSERT DELAYED puting an \n before data

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

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'

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?

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

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.

The same problem while migrating from 4.1.18 to 5.0.22.

"insert delayed" statement corrupt data on VARCHAR column.
Any solutions?

Solution:

ALTER TABLE tbl_name ENGINE = MyISAM;

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.