Bug #3226 User variables in insert statements don't replicate properly
Submitted: 18 Mar 2004 9:46 Modified: 25 Mar 2004 12:08
Reporter: Gary Thornock Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.1 and previous OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[18 Mar 2004 9:46] Gary Thornock 
Description:
User variables in insert statements work correctly on the master but often get NULL values on replicated slaves.

How to repeat:
In an environment that has replication, create two tables with auto-incrementing, primary key ID fields, where table2 also has a reference to table1.ID:

CREATE TABLE table1 (
  ID INT(10) NOT NULL AUTO_INCREMENT,
  field1 VARCHAR(255),
  PRIMARY KEY (ID)
) TYPE=MyISAM CHARSET=latin1;

CREATE TABLE table2 (
  ID INT(10) NOT NULL AUTO_INCREMENT,
  table1ID INT(10),
  field2 VARCHAR(255),
  PRIMARY KEY (ID)
) TYPE=MyISAM CHARSET=latin1;

Then, run a query that inserts into table1, takes the last insert ID into a user variable, and inserts it into table2:

INSERT INTO table1 (field1)
VALUES ('foo');

SET @tbl1ID = last_insert_id();

INSERT INTO table2 (table1ID, field2)
VALUES
  (@tbl1ID,'bar');

This will work exactly as expected on the master, but on the slave, the value inserted by @tbl1ID will (often) be NULL.
[25 Mar 2004 10:37] Sinisa Milivojevic 
Worked for me with both 4.0.19 and 4.1.2:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#040325 20:39:02 server id 1  log_pos 4         Start: binlog v 3, server v 4.1.2-alpha-log created 040325 20:39:02 at startup
# at 79
#040325 20:39:50 server id 1  log_pos 79        User_var
SET @sinisa:=112;
# at 126
#040325 20:39:50 server id 1  log_pos 126       Query   thread_id=1     exec_time=0     error_code=0
use telcent;
SET TIMESTAMP=1080239990;
insert into nazivi values (@sinisa,"Yes");
[25 Mar 2004 12:08] Guilhem Bichot 
Correction to the previous answer. It does not work in 4.0, which is a documented bug. User variables replicate fine starting from 4.1.0 (so master and slave must be _both_ 4.1.0 or 4.1.1).
So it's normal that it does not work when using MySQL "previous to" 4.1. If your master and slave are both 4.1.0 or 4.1.1, please re-open the bug report.
Thank you.