Bug #10654 Replication error when i call a procedure.
Submitted: 16 May 2005 6:03 Modified: 16 May 2005 8:31
Reporter: hwang hwang Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:MySQL-server-5.0.4-0 OS:Linux (redhat linux9.0)
Assigned to: CPU Architecture:Any

[16 May 2005 6:03] hwang hwang 
Description:
Replication Error:
master:
server-id=1
log-bin
---------------------------------
slave:
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        = newbill
report-host     = 192.168.2.171
slave-skip-errors       = all
log-bin         = billserver-bin
----------------------------------
master server:
CREATE TABLE `td_a_staff` (
  `staffid` varchar(7) NOT NULL default '',
  `passwd` varchar(20) NOT NULL default '',
  `staffname` varchar(40) 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', '*64E794948AA6B2E33A6', 'administrator, '0000', '0', '2004-08-25 13:33:28', '0000', '0000000');

CREATE TABLE `tf_b_loginlog` (
  `staffid` varchar(7) NOT NULL default '',
  `staffname` varchar(20) NOT NULL default '',
  `login_ip` varchar(15) NOT NULL default '',
  `login_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `remark` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Create Procedure p_bt_loginlog(In v_staffid char,
				  v_ipaddr  char)
Begin
	Insert Into tf_b_loginlog
	Select v_staffid,staffname,v_ipaddr,now(),null
	From td_a_staff Where staffid=Trim(v_staffid);
End

mysql>call p_bt_loginlog("0000000","192.168.2.171");
--------------------------------------
slave server:
050516 11:15:16  mysqld started
050516 11:15:16 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=billserver-bin' to avoid this problem.
050516 11:15:16  InnoDB: Started; log sequence number 1 350209320
050516 11:15:16  InnoDB: Starting recovery for XA transactions...
050516 11:15:16  InnoDB: 0 transactions in prepared state after recovery
050516 11:15:16 [Note] Slave SQL thread initialized, starting replication in log 'testserver-bin.000001' at position 46480, relay log './billserver-relay-bin.000002' position: 46622
050516 11:15:16 [ERROR] Slave: Error 'Unknown column 'v_staffid' in 'field list'' on query. Default database: 'newbill'. Query: 'Insert Into tf_b_loginlog
        Select v_staffid,staffname,v_ipaddr,now(),null
        From td_a_staff Where staffid=Trim(v_staffid)', Error_code: 1054
050516 11:15:16 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'testserver-bin.000001' position 46480

How to repeat:
Replication Error:
master:
server-id=1
log-bin
---------------------------------
slave:
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        = newbill
report-host     = 192.168.2.171
slave-skip-errors       = all
log-bin         = billserver-bin
----------------------------------
master server:
CREATE TABLE `td_a_staff` (
  `staffid` varchar(7) NOT NULL default '',
  `passwd` varchar(20) NOT NULL default '',
  `staffname` varchar(40) 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', '*64E794948AA6B2E33A6', 'administrator, '0000', '0', '2004-08-25 13:33:28', '0000', '0000000');

CREATE TABLE `tf_b_loginlog` (
  `staffid` varchar(7) NOT NULL default '',
  `staffname` varchar(20) NOT NULL default '',
  `login_ip` varchar(15) NOT NULL default '',
  `login_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `remark` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Create Procedure p_bt_loginlog(In v_staffid char,
				  v_ipaddr  char)
Begin
	Insert Into tf_b_loginlog
	Select v_staffid,staffname,v_ipaddr,now(),null
	From td_a_staff Where staffid=Trim(v_staffid);
End

mysql>call p_bt_loginlog("0000000","192.168.2.171");
--------------------------------------
slave server:
050516 11:15:16  mysqld started
050516 11:15:16 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=billserver-bin' to avoid this problem.
050516 11:15:16  InnoDB: Started; log sequence number 1 350209320
050516 11:15:16  InnoDB: Starting recovery for XA transactions...
050516 11:15:16  InnoDB: 0 transactions in prepared state after recovery
050516 11:15:16 [Note] Slave SQL thread initialized, starting replication in log 'testserver-bin.000001' at position 46480, relay log './billserver-relay-bin.000002' position: 46622
050516 11:15:16 [ERROR] Slave: Error 'Unknown column 'v_staffid' in 'field list'' on query. Default database: 'newbill'. Query: 'Insert Into tf_b_loginlog
        Select v_staffid,staffname,v_ipaddr,now(),null
        From td_a_staff Where staffid=Trim(v_staffid)', Error_code: 1054
050516 11:15:16 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'testserver-bin.000001' position 46480
[16 May 2005 6:13] hwang hwang 
"slave-skip-errors       = all"  Added later.
[16 May 2005 8:31] Guilhem Bichot 
Hello,
yes, stored procedures have replication problems in this version. In the next 5.0 (in a few weeks) it is fixed. BUG#9100 is similar to this.
Thank you for your bug report.