Bug #48062 Date artithmetic in stored procedure breaks replication
Submitted: 14 Oct 2009 23:09 Modified: 15 Oct 2009 3:34
Reporter: Jeremy Grodberg Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.85, 5.1.41 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Tags: replication, stored programs

[14 Oct 2009 23:09] Jeremy Grodberg
Description:

(Statement-based) Replication of stored procedures loses the context that a local variable is a DATETIME, causing type conversion problems in replication, which cause data to be different on the slave.

How to repeat:

Example:
<code>
CREATE DATABASE test;
USE test;
CREATE TABLE `test`.`time_test` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `logtime` DATETIME default null,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;
delimiter //

CREATE PROCEDURE `sp_test`(
 IN oldDate DATETIME
)
BEGIN
	START TRANSACTION;
		INSERT INTO `test`.`time_test` (logtime)
			VALUES(oldDate+120000);
	COMMIT;
    END
//

call sp_test('2009-10-14 00:00:00');
</code>

On the master, you get:

mysql> select * from time_test;
+----+---------------------+
| id | logtime             |
+----+---------------------+
|  1 | 2009-10-14 12:00:00 |
+----+---------------------+

On the slave, you get:

mysql> select * from time_test;
+----+---------------------+
| id | logtime             |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+

The problem comes from the binlog statement sent to the slave:

INSERT INTO `test`.`time_test` (logtime)
   VALUES( NAME_CONST('oldDate',_binary'2009-10-14 00:00:00' COLLATE 'binary')+120000)

Where the data type of NAME_CONST is not a DATETIME and so its value is instead taken as 2009, the result of the addition is 122009, and that is not a valid number to use to construct a DATETIME.

Suggested fix:

Adding an explicit CAST to the binlog statement fixes the problem:

INSERT INTO `test`.`time_test` (logtime)
   VALUES( CAST(NAME_CONST('oldDate',_binary'2009-10-14 00:00:00' COLLATE 'binary') as DATETIME)+120000)

As a workaround, using explicit date arithmetic solves the problem:

INSERT INTO `test`.`time_test` (logtime)
VALUES(oldDate + interval 12 hour);
[15 Oct 2009 3:25] Valeriy Kravchuk
Looks like closely related to bug #42014. That bug was not fixed completely it seems.
[15 Oct 2009 3:34] Valeriy Kravchuk
Thank you for the bug report. Verified just as described, also with latest 5.1.41 from bzr.