Description:
DML in Stored Procedure(SP) using SP variables result in Replication failure:
extract from .err file:
--------------------------------------------------------------------
050429 16:53:08 mysqld started
050429 16:53:08 InnoDB: Started; log sequence number 0 4660508
050429 16:53:08 InnoDB: Starting recovery for XA transactions...
050429 16:53:08 InnoDB: 0 transactions in prepared state after recovery
050429 16:53:08 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.4-beta-max-log' socket: '/u01/mysqlTMP/mysql.sock' port: 3306 MySQL Community Edition - Experimental (GPL)
050429 16:53:08 [Note] Slave SQL thread initialized, starting replication in log 'kalinka-binlog.000075' at position 293, relay log './lasagna-relay-bin.000002' position: 435
050429 16:53:08 [ERROR] Slave: Error 'Unknown column 't_prm' in 'field list'' on query. Default database: 'huish'. Query: 'insert into test_t(test_f) values(t_prm)', Error_code: 1054
050429 16:53:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'kalinka-binlog.000075' position 293
--------------------------------------------------------------------------------------
Note: the problem with SP-variables used in DML inside stored procedure also shows up when doing recovery from the bin-log files using mysqlbinlog!
How to repeat:
In an operational replication setup do the following:
Make a test table: at master and slave sides)
--------------------------------------------------------------------
> create table test_t ( test_f numeric );
--------------------------------------------------------------------
Make a procedure: (at master side)
--------------------------------------------------------------------
> delimiter $
> create procedure test_p1 ( in t_prm numeric )
> begin
> insert into test_t(test_f) values(t_prm);
> end
> $
--------------------------------------------------------------------
Execute the proc:
-------------------------------
>call test_p1(1);
-------------------------------
have a look at the .err file at the slave side!
Suggested fix:
somehow the actual contents of the variables should be set in the bin-log files!
