| Bug #11008 | SP: Replication error when i use procedure | ||
|---|---|---|---|
| Submitted: | 1 Jun 2005 6:21 | Modified: | 16 Feb 2007 16:56 |
| Reporter: | hwang hwang | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0.6 | OS: | Linux (redhat9) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[1 Jun 2005 6:23]
hwang hwang
master server:
>call test("0000000")//
[3 Jun 2005 1:09]
hwang hwang
?
[9 Aug 2006 22:55]
Lars Thalmann
Can't repeat this failure.
I ran the following test case (rpl_bug11008.test) via
mysql-test-run.pl:
-------------
source include/master-slave.inc;
CREATE TABLE `td_a_staff` (
`staffid` varchar(7) NOT NULL default '',
`passwd` varchar(20) NOT NULL default '',
`staffname` varchar(20) NOT NULL default '',
`departid` varchar(4) NOT NULL default '',
`dimissiontag` char(1) NOT NULL default '',
`updatetime` datetime default NULL,
`departid_m` varchar(4) default NULL,
`staffid_m` varchar(7) default NULL,
PRIMARY KEY (`staffid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `td_a_staff` (`staffid`, `passwd`, `staffname`, `departid`,
`dimissiontag`, `updatetime`, `departid_m`, `staffid_m`) VALUES ('0000000',
'*58D24DFDC5B5D55D6F6', '', '0000', '0', '2004-11-18 14:45:44',
'0000', '0000000');
delimiter //;
Create Procedure test(in v_staffid char)
Begin
Declare iv_departid char(4);
Select departid Into iv_departid
From td_a_staff Where staffid=v_staffid;
End//
call test("0000000")//
delimiter ;//
sync_slave_with_master;
select * from td_a_staff;
---------
The test pass on both 5.0 and 5.1.
[16 Jan 2007 16:56]
Guilhem Bichot
The way the replication master stores stored procedure calls into its binary log has changed since 5.0.6 (we don't write CALL anymore, but statements used by the procedure). And we cannot repeat the bug, it is probably that the changes have fixed the bug. We suggest the user to try with a version newer than 5.0.6 and provide feedback on the results.
[17 Feb 2007 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Error 'Unknown column 'departid' in 'field list'' on query. Default database: 'ttt'. Query: 'call test("0000000") How to repeat: -------------------------------------------------------- master host(192.168.2.3): [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking log_bin_trust_routine_creators max_connections = 500 server-id = 1 log-bin = testserver-bin ------------------------------------------------------- slave host(192.168.2.171): [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock max_connections=1000 log_bin_trust_routine_creators server-id = 2 master-host = 192.168.2.3 master-user = bill master-password = newrockbill master-port = 3306 master-connect-retry = 60 replicate-do-db = ttt report-host = 192.168.2.171 #slave-skip-errors = all log-bin ---------------------------------------------------------------------- mast server: >CREATE TABLE `td_a_staff` ( `staffid` varchar(7) NOT NULL default '', `passwd` varchar(20) NOT NULL default '', `staffname` varchar(20) NOT NULL default '', `departid` varchar(4) NOT NULL default '', `dimissiontag` char(1) NOT NULL default '', `updatetime` datetime default NULL, `departid_m` varchar(4) default NULL, `staffid_m` varchar(7) default NULL, PRIMARY KEY (`staffid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; >INSERT INTO `td_a_staff` (`staffid`, `passwd`, `staffname`, `departid`, `dimissiontag`, `updatetime`, `departid_m`, `staffid_m`) VALUES ('0000000', '*58D24DFDC5B5D55D6F6', '系统管理员', '0000', '0', '2004-11-18 14:45:44', '0000', '0000000'); >use ttt; >delimiter // >Create Procedure test(in v_staffid char) Begin Declare iv_departid char(4); Select departid Into iv_departid From td_a_staff Where staffid=v_staffid; End// ---------------------------------------------------- slave host: >show slave status\G; Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.3 Master_User: bill Master_Port: 3306 Connect_Retry: 60 Master_Log_File: testserver-bin.000001 Read_Master_Log_Pos: 503 Relay_Log_File: billserver-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: testserver-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: ttt Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1054 Last_Error: Error 'Unknown column 'departid' in 'field list'' on query. Default database: 'ttt'. Query: 'call test("0000000")' Skip_Counter: 0 Exec_Master_Log_Pos: 413 Relay_Log_Space: 645 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