MySQL Bugs: #6237: Calling stored procedure with parameters causes replication error on slaves

Bug #6237	Calling stored procedure with parameters causes replication error on slaves
Submitted:	24 Oct 2004 22:18	Modified:	7 Oct 2008 10:49
Reporter:	Levap Aretnyd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.2-alpha-debug	OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
Calling a stored procedure with parameters on replication master causes replication error on slaves.
Report is very similar to bug#2611 except slaves do not crash, only replication error occures.
The error is "'Unknown column '_val' in 'field list'' on query. Default database: 'test'. Query: 'insert into test values (_val)"
`_val` is parameter of procedure I used.

How to repeat:
On master (in replicated database):

mysql> create table test (val int);
Query OK, 0 rows affected (0.06 sec)

mysql> create procedure tester(IN _val int) begin insert into test values (_val)
; end//
Query OK, 0 rows affected (0.00 sec)

On slave (in the same replicated datbase) - (load data from master and slave start are not needed to repeat the error - if replication is already running) - procedure has to be manualy created (bug#2610)

mysql> delimiter //
mysql> create procedure tester(IN _val int) begin insert into test values (_val)
; end//
Query OK, 0 rows affected (0.02 sec)

mysql> load data from master;
    -> //
Query OK, 0 rows affected (6.42 sec)

mysql> slave start//
Query OK, 0 rows affected (0.02 sec)

On master:
mysql> call tester(89)//
Query OK, 1 row affected (0.00 sec)

On slave:
mysql> show slave status//
+----------------------------------+-------------+-------------+-------------| Slave_IO_State                   | Master_Host | Master_User | Master_Port | C
onnect_Retry | Master_Log_File   | Read_Master_Log_Pos | Relay_Log_File       |
Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | R
eplicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Tab
le | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_E
rror
                                    | Skip_Counter | Exec_Master_Log_Pos | Relay
_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allow
ed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Ciph
er | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+-------------+-------------| Waiting for master to send event | server      | repl        |        3307 |
          60 | server-bin.000004 |                 752 | god-relay-bin.000007 |
          230 | server-bin.000004     | Yes              | No                | c
dklubcz3,test  |                     |                    |
   |                         |                             |       1054 | Error
'Unknown column '_val' in 'field list'' on query. Default database: 'test'. Quer
y: 'insert into test values (_val)' |            0 |                 665 |
       317 | None            |                |             0 | No
   |                    |                    |                 |
   |                |                  2401 |
1 row in set (0.00 sec)

It seems that 'CREATE PROCEDURE ..' is not needed to be called on the slaves, because mysql is not replicating 'CALL ..' but only the updating queries in it. Since parameters are declared in the CREATE PROCEDURE, slaves do not know what the parameters are.

Thank you very much for writting to us.

Replicating stored procedures is right now in the design stage. After being designed, 
this feature will be implemented. 

This is expected to happen in the next few months, may be even sooner.

Should be fixed by now. Needs to be re-verified and closed.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Result of test:

procedure replication works fine in newest version of MySQL 5.0 and 5.1