Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster
Submitted: 14 Aug 2009 9:16 Modified: 14 Sep 2009 11:09
Reporter: chris lin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:mysql-5.1-telco-6.2 OS:Any
Assigned to: Martin Skold CPU Architecture:Any
Tags: auto_increment, NDB Cluster, ndb-6.2.15 ndb7.0.6, trigger

[14 Aug 2009 9:16] chris lin 
Description:
In a cluster with 2 data nodes and 2 sql nodes, using a trigger to insert a row to a auto-increment table. Execute the trigger from each sql node many times, we found that the auto_increment column is out of order.

How to repeat:
1.
setup mysql cluster with 2 data nodes, 2 sql nodes, and 1 mgm node. 
NoOfReplica=2,there is nothing especial in config.ini.
On sql node, ndb_autoincrement_prefetch_sz=1 

2. 
create tables, and initialize data.
Table scripts:
CREATE TABLE `sbtest` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `k` int(10) unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL DEFAULT '',
 `pad` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `k` (`k`)
) ENGINE=ndbcluster;

CREATE TABLE `t1` (
  `evend_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `record_id` bigint(20) unsigned DEFAULT NULL,
  `table_id` tinyint(3) unsigned NOT NULL,
  `event_type` char(1) DEFAULT NULL,
  `timestamp` int(11) NOT NULL,
  `timestamp` int(11) NOT NULL,
   PRIMARY KEY (`evend_id`)
) ENGINE=ndbcluster

Insert records to sbtest:
mysql> insert into sbtest values (null,1,'',''),(null,2,'','');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

3. 
create trigger on each sql node:
mysql> delimiter /
mysql> CREATE trigger tgtest after update on sbtest for each row begin insert into t1(record_id, table_id, event_type, timestamp) values(1222312312, 20, 'I', UNIX_TIMESTAMP()); end;/
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

4. 
On sql node 1:
mysql> update sbtest set c='alsdj' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

On sql node 2:
mysql>  update sbtest set c='alsdj' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Execute each sql statement many times, in my environment, when execute more than 5 times on both sql node, the issue occurred:
mysql>  select * from cache_events;
+----------+------------+----------+------------+------------+
| evend_id | record_id  | table_id | event_type | timestamp  |
+----------+------------+----------+------------+------------+
|        1 | 1222312312 |       20 | I          | 1250231995 | 
|        4 | 1222312312 |       20 | I          | 1250232009 | 
|       36 | 1222312312 |       20 | I          | 1250231579 | 
|        2 | 1222312312 |       20 | I          | 1250231570 | 
|        3 | 1222312312 |       20 | I          | 1250231572 | 
|        5 | 1222312312 |       20 | I          | 1250232011 | 
+----------+------------+----------+------------+------------+

We can see a record with evend_id=36, but it should be 6.

Suggested fix:
keep the auto_increment column increase as it work under MyISAM
[17 Aug 2009 13:22] Jørgen Austvik 
Could be related to bug#38051
[24 Aug 2009 2:01] chris lin 
Could this bug be fixed?
[31 Aug 2009 5:41] chris lin 
Bug 38051 is duplicate of bug#38034, and bug38034 is fixed now, so I push the patch of bug38034 to mysql-5.1-telco-6.2.15 and test again, unfortunately, I found bug46712 is the same.
[31 Aug 2009 9:47] 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/81989

2968 Martin Skold	2009-08-31
      Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, changed prefetch cache to only use thd->variables.ndb_autoincrement_prefetch_sz, added test case
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        sql/ha_ndbcluster.cc
[31 Aug 2009 13:38] chris lin 
Thanks, Martin.
I have tested this patch on mysql-5.1-telco-6.2.15, it works correctly when ndb_autoincrement_prefetch_sz=1. 
From the patch, what I understand is that ndb_autoincrement_prefetch_sz must be set to 1 to keep the auto_increment column increase one by one on each sql node. Is it right? When i set ndb_autoincrement_prefetch_sz more than 1, the patch is noneffective.

To evaluate the performance, I insert 25,000 rows  into a table, the table has no trigger on it. When ndb_autoincrement_prefetch_sz=1, it needs about 33.3 seconds with this patch, and about 18.1 seconds without this patch; when ndb_autoincrement_prefetch_sz=16, it needs about 20.3 seconds whih patch, and about 17.5 without patch.
We can find that without this patch, no matter how to set ndb_autoincrement_prefetch_sz, the INSERT performance is little difference. But there is much different with this patch.
For batch insert, there is little difference.

Is this correctly?
[31 Aug 2009 14:53] 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/82022

2969 Martin Skold	2009-08-31
      Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, increasing prefetch when batch size is known to not degrade table load performance
      modified:
        sql/ha_ndbcluster.cc
[31 Aug 2009 14:56] Martin Skold 
Yes, your analyses seems correct. The patch corrects the faulty code
that sets the auto_increment prefetch to the internal Ndb default (32)
when triggers are executed. The auto_increment prefetch is an optimization
that the user can control. Setting ndb_autoincrement_prefetch_sz=32 should give
you about the same performance as before. The reason why the mysqld default
setting for ndb_autoincrement_prefetch_sz is one is that is what users expect,
but experienced cluster users should know to increase this for better performance.
Note, however, the second commit which ensures the performance when batching inserts with known number of rows is not decreased.
[1 Sep 2009 2:17] chris lin 
Setting ndb_autoincrement_prefetch_sz=1, and then execute INSERT ... SELECT ... to insert 10,000 records, it needs about 5.88 seconds with second patch, and about 3.17 seconds with first patch.
The performance of batching inserts is decreased.
[1 Sep 2009 6:38] Martin Skold 
Yes, this is expected, the old code guessed a prefetch size based on the
internal default for INSERT ... SELECT ..., because the exact number of
rows is not known. Guessing a value will sometimes cause holes, if one
wants total predictability based on the ndb_autoincrement_prefetch_sz setting
the performance will suffer, unless one increases it's value.
[1 Sep 2009 8:04] 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/82087

2970 Martin Skold	2009-09-01
      Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, added same optimization as prior for increasing prefetch for batched inserts
      modified:
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
[1 Sep 2009 8:05] Martin Skold 
Added similar optimization as previously for batched inserts.
[1 Sep 2009 9:40] chris lin 
Great! The optimization is efficient. According to my testing, it seems that the batching insert performance is better than previously. Everything works fine now.
Thanks, Martin.
[1 Sep 2009 10:11] Martin Skold 
Total patch, for review

Attachment: patch.txt (text/plain), 11.24 KiB.
[1 Sep 2009 10:24] Martin Skold 
Total patch, for review (fixed comment)

Attachment: patch.txt (text/plain), 11.29 KiB.
[3 Sep 2009 7:35] 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/82279

2975 Martin Skold	2009-09-03
      Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, defining m_rows_to_insert=1 as default, even when start_bulk_insert is nor called, simplified code in get_auto_increment.
      modified:
        sql/ha_ndbcluster.cc
[3 Sep 2009 7:49] 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/82282

2976 Martin Skold	2009-09-03
      Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, added test case.
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
[3 Sep 2009 9:14] 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/82295

3028 Martin Skold	2009-09-03 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc
[3 Sep 2009 9:55] 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/82310

2982 Martin Skold	2009-09-03 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc
[3 Sep 2009 11:48] Bugs System 
Pushed into 5.1.37-ndb-7.0.8 (revid:martin.skold@mysql.com-20090903095452-7ukx3yiaeltk2mgy) (version source revid:martin.skold@mysql.com-20090903095452-7ukx3yiaeltk2mgy) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[3 Sep 2009 12:02] Bugs System 
Pushed into 5.1.37-ndb-6.3.27 (revid:martin.skold@mysql.com-20090903115020-aqt0bnf1s1m458c3) (version source revid:martin.skold@mysql.com-20090903091350-l7if6pbajxsawq9z) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[3 Sep 2009 12:50] Bugs System 
Pushed into 5.1.37-ndb-6.2.19 (revid:martin.skold@mysql.com-20090903125016-bd6hb0ukbdasnj23) (version source revid:martin.skold@mysql.com-20090903074950-p8ejthvvbbatmv0g) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[3 Sep 2009 15:25] 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/82339

2982 Martin Skold	2009-09-03 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc
[3 Sep 2009 15:43] Bugs System 
Pushed into 5.1.35-ndb-7.1.0 (revid:martin.skold@mysql.com-20090903152059-p5762oa1wy9b6o6f) (version source revid:martin.skold@mysql.com-20090903152059-p5762oa1wy9b6o6f) (merge vers: 5.1.35-ndb-7.1.0) (pib:11)
[7 Sep 2009 9: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/82563

2973 Martin Skold	2009-09-07 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_trigger.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_trigger.test
        sql/ha_ndbcluster.cc
        storage/ndb/include/kernel/signaldata/CloseComReqConf.hpp
        storage/ndb/src/kernel/blocks/ERROR_codes.txt
        storage/ndb/src/kernel/blocks/cmvmi/Cmvmi.cpp
        storage/ndb/src/kernel/blocks/dbtc/Dbtc.hpp
        storage/ndb/src/kernel/blocks/dbtc/DbtcMain.cpp
        storage/ndb/src/kernel/blocks/dbtup/Dbtup.hpp
        storage/ndb/src/kernel/blocks/dbtup/DbtupDiskAlloc.cpp
        storage/ndb/src/kernel/blocks/dbtup/DbtupMeta.cpp
        storage/ndb/src/kernel/blocks/qmgr/Qmgr.hpp
        storage/ndb/src/kernel/blocks/qmgr/QmgrMain.cpp
        storage/ndb/test/ndbapi/testNdbApi.cpp
        storage/ndb/test/run-test/autotest-boot.sh
        storage/ndb/test/run-test/daily-basic-tests.txt
[14 Sep 2009 11:09] Jon Stephens 
Documented bugfix in the NDB-6.2.19, 6.3.27, 7.0.8 changelogs, as follows:

        Using triggers on NDB tables caused
        ndb_autoincrement_prefetch_sz to be handled as the NDB kernel's
        internal default value (32) and the value for this variable as set 
        on the cluster's SQL nodes to be ignored.

Closed.