Bug #44251 Slave Server crash on when executing SP statements in slave

• Submitted: 13 Apr 2009 23:30
• Modified: 14 Apr 2009 3:35
• Reporter: Venu Anuganti
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.77
• OS: Any
• CPU Architecture: Any
• Tags: COllation bug, Slave crash for stored procedure

[13 Apr 2009 23:30] Venu Anuganti

Description:
The slave 5.0.77 crashes when executing the following statement:

DELETE FROM test_tab WHERE date( timestamp ) =  NAME_CONST('p_day',_binary'2009-04-12' COLLATE 'binary')

The statement is from bin-log generated by master with the following set of statements:

DROP TABLE IF EXISTS test_tab;
CREATE TABLE test_tab(timestamp datetime default NULL);

DROP PROCEDURE IF EXISTS test_date_proc;

delimiter //

CREATE PROCEDURE test_date_proc
   ( p_day DATE)
BEGIN
  DECLARE  v_begin_time INT;
  DECLARE  v_end_time   INT;
  DECLARE  v_time1      INT;
  DECLARE  v_time2      DATETIME;

  SET v_begin_time = unix_timestamp( p_day ) ;
  SET v_end_time   = v_begin_time + 86400 ;

  SET v_time1 = v_begin_time ;
  SET v_time2 = p_day ;
   
  DELETE FROM test_tab WHERE date( timestamp ) = p_day;
 
END;
//

delimiter ;

call test_date_proc('2009-04-12')

How to repeat:
repro 1:

CREATE TABLE test_tab(timestamp datetime default NULL);
DELETE FROM test_tab WHERE date( timestamp ) =  NAME_CONST('p_day',_binary'2009-04-12' COLLATE 'binary');

repro 2:
master and slave setup and execute the following in master:

DROP TABLE IF EXISTS test_tab;
CREATE TABLE test_tab(timestamp datetime default NULL);

DROP PROCEDURE IF EXISTS test_date_proc;

delimiter //

CREATE PROCEDURE test_date_proc
   ( p_day DATE)
BEGIN
  DECLARE  v_begin_time INT;
  DECLARE  v_end_time   INT;
  DECLARE  v_time1      INT;
  DECLARE  v_time2      DATETIME;

  SET v_begin_time = unix_timestamp( p_day ) ;
  SET v_end_time   = v_begin_time + 86400 ;

  SET v_time1 = v_begin_time ;
  SET v_time2 = p_day ;
   
  DELETE FROM test_tab WHERE date( timestamp ) = p_day;
 
END;
//

delimiter ;

call test_date_proc('2009-04-12');

and notce slave crashes; and upon restarting slave, it will keep crashing.

[14 Apr 2009 0:40] MySQL Verification Team

Thank you for the bug report. Could you please see bug: http://bugs.mysql.com/bug.php?id=42014 which will be fixed in version 5.0.78. Thanks in advance.

[14 Apr 2009 3:33] Venu Anuganti

Yes, thats the same problem...

work around, use CAST with NAMED VARIBLES WITHIN SP

like

delete from foo where date(ts) = day 

to

delete from foo where date(ts) = CAST(day as DATE)

[14 Apr 2009 3:35] Venu Anuganti

Duplicate of bug #42014

http://bugs.mysql.com/bug.php?id=42014