MySQL Bugs: #13900: DATETIME data changes after inserting a new row in a InnoDB table

Bug #13900	DATETIME data changes after inserting a new row in a InnoDB table
Submitted:	10 Oct 2005 15:54	Modified:	20 Oct 2005 20:38
Reporter:	Thoralf Rickert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.13-rc	OS:	Linux (Linux Debian Sarge)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
I think I've found a strange bug and try to find a solution for two days - but nothing. I can reproduce it every time with different data in a MySQL 5.0.13-rc environment. I couldn't reproduce it with mysql 4.0.

I'd like to insert some rows in a InnoDB table with a transaction. After a while an old row changes the data of a DATETIME field. I've downgraded my problem to a very simple example.

How to repeat:
Here is the SQL code:

DROP DATABASE test;
CREATE DATABASE test;
use test;
CREATE TABLE store ( id BIGINT NOT NULL AUTO_INCREMENT, website VARCHAR (255), exists_since_time DATETIME, PRIMARY KEY(id) ) Type=InnoDB;

BEGIN;
INSERT INTO store ( website, exists_since_time ) VALUES ( NULL, NULL );
UPDATE store SET website = '', exists_since_time = '2002-04-01 00:00:00' WHERE ID = 1;

INSERT INTO store ( website, exists_since_time ) VALUES ( NULL, NULL );
UPDATE store SET website = '', exists_since_time = '1995-01-05 00:00:00' WHERE ID = 2;

INSERT INTO store ( website, exists_since_time ) VALUES ( NULL, NULL );
UPDATE store SET website = '', exists_since_time = NULL WHERE ID = 3;

INSERT INTO store ( website, exists_since_time ) VALUES ( NULL, NULL );
UPDATE store SET website = '', exists_since_time = '1999-02-02 00:00:00' WHERE ID = 4;

INSERT INTO store ( website, exists_since_time ) VALUES ( NULL, NULL );
UPDATE store SET website = '', exists_since_time = '2000-04-01 00:00:00' WHERE ID = 5;

INSERT INTO store ( website, exists_since_time ) VALUES ( NULL, NULL );
UPDATE store SET website = '', exists_since_time = NULL WHERE ID = 6;
COMMIT;

After this the second row contains a special character (0x01) in the "exist_since_time" column. See this:

mysql> select * from store;
+----+---------+---------------------+
| id | website | exists_since_time   |
+----+---------+---------------------+
|  1 |         | 2002-04-01 00:00:00 |
|  2 |         | ?720-24-47 77:58:08 |
|  3 | NULL    | NULL                |
|  4 |         | 1999-02-02 00:00:00 |
|  5 |         | 2000-04-01 00:00:00 |
|  6 | NULL    | NULL                |
+----+---------+---------------------+

When I comment out the last INSERT and UPDATE and rerun the script, the second row is correct:

+----+---------+---------------------+
| id | website | exists_since_time   |
+----+---------+---------------------+
|  1 |         | 2002-04-01 00:00:00 |
|  2 |         | 1995-01-05 00:00:00 |
|  3 | NULL    | NULL                |
|  4 |         | 1999-02-02 00:00:00 |
|  5 |         | 2000-04-01 00:00:00 |
+----+---------+---------------------+

So, this could NOT be a problem with the SQL syntax during inserting and updating the second row...

Here is the installation procedure (from source):

#>./configure --prefix=/usr/local/mysql5 --with-charset=latin1
#>make
#>make install

and the configuration file changes:

default-character-set = latin1
collation-server = latin1_german2_ci

Suggested fix:
I don't have a fix but a workaround:

Making first all INSERTs and then all UPDATEs is okay.
But I produce the SQL in a for-loop and lose the data. So I don't know the data after the first loop.

mysql> select * from store;
+----+---------+---------------------+
| id | website | exists_since_time   |
+----+---------+---------------------+
|  1 |         | 2002-04-01 00:00:00 |
|  2 |         | 720-24-47 77:58:08 |
|  3 | NULL    | NULL                |
|  4 |         | 1999-02-02 00:00:00 |
|  5 |         | 2000-04-01 00:00:00 |
|  6 | NULL    | NULL                |
+----+---------+---------------------+
6 rows in set (0.00 sec)
-------------------------------------
mysql> select * from store;
+----+---------+---------------------+
| id | website | exists_since_time   |
+----+---------+---------------------+
|  1 |         | 2002-04-01 00:00:00 |
|  2 |         | 1995-01-05 00:00:00 |
|  3 | NULL    | NULL                |
|  4 |         | 1999-02-02 00:00:00 |
|  5 |         | 2000-04-01 00:00:00 |
+----+---------+---------------------+
5 rows in set (0.00 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.15-rc-debug |
+-----------------+
1 row in set (0.00 sec)

I think I have found the bug.

Marko has now emailed the patch to Elliot.

Tried applying patch on Friday, saw test failures, emailed to Marko today. Also rerunning tests.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31185

Tried again with a fresh tree, tests are passing now.
Will push in a couple of hours, fix will be available for 5.0.16

Pushed for 5.0.16

Noted in 5.0.16 changelog.