Bug #45448 Query with datetime parameter taken from variable crashes replication slave.
Submitted: 11 Jun 2009 11:39 Modified: 16 Jun 2009 7:34
Reporter: Nick Ivannikov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.30 OS:Linux
Assigned to: CPU Architecture:Any
Tags: replication

[11 Jun 2009 11:39] Nick Ivannikov 
Description:
I have discovered that under certain conditions mysql replication slave chashes with sigterm.

After crashing it restarts and fails again and again. In err file on slave I have found suspicous query:

DELETE
FROM
  sessions
WHERE
  start_time >  NAME_CONST('_clean_date',_binary'2009-06-11 13:44:11' COLLATE 'binary')

This query is surrounded with stack traces:) In the stored procedure which was called from master query looks like:

DELETE FROM sessions WHERE start_time > _clean_date;

If _clean_date is replaced with NOW() for example, no error occurs. So I suggest problem is in datetime parameter, taken from variable.

Table "sessions" and sql of procedure you can see in How to repeat.

Err file is in attachment.

Any answer is much appriciated.

How to repeat:
Create table:

CREATE TABLE sessions(
  id CHAR (36) NOT NULL,
  domain_id INT (11) NOT NULL,
  record_file_name CHAR (250) DEFAULT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  is_incoming TINYINT (4) NOT NULL DEFAULT 0,
  is_successful TINYINT (4) NOT NULL DEFAULT 1,
  rtp_ip VARCHAR (20) DEFAULT NULL,
  INDEX sessions_idx USING BTREE (id),
  INDEX domain_id USING BTREE (domain_id),
  INDEX is_incoming USING BTREE (is_incoming),
  INDEX is_successful USING BTREE (is_successful)
)
ENGINE = MYISAM
CHARACTER SET utf8
COLLATE utf8_bin;

And a stored procedure:

CREATE DEFINER = 'root' @'localhost'
PROCEDURE crash()
BEGIN
  DECLARE _clean_date DATETIME;

  SET _clean_date = NOW();
  DELETE
  FROM
    sessions
  WHERE
    start_time > _clean_date;
END

Then issue query:

call crash();

from master replication server. At this time slave server chashes. No matter if table is empty.
[11 Jun 2009 11:40] Nick Ivannikov 
Err file from crashed slave server.

Attachment: fedora9.err (application/octet-stream, text), 30.25 KiB.
[11 Jun 2009 14:16] Valeriy Kravchuk 
This looks like a duplicate of Bug #42014 (fixed since 5.1.32). Please, check.
[12 Jun 2009 5:58] Nick Ivannikov 
Thanks for your reply!

I'll check if problem was fixed in 5.1.32 soon. I have searched for bug with keyword "replication". Thats why I missed it.
[12 Jun 2009 6:19] Valeriy Kravchuk 
Please, inform about any results of your tests.
[16 Jun 2009 5:23] Nick Ivannikov 
Upgrade to 5.1.33 solve the issue! Thanks for advice, it save me a lot of time.
[16 Jun 2009 7:34] Valeriy Kravchuk 
So, this was a duplicate of Bug #42014.