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);