Bug #11932 Replication auto_increment gets out of sync
Submitted: 14 Jul 2005 10:39 Modified: 22 May 2006 20:25
Reporter: Are Casilla Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0 OS:Linux (linux)
Assigned to: Bugs System CPU Architecture:Any

[14 Jul 2005 10:39] Are Casilla
Description:
When you truncate table and run a procedure that insert two lines in one table the auto_increment (id field) on the SLAVE become 2 and 3 when the master become 1 and 2.

Basically the auto_increment on MASTER and SLAVE become out of sync.

How to repeat:
CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `fname` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `t` VALUES (1, 'blablabla');

CREATE TABLE `test3` (
  `id` int(10) NOT NULL auto_increment,
  `comment` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  AUTO_INCREMENT=3 ;

INSERT INTO `test3` VALUES (1, 'testtest 1');
INSERT INTO `test3` VALUES (2, 'test 2');
  
DROP PROCEDURE IF EXISTS simpleproc3;
delimiter //
CREATE PROCEDURE simpleproc3 ()
    NOT DETERMINISTIC
    BEGIN
    INSERT INTO t (fname) (SELECT test3.comment FROM test3 WHERE test3.id = '1');
    INSERT INTO t (fname) VALUES('test');
    END
    //
delimiter ;

CALL simpleproc3();

TRUNCATE TABLE `t` ;
CALL simpleproc3();
[2 Aug 2005 10:48] Aleksey Kishkin
Hi! tested against 5.0.10 and ws not able to reproduce. on both servers (master and slave) got the same result:

 select * from test3;
+----+------------+
| id | comment    |
+----+------------+
|  1 | testtest 1 |
|  2 | test 2     |
+----+------------+
2 rows in set (0.00 sec)

Could you check if latest mysql server works properly on your computer?
[2 Aug 2005 11:21] Aleksey Kishkin
sorry I was wrong: on slave result is 
mysql> select * from t;
+----+------------+
| id | fname      |
+----+------------+
|  2 | testtest 1 |
|  3 | test       |
+----+------------+
2 rows in set (0.00 sec)
[8 Mar 2006 8:57] Magnus BlÄudd
Duplicate of bug#14945? Or at least caused by same problem.
[12 Apr 2006 3:43] Greg Lehey
Magnus' comment appears to be correct.  This problem no longer occurs in the same manner.  It can probably be closed, but I want to take a more thorough look before closing it.
[3 May 2006 6:58] 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/5848
[3 May 2006 6:59] Greg Lehey
Wrote test case rpl_auto_increment_11932 for this bug.  It no longer fails.
[22 May 2006 20:25] Paul Dubois
Test case change.
No changelog entry needed.