| Bug #34289 | Incorrect NAME_CONST substitution in stored procedures breaks replication | ||
|---|---|---|---|
| Submitted: | 4 Feb 17:58 | Modified: | 29 Mar 18:10 |
| Reporter: | Matt Nathan | ||
| Status: | Closed | ||
| Category: | Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.45, 5.0, 5.1, 6.0 BK | OS: | Any (Linux) |
| Assigned to: | Sergey Vojtovich | Target Version: | 5.0+,5.1.24 |
| Tags: | binary log, stored procedure, replication, name_const | ||
| Triage: | D2 (Serious) | ||
[4 Feb 18:51]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a, and inform about the results.
[5 Feb 13:42]
Matt Nathan
Having tested on the latest 5.0.51a community server the first problem (collation casting)
still breaks, the second problem (variable escaping) has been fixed.
The variable escaping now produces binary log entries like:
insert into t2 values( NAME_CONST('order',1))
instead of:
insert into t2 values(` NAME_CONST('order',1)`)
The collation casting still produces binary log entries like:
update t1 set v = 'updated' where q = NAME_CONST('var',_latin1'the value') collate
latin1_general_cs
[6 Feb 1:42]
Miguel Solorzano
Thank you for the feedback. It is the below error are you reporting?
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: miguel
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: windows-bin.000009
Read_Master_Log_Pos: 106
Relay_Log_File: amanhecer-relay-bin.000023
Relay_Log_Pos: 253
Relay_Master_Log_File: windows-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 559
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: miguel
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: windows-bin.000009
Read_Master_Log_Pos: 1385
Relay_Log_File: amanhecer-relay-bin.000023
Relay_Log_Pos: 1240
Relay_Master_Log_File: windows-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1253
Last_Error: Error 'COLLATION 'latin1_general_cs' is not valid for
CHARACTER SET 'binary'' on query. Default database: 'testdb'. Que
ry: 'update t1 set v = 'updated' where q = NAME_CONST('var',_latin1'the value') collate
latin1_general_cs'
Skip_Counter: 0
Exec_Master_Log_Pos: 1093
Relay_Log_Space: 1838
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1253
Last_SQL_Error: Error 'COLLATION 'latin1_general_cs' is not valid for
CHARACTER SET 'binary'' on query. Default database: 'testdb'. Que
ry: 'update t1 set v = 'updated' where q = NAME_CONST('var',_latin1'the value') collate
latin1_general_cs'
1 row in set (0.00 sec)
mysql>
Thanks in advance.
[6 Feb 10:11]
Matt Nathan
Yes, this is the error we are getting.
[6 Feb 15:00]
Sveta Smirnova
Thank you for the report. Verified as described.
[19 Feb 15:18]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/42555 ChangeSet@1.2575, 2008-02-19 18:16:17+04:00, svoj@mysql.com +3 -0 BUG#34289 - Incorrect NAME_CONST substitution in stored procedures breaks replication NAME_CONST() didn't replicate constant character set and collation correctly. With this fix NAME_CONST() inherits collation from the value argument.
[19 Feb 15:35]
Alexander Barkov
The patch http://lists.mysql.com/commits/42555 is ok to push.
[27 Mar 12:18]
Bugs System
Pushed into 5.1.24-rc
[27 Mar 12:21]
Bugs System
Pushed into 5.0.60
[27 Mar 18:49]
Bugs System
Pushed into 6.0.5-alpha
[29 Mar 18:10]
Jon Stephens
Bugfix documented in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:
The character sets and collations used for constant identifiers in
stored procedures were not replicated correctly.
[2 Apr 21:40]
Jon Stephens
Also noted in the 5.1.23-ndb-6.3.11 changelog.
Description: The automatic substitution of variable names for NAME_CONST('varName', varVal) in the binary logging of stored procedures is incorrect under certain conditions. The two cases which have caused us problems are listed below: 1) replication of collation casting a procedure like this create procedure collationCast() begin declare var char(10) default 'the value'; update t1 set v = 'updated' where q = var collate latin1_general_cs; end// This gets logged in the binary log as: update t1 set v = 'updated' where q = NAME_CONST('var',_latin1'the value') collate latin1_general_cs Which is invalid and throws an exception stating: COLLATION 'latin1_general_cs' is not valid for CHARACTER SET 'binary' I would guess that this is because it it trying to collate a binary result set (NAME_CONST) instead of a character variable. 2) replication using reserved words While this is less important as it is easy to work around it does cause some problems when it cannot be avoided. Stored procedures like this: create procedure reservedWord() begin declare `order` int default 1; insert into t2 values(`order`); end// places the following into the binary log: insert into t2 values(` NAME_CONST('order',1)`) which gives the error: Unknown column ' NAME_CONST('order',1)' in 'field list' Work arounds For problem 1 you cab use a user variable instead of a SP variable, something like this: create procedure collateCastFix() begin declare var char(10) default 'the value'; set @varTmp = var; update t1 set v = 'update fix' where q = @varTmp collate latin1_general_cs; end// For problem 2 it should be possible to change the name of the SP variable to something that is not a reserved word allowing you to exclude the `` when defining it. How to repeat: drop database if exists testdb; create database testdb; use testdb; -- for error 1 create table t1 (v char(10), q char(10)) character set latin1 collate latin1_general_ci; insert into t1 values ('original', 'the value'); delimiter // create procedure collationCast() begin declare var char(10) default 'the value'; update t1 set v = 'updated' where q = var collate latin1_general_cs; end// delimiter ; -- for error 2 create table t2 (`order` int); delimiter // create procedure reservedWord() begin declare `order` int default 1; insert into t2 values(`order`); end// delimiter ; -- enable the binary log then run the following: call collationCast(); call reservedWord(); -- the binary log should now show two invalid queries.