Bug #29928 INSERT ... VALUES(connection_id(), ...) incorrect restores from mysqlbinlog out
Submitted: 20 Jul 2007 11:01 Modified: 3 Aug 2007 2:27
Reporter: Sveta Smirnova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:4.1 bk, 5.0 bk, 5.1 bk OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any

[20 Jul 2007 11:01] Sveta Smirnova 
Description:
If I fill table with INSERT ... VALUES(connection_id(), ...) and then restore its data from mysqlbinlog later, select statement will be issued as is and new connection_id will be written. This make binary logging is not good way to restore data after crash.

Originally reported in bug #29861

How to repeat:
source include/master-slave.inc;

connection slave;

connection master;

CREATE TABLE BugReport( 
  PRIMARY KEY (id), 
  id INT(11) AUTO_INCREMENT NOT NULL, 
  f1 int 
   ); 

insert into BugReport(f1) values(connection_id()); 

select * from BugReport;

flush logs;

--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/bug29862.sql

sleep 5;

connection slave;
select 'xxx', connection_id();
select * from BugReport;

connection master;

drop table BugReport;

--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug29862.sql

select * from BugReport;

Suggested fix:
Make fix for backup/restore as for replication Bug #177 or document this limitation.
[27 Jul 2007 18:31] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31728

ChangeSet@1.2577, 2007-07-27 23:31:02+05:00, ramil@mysql.com +3 -0
  Fix for bug #29928: INSERT ... VALUES(connection_id(), ...) incorrect restores 
  from mysqlbinlog out
  
  Mark connection_id() containing statements as unsafe for stmt-logging.
[30 Jul 2007 11:42] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31812

ChangeSet@1.2481, 2007-07-30 16:41:46+05:00, ramil@mysql.com +7 -0
  Fix for bug #29928: INSERT ... VALUES(connection_id(), ...) incorrect 
  restores from mysqlbinlog out
  
  Problem: using "mysqlbinlog | mysql" for recoveries the connection_id() 
  result may differ from what was used when issuing the statement.
  
  Fix: if there is a connection_id() in a statement, write to binlog
  SET pseudo_thread_id= XXX; before it and use the value later on.
[1 Aug 2007 9:30] Guilhem Bichot 
minor comments on the patch sent by mail
[1 Aug 2007 10:28] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31948

ChangeSet@1.2481, 2007-08-01 15:27:03+05:00, ramil@mysql.com +7 -0
  Fix for bug #29928: INSERT ... VALUES(connection_id(), ...) incorrect 
  restores from mysqlbinlog out
  
  Problem: using "mysqlbinlog | mysql" for recoveries the connection_id() 
  result may differ from what was used when issuing the statement.
  
  Fix: if there is a connection_id() in a statement, write to binlog
  SET pseudo_thread_id= XXX; before it and use the value later on.
[2 Aug 2007 19:12] Bugs System 
Pushed into 5.1.21-beta
[2 Aug 2007 19:15] Bugs System 
Pushed into 5.0.48
[3 Aug 2007 2:27] Paul DuBois 
Noted in 5.0.48, 5.1.21 changelogs.

INSERT ... VALUES(CONNECTION_ID(), ...) statements were written to
the binary log in such a way that they could not be properly
restored.