Auto_increment value was confusion in a clustered environment
.Enviroment Info
Server version: 5.6.14-ndb-7.3.3-cluster-gpl-log MySQL Cluster Community Server (GPL)
Server to each of the two(DB1 and DB2),
to set up the management node, data node, the SQL node, I built a cluster environment.
.result of [ndb_mgm -e show]
Connected to Management Server at: localhost:XXXX
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=11 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3, Nodegroup: 0, *)
id=12 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3, Nodegroup: 0)
[ndb_mgmd(MGM)] 2 node(s)
id=1 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=2 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
[mysqld(API)] 17 node(s)
id=21 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=22 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=23 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=24 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=25 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=26 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=27 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=28 @<localIP for DB1> (mysql-5.6.14 ndb-7.3.3)
id=31 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
id=32 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
id=33 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
id=34 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
id=35 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
id=36 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
id=37 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
id=38 @<localIP for DB2> (mysql-5.6.14 ndb-7.3.3)
id=62 (not connected, accepting connect from any host)
.contents of [my.cnf]
[mysqld]
ndbcluster
default-storage-engine=ndbcluster
ndb-connectstring="<localIP:Port for DB1>;<localIP:Port for DB2>"
ndb-cluster-connection-pool=8
ndb-force-send=1
ndb-use-exact-count=0
ndb-autoincrement-prefetch-sz=1024
log-error=/var/log/mysql/sql_error.log
datadir=/var/lib/mysql-cluster/sql
socket=/var/lib/mysql-cluster/sql/mysql.sock
character_set_server=utf8
key_buffer_size=256M
max_allowed_packet=16M
max_connections=500
thread_cache=200
slow_query_log=ON
long_query_time=0.3
[mysql_cluster]
ndb-connectstring="<localIP:Port for DB1>;<localIP:Port for DB2>"
.Problems
INSERT from the application to access the load balance of Connector/J to DB1 DB2 respectively, became Duplication error.
It was in an error value of auto_increment column [id] is after 2049(1024*2 + 1) all
Maybe, the problem had occurred in only DB2.
Whenever an error occurs, AUTO_INCREMENT of DB02 increased.
DB1
>show create table t1
*Excerpt of the response
CREATE TABLE `access_history` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
~~~
ENGINE=ndbcluster AUTO_INCREMENT=2710
~~~
DB2
>show create table t1
*Excerpt of the response
CREATE TABLE `access_history` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
~~~
ENGINE=ndbcluster AUTO_INCREMENT=2112
~~~
I went to [ALTER TABLE t1 AUTO_INCREMENT = 2710 ] in DB02.
Then, the value of the AUTO_INCREMENT of DB02 becomes 3785 (2710 +1024 +1), the same problem did not occur thereafter.
Even after the AUTO_INCREMENT DB01 reaches 3785, the problem did not occur if the value is +1024 further added is set to AUTO_INCREMENT.
I was expected when the AUTO_INCREMENT reaches 2049, DB02 had lost a number of files prefetch(or consumption).
*For example, by a restart of the SQL node
i examine the document, It was impossible to know the cause.
I want to know the following things.
1.How each node examine the AUTO_INCREMENT number of prefetch(or Consumed) already.
2.A similar problem or has been reported.
(For example, it occurs when you restart only one node)