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