Bug #10264 Replication failure with DML in stored procedure
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!
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.