Bug #52563 Replication failure when using xpath variable inside a stored procedure
Submitted: 2 Apr 2010 21:43 Modified: 3 Apr 2010 7:15
Reporter: Corrado Pandiani Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.44, 5.1 bzr, 5.6.99 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Tags: replication, stored procedure, xpath variable
Triage: Triaged: D2 (Serious)

[2 Apr 2010 21:43] Corrado Pandiani
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
[3 Apr 2010 7:15] Sveta Smirnova
Thank you for the report.

Verified as described. set binlog_format='mixed'; doesn't help.
[21 Jan 2011 11:14] sameer joshi
i m also facing similar kind of issue. any updates on this?
[1 Jun 2011 11:23] Axel Schwenke
There is a simple workaround available:

The problem is the replication of a strings containing a variable. I.e. in this expression

insert into test values(ExtractValue(xml,'//node[$i]'));

the string '//node[$i]' refers to the variable i

if this is written like so

insert into test values(ExtractValue(xml,concat('//node[', i, ']')));

then it replicates without a problem.