Bug #10264 Replication failure with DML in stored procedure

Submitted: 29 Apr 2005 15:06 Modified: 29 Apr 2005 20:46
Reporter: fons bouma Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.4-beta-max OS:Linux (linux Debian 2.6.10)
Assigned to: CPU Architecture:Any

[29 Apr 2005 15:06] fons bouma
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!
[29 Apr 2005 20:46] Guilhem Bichot
Thanks for your bug report.
Almost a duplicate of BUG#9100, for which a patch is soon to be incorporated into MySQL 5.0.
[29 Apr 2005 23:01] fons bouma
Good News.
Somehow bug#9100 doesn't show up for me in bug query.
The recovery issue re. mysqlbinlog is the major problem because my backup-recovery approach is invalid now, and I need procedures.

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.