Bug #4982 auto_increment with MySQL Cluster produce weird result in some case

• Submitted: 10 Aug 2004 22:09
• Modified: 11 Aug 2004 10:00
• Reporter: Pascal Gauthier
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S1 (Critical)
• Version: 4.1.4 beta (bk tree 09au)
• OS: Linux (Debian Linux)
• Assigned to: Martin Skold
• CPU Architecture: Any

[10 Aug 2004 22:09] Pascal Gauthier

Description:
I have seen something weird when I test MySQL Cluster with one of my
perl script. 

Here are the table:
CREATE TABLE log (logid bigint(20) NOT NULL auto_increment, entityid
bigint(20) NOT NULL default 0, timestamp bigint(20) NOT NULL default 0,
entitylogid bigint(20) NOT NULL default 0, entitylogtimestamp bigint(20)
NOT NULL default 0, logtype bigint(20) NOT NULL default 0, PRIMARY KEY
(logid)) ENGINE=NDB;

With this script, auto_increment doesnt work properly. 192.168.101.4 and
192.168.101.7 are my api node. The auto_increment jump by a factor of
32...

#!/usr/bin/perl
 
use DBI;
use POSIX qw(strftime);
 
while (true) {
 
        if ( $server eq "192.168.101.4") {
                $server = "192.168.101.7";
        }
        else {
                $server = "192.168.101.4";
        };
 
        my $dbh = DBI->connect("DBI:mysql:test:$server", mytest)
                or die "Couldn't connect to database: " . DBI->errstr;
 
        my $sql = qq{ INSERT INTO log VALUE ( NULL, 1, 2, 3, 4, 5) };
        $dbh->do($sql);
 
        $time = strftime "%H:%M:%S", localtime;
        print "$time :  INSERT INTO log VALUE ( NULL, 1, 2, 3, 4, 5 )
FROM server $server\n";
        sleep(1);
 
        $dbh->disconnect;
};

How to repeat:
Always the same results

Suggested fix:
I don't known

[11 Aug 2004 9:59] Martin Skold

In MySQL Cluster auto_increment values are stored in the distributed database since
they are global (per table). Since it is much more expensive to manage such counters
than in a local non-distributed database, the auto_increment values are pre-fetched in
batches (currently of size 32). This is thus not a bug.
Actually I was only able to reproduce the problem running two clients, where if I do
insert in the first I get 0 (since it has a batch 0 - 31) and if insert in the second I get
32 (since it has a batch of 32-63).