Description:
I work in a replicated environment and I created a stored procedure to parse XML documents. In this SP I used XPATH variables in the ExtractValue function. Everything works good in the master but replication breaks on all the slaves when dealing with the XPATH variables.
XPATH variables replicate correctly outside stored procedures but they break replication when inside.
To prove just create a simple stored procedure on the master using XPATH variable to populate a table from a XML document. CALL the SP and take a look to a slave to see the error.
Here is the master:
mysql> delimiter //
mysql> create procedure test_rep_xpath_var()
-> begin
-> declare xml text;
-> declare i int;
-> drop table if exists test;
-> create table test (a text);
->
-> set xml='<root><node>Mickey Mouse</node><node>Donald Duck</node></root>';
->
-> set i = 1;
->
-> insert into test values(ExtractValue(xml,'//node[$i]'));
->
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> call test_rep_xpath_var()//
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from test;
+--------------+
| a |
+--------------+
| Mickey Mouse |
+--------------+
1 row in set (0.00 sec)
and everything is correct.
But now take a look to a slave:
mysql> select * from test;
Empty set (0.00 sec)
opss... no values
why ?
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mymaster
Master_User: myuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000263
Read_Master_Log_Pos: 70007395
Relay_Log_File: mysqld-relay-bin.000608
Relay_Log_Pos: 69680817
Relay_Master_Log_File: master-binlog.000263
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: 1105
Last_Error: Error 'Unknown XPATH variable at: '$i]'' on query. Default database: 'test'. Query: 'insert into test values(ExtractValue( NAME_CONST('xml',_latin1'<root><node>Mickey Mouse</node><node>Donald Duck</node></root>' COLLATE 'latin1_swedish_ci'),'//node[$i]'))'
Skip_Counter: 0
Exec_Master_Log_Pos: 69680671
Relay_Log_Space: 70007741
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: 1105
Last_SQL_Error: Error 'Unknown XPATH variable at: '$i]'' on query. Default database: 'test'. Query: 'insert into test values(ExtractValue( NAME_CONST('xml',_latin1'<root><node>Mickey Mouse</node><node>Donald Duck</node></root>' COLLATE 'latin1_swedish_ci'),'//node[$i]'))'
1 row in set (0.00 sec)
There's a workaround to the problem and it's to use ROW replication or to change your session binlog_format to ROW before the CALL. But it's just a workaround, if you freely choose MIXED or STATEMENT format this is a bug.
How to repeat:
ON THE MASTER
delimiter //
create procedure test_rep_xpath_var()
begin
declare xml text;
declare i int;
drop table if exists test;
create table test (a text);
set xml='<root><node>Mickey Mouse</node><node>Donald Duck</node></root>';
set i = 1;
insert into test values(ExtractValue(xml,'//node[$i]'));
end//
delimiter ;
call test_rep_xpath_var();
select * from test;
ON THE SLAVE
show slave status\G
Suggested fix:
Write XPATH variables values on the binlog or swicth automatically to ROW replication before every statement that use them.
Thanks