MySQL Bugs: #30010: Auto-increment out of sync when using triggers and NDB Cluster

Bug #30010	Auto-increment out of sync when using triggers and NDB Cluster
Submitted:	24 Jul 2007 16:18	Modified:	18 Jun 2008 12:26
Reporter:	Ray Fraser	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0, 5.1.20, 5.1.24-ndb-6.3.14	OS:	Linux (Fedora Core 5)
Assigned to:		CPU Architecture:	Any
Tags:	Auto-increment Cluster Trigger

Description:
When using a trigger to do inserts in a 2 node clustered environment we see problems with auto_increment columns.  We have defined a trigger on both nodes of the cluster that inserts to table Atest whenever a row is added to table Btest.  Once the values for auto_increment in information_schema.tables become out of sync they stay out of sync - sometimes by differences over 1000. The results in both tables after manually adding rows to Btest alternating between the nodes are listed below. I have failed to duplicate when not using a trigger.

Atest and Btest results are identical:
trigSeq	cluster	        Time
1	from node 1	2007-07-24 11:11:48
2	from node 2	2007-07-24 11:12:03
3	from node 1	2007-07-24 11:12:05
4	from node 2	2007-07-24 11:12:20
5	from node 1	2007-07-24 11:12:23
36	from node 2	2007-07-24 11:12:35
6	from node 1	2007-07-24 11:18:13
37	from node 2	2007-07-24 11:19:04

How to repeat:
I used the statements below to create two tables and a trigger (on both clusters). 

CREATE TABLE  `asterisk`.`Atest` (
  `trigSeq` int(11) NOT NULL AUTO_INCREMENT,
  `cluster` varchar(16) NOT NULL DEFAULT '',
  `UTCtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`trigSeq`)
) /*!50100 TABLESPACE asterisk_ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1;

CREATE TABLE  `asterisk`.`Btest` (
  `trigSeq` int(11) NOT NULL AUTO_INCREMENT,
  `cluster` varchar(16) NOT NULL DEFAULT '',
  `UTCtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`trigSeq`)
) /*!50100 TABLESPACE asterisk_ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1;

DELIMITER //
Create trigger asterisk.trig7 after insert on asterisk.Btest
FOR EACH ROW
begin
  insert Atest set cluster = new.cluster;
end;//

Suggested fix:
Our current work around:
Setting ndb_autoincrement_prefetch_sz=1 eliminates the above problem.

To further clarify my example I have included our config.ini below. Note that my initial write-up confusingly used cluster instead of node and did not properly identify the manually entered data as originating from nodes 4 and 5.

ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @10.4.229.37  (Version: 5.1.20, Nodegroup: 0)
id=3    @10.4.229.38  (Version: 5.1.20, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.4.229.40  (Version: 5.1.20)

[mysqld(API)]   2 node(s)
id=4    @10.4.229.37  (Version: 5.1.20)
id=5    @10.4.229.38  (Version: 5.1.20)

A few questions:

- do you perform all inserts from the same connection?
  different connections to the same mysql server?
  different connections to different mysql servers?

- can you check whether the same happens when using
  MySQL 5.0 instead of 5.1? AFAIK there were some
  changes in the way auto increment prefetch works
  in 5.1, so this might be a regression ...

Response to Hartmut's questions:

1. Different connections to different MySQL servers.

2. We currently do not have any MySQL 5.0 NDB Cluster systems up and running. Sorry, but going forward I do not expect a chance to regress.

Verified on 5.0, 5.1 and latest ndb-6.3.x using

== on mysqld #1 ==

  CREATE LOGFILE GROUP lg_1
    ADD UNDOFILE 'undo_1.dat'
    INITIAL_SIZE 16M
    UNDO_BUFFER_SIZE 2M
    ENGINE NDB;

  CREATE TABLESPACE ts_1
    ADD DATAFILE 'data_1.dat'
    USE LOGFILE GROUP lg_1
    INITIAL_SIZE 32M
    ENGINE NDB;

  CREATE TABLE  `test`.`t1` (
    `trigSeq` int(11) NOT NULL AUTO_INCREMENT,
    `cluster` varchar(16) NOT NULL DEFAULT '',
    `UTCtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`trigSeq`)
  ) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1;

  CREATE TABLE  `test`.`t2` (
    `trigSeq` int(11) NOT NULL AUTO_INCREMENT,
    `cluster` varchar(16) NOT NULL DEFAULT '',
    `UTCtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`trigSeq`)
  ) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1;

== on both mysqld #1 & #2 ==

  DELIMITER //
  Create trigger test.trig1 after insert on test.t1
  FOR EACH ROW
  begin
    insert t2 set cluster = new.cluster;
  end;//
  DELIMITER ;

== alternating on mysqld #1 and #2 a few times ==

  INSERT INTO t1 SET cluster="mysqld #(1|2)";

Tried with and without the trigger, only with the trigger the autoincrement sequence sometimes jumps by ndb_autoincrement_prefetch_sz

I'd understand (somehow) if it would always jump by that value, but it actually only happens sometimes ... which is weired ...