| Bug #66637 | replication break with error 1253 'COLLATION 'latin1_swedish_ci' is not valid | ||
|---|---|---|---|
| Submitted: | 31 Aug 2012 21:22 | Modified: | 3 May 2013 14:11 |
| Reporter: | raouf Hanna | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.5.24, 5.5.29, 5.7.0 | OS: | Linux (2.6.18-164.11.1.el5 #1 SMP Wed Jan 6 13:26:04 EST 2010 x86_64 x86_64 x86_64 GNU/Linux ) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression, replication COLLATION | ||
[31 Aug 2012 22:35]
MySQL Verification Team
Could you please provide the complete test case (procedure and inserts). Thanks in advance.
[5 Sep 2012 20:29]
raouf Hanna
create database test;
create test table ===>
CREATE TABLE test (
M_ID binary(16) NOT NULL,
D_ID binary(20) NOT NULL,
UUID binary(16) NOT NULL,
TL_M5 binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
UNIX_START_TIME int(10) unsigned NOT NULL,
UNIX_END_TIME int(10) unsigned NOT NULL,
VERSION_WATERMARK set('disable','p-disable','a-disable-01','a-disable-02','a-disable-03') NOT NULL DEFAULT '',
BODY mediumblob NOT NULL,
D_PTS smallint(5) unsigned NOT NULL DEFAULT '0',
LAST_D_TYPE enum('MDM_SND','MDM_ING','NK','FRD') DEFAULT NULL,
LAST_D_TTPT int(10) unsigned DEFAULT NULL,
TERD int(10) unsigned DEFAULT NULL,
INGTD int(10) unsigned DEFAULT NULL,
test_BUILD_DATE_UNIX int(10) unsigned NOT NULL,
PRIMARY KEY (M_ID),
UNIQUE KEY CPLUUID (UUID,D_ID,TL_M5,VERSION_WATERMARK,UNIX_START_TIME,UNIX_END_TIME),
KEY D_ID (D_ID)) ;
Create procedure ===>
delimiter $$
CREATE PROCEDURE `sp_Addtest`(IN body MEDIUMBLOB,
IN start_time INT UNSIGNED,
IN end_time INT UNSIGNED,
IN watermark SET('disable','p-disable','a-disable-01','a-disable-02','a-disable-03'),
IN device_id BINARY(20),
IN tdl_md5 BINARY(16),
IN message_id BINARY(16),
IN cpl_uuid BINARY(16),
IN build_date INT UNSIGNED)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
INSERT INTO test
SET M_ID = message_id,
D_ID = device_id,
UUID = cpl_uuid,
TL_M5 = tdl_md5,
UNIX_START_TIME = start_time,
UNIX_END_TIME = end_time,
VERSION_WATERMARK = watermark,
BODY = body,
test_BUILD_DATE_UNIX = COALESCE(build_date, UNIX_TIMESTAMP());
END$$
call procedure
call sp_Addtest('9xÁdHÃ16',1343278800,1343278800,'','ªÊÛR1rñcìÌ)8çÌ|','9x1ÁdHÃ6','91xÁdHÃ6','9x1ÁdHÃ6',1343278800) ;
you will get an error on the slave server
Last_SQL_Errno: 1253
Last_SQL_Error: Error 'COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary'' on query. Default database: 'test'. Query: 'INSERT INTO test
SET M_ID = NAME_CONST('message_id',_binary'91xÁdHÃ6\0\0\0\0\0\0\0\0' COLLATE 'binary'),
D_ID = NAME_CONST('device_id',_binary'ªÊÛR1rñcìÌ)8çÌ|\0\0\0\0\0' COLLATE 'binary'),
UUID = NAME_CONST('cpl_uuid',_binary'9x1ÁdHÃ6\0\0\0\0\0\0\0\0' COLLATE 'binary'),
TL_M5 = NAME_CONST('tdl_md5',_binary'9x1ÁdHÃ6\0\0\0\0\0\0\0\0' COLLATE 'binary'),
UNIX_START_TIME = NAME_CONST('start_time',1343278800),
UNIX_END_TIME = NAME_CONST('end_time',1343278800),
VERSION_WATERMARK = NAME_CONST('watermark',_binary'' COLLATE 'latin1_swedish_ci'),
BODY = NAME_CONST('body',_binary'9xÁdHÃ16' COLLATE 'binary'),
test_BUILD_DATE_UNIX = COALESCE( NAME_CONST('build_date',1343278800), UNIX_TIMESTAMP())'
[6 Sep 2012 17:41]
Sveta Smirnova
Thank you for the feedback. Verified as described. Versions 5.0 and 5.1 were not affected: this is regression.
[13 Mar 2013 13:07]
Venkatesh Duggirala
Hello Raouf Hanna, Thanks for reporting the bug. We are working on this issue. Even though 5.1 was not effected (as mentioned by Sveta above), the root cause code exists in 5.1 code base also, hence there will be a push to mysql-5.1 version also. Regards, Venkatesh.
[3 May 2013 14:11]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
[3 May 2013 14:11]
Jon Stephens
Fixed in 5.5+. Documented in the 5.5.32, 5.6.12, and 5.7.2 as follows:
Setting a SET column to NULL inside a stored procedure caused
replication to fail.
Closed.
Description: 1- data inserted into master server using stored procedure 2- bin-log is set to MIXED 3- both master and slave are 5.5.24 and exact configuration at server/database 4- the insert statement at the slave changed to be used NAME_CONST to all column insert and has the wrong collation with the field type set ('sdfsd','dfgdf') error msg Last_SQL_Errno: 1253 Last_SQL_Error: Error 'COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary'' on query. table definition CREATE TABLE test ( M_ID binary(16) NOT NULL, D_ID binary(20) NOT NULL, UUID binary(16) NOT NULL, TL_M5 binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', UNIX_START_TIME int(10) unsigned NOT NULL, UNIX_END_TIME int(10) unsigned NOT NULL, VERSION_WATERMARK set('disable','p-disable','a-disable-01','a-disable-02','a-disable-03') NOT NULL DEFAULT '', BODY mediumblob NOT NULL, D_PTS smallint(5) unsigned NOT NULL DEFAULT '0', LAST_D_TYPE enum('MDM_SND','MDM_ING','NK','FRD') DEFAULT NULL, LAST_D_TTPT int(10) unsigned DEFAULT NULL, TERD int(10) unsigned DEFAULT NULL, INGTD int(10) unsigned DEFAULT NULL, test_BUILD_DATE_UNIX int(10) unsigned NOT NULL, PRIMARY KEY (M_ID), UNIQUE KEY CPLUUID (UUID,D_ID,TL_M5,VERSION_WATERMARK,UNIX_START_TIME,UNIX_END_TIME), KEY D_ID (D_ID)) ; How to repeat: create a table at the master database CREATE TABLE test ( M_ID binary(16) NOT NULL, D_ID binary(20) NOT NULL, UUID binary(16) NOT NULL, TL_M5 binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', UNIX_START_TIME int(10) unsigned NOT NULL, UNIX_END_TIME int(10) unsigned NOT NULL, VERSION_WATERMARK set('disable','p-disable','a-disable-01','a-disable-02','a-disable-03') NOT NULL DEFAULT '', BODY mediumblob NOT NULL, D_PTS smallint(5) unsigned NOT NULL DEFAULT '0', LAST_D_TYPE enum('MDM_SND','MDM_ING','NK','FRD') DEFAULT NULL, LAST_D_TTPT int(10) unsigned DEFAULT NULL, TERD int(10) unsigned DEFAULT NULL, INGTD int(10) unsigned DEFAULT NULL, test_BUILD_DATE_UNIX int(10) unsigned NOT NULL, PRIMARY KEY (M_ID), UNIQUE KEY CPLUUID (UUID,D_ID,TL_M5,VERSION_WATERMARK,UNIX_START_TIME,UNIX_END_TIME), KEY D_ID (D_ID)) ; create simple procedure with in all columns and insert to the table you will get an error on replication server