Bug #40176 Combination of event, trigger and partitioning breaks auto increment
Submitted: 20 Oct 2008 13:43 Modified: 10 Nov 2008 15:40
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.29/5.1.30 OS:Any
Assigned to: Mattias Jonsson
Tags: regression
Triage: Triaged: D2 (Serious)

[20 Oct 2008 13:43] Elena Stepanova
Description:
If a table is created with partitioning and auto increment, and a trigger and an event are created for the table in a specific order, auto increment value on the table stays less than the last inserted value, thereby breaking future inserts (unless they set the auto-incremented column value explicitly).

The provided script is the least number of steps I could downgrade the initial case to. The following details seem important:

- the table t is created with auto-increment and partitioning. I tried partition by hash, key and range, with 2 and 1 partitions, the result was the same; but if partitioning is not set at all, the problem does not appear.

- values inserted into the table t, specifically in the column f1 (used by the event created later) should 
  a) contain a value called by the event, and
  b) this value should not be the last inserted.

- creating a trigger on the table seems essential, while the contents of it does not matter. The empty one suffices.

- event is created so it starts immediately. If the start is delayed, the problem does not show up. 

After executing the script the last three queries produce the following result:

show create table t 
 shows AUTO_INCREMENT=2 (while 3 is expected)

select max(i) from t
+--------+
| max(i) |
+--------+
|      2 |
+--------+
(just as expected)

insert into t ( f1, f2 ) values ( 100, 'tst' );
ERROR 1022 (23000): Can't write; duplicate key in table 't'

Generally, it looks like in these conditions auto_increment value is set to the next value after the last row updated by the event.

e.g. 

if the table contains 
i = 1, f1 = 10
i = 2, f1 = 20
and event updates rows with f1 = 10, auto increment becomes 2

if the table contains
i = 1, f1 = 10
i = 2, f1 = 20
i = 3, f1 = 20
i = 4, f1 = 12
and event updates rows with f1 = 20, auto increment becomes 4

etc.

I tried 5.1.26 also, but could not reproduce it there.
Found bug#33479 which might be related, but I'm not sure if the patch fixes the problem.

How to repeat:
SET GLOBAL event_scheduler = ON;

drop database if exists part_test;
create database part_test;

use part_test;

create table t (
   i int NOT NULL auto_increment, primary key (i),
      f1 int,
      f2 char(15)
)
PARTITION BY HASH(i) PARTITIONS 1;

insert into t ( f1, f2 ) values ( 10, 'tst-1' ), ( 20, 'tst-2' );

Create trigger trg before delete on t for each row
BEGIN END;

delimiter |

CREATE EVENT ev ON SCHEDULE EVERY 6 MINUTE
            DO BEGIN
                update t set f2='ev-upd' where f1=10;
END |

delimiter ;

-- select is needed if you execute it as a script, not manually

select sleep(2);

-- here the preparation ends, the rest is only verification
-- if everything goes well, auto increment in show table will be greater than max(i)
-- and the record will be inserted successfully

show create table t \G
select max(i) from t;
insert into t ( f1, f2 ) values ( 100, 'tst' );
[20 Oct 2008 15:24] Elena Stepanova
In fact, the event creation as such is unnecessary -- instead, a direct update of the same kind can be performed. The trigger is still important though.
[20 Oct 2008 15:44] Miguel Solorzano
Thank you for the bug report. Verified as described. Not repeatable in older released version 5.1.22:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.03 sec)

mysql 5.1 >

<cut>

mysql 5.1 >select max(i) from t;
+--------+
| max(i) |
+--------+
|      2 |
+--------+
1 row in set (0.03 sec)

mysql 5.1 >insert into t ( f1, f2 ) values ( 100, 'tst' );
ERROR 1022 (23000): Can't write; duplicate key in table 't'

**********************************************************
C:\temp\mysql-5.1.22-rc-win32>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.22-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)

mysql>

<cut>

mysql> select max(i) from t;
+--------+
| max(i) |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql> insert into t ( f1, f2 ) values ( 100, 'tst' );
Query OK, 1 row affected (0.00 sec)

mysql> select max(i) from t;
+--------+
| max(i) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)
[20 Oct 2008 18:17] Mattias Jonsson
Here is a simpler test case:
create table t1 (
a int not null auto_increment primary key,
b int)
partition by hash(a);
insert into t1 (b) VALUES (1), (2), (3);
flush tables;
update t1 set b = 4 where a = 1;
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a)  */

The bug is that it does not initialize the auto_increment value in update, resulting in if the first statement is a update it will use '0' as the previously inserted value.

The fix is simple, just check if the auto_increment value in the partitioning engine is initialized before updating it (if not, just initialize it).

Will probably propose this patch:
=== modified file 'sql/ha_partition.cc'
--- sql/ha_partition.cc	2008-10-06 13:14:20 +0000
+++ sql/ha_partition.cc	2008-10-20 18:11:37 +0000
@@ -3006,7 +3006,12 @@
     */
     if (table->found_next_number_field && new_data == table->record[0] &&
         !table->s->next_number_keypart)
+    {
+      HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
+      if (!ha_data->auto_inc_initialized)
+        info(HA_STATUS_AUTO);
       set_auto_increment_if_higher(table->found_next_number_field->val_int());
+    }
     reenable_binlog(thd);
     goto exit;
   }
@@ -3018,7 +3023,12 @@
     error= m_file[new_part_id]->ha_write_row(new_data);
     if (table->found_next_number_field && new_data == table->record[0] &&
         !table->s->next_number_keypart)
+    {
+      HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
+      if (!ha_data->auto_inc_initialized)
+        info(HA_STATUS_AUTO);
       set_auto_increment_if_higher(table->found_next_number_field->val_int());
+    }
     reenable_binlog(thd);
     if (error)
       goto exit;

=== modified file 'sql/ha_partition.h'
--- sql/ha_partition.h	2008-10-01 10:14:55 +0000
+++ sql/ha_partition.h	2008-10-20 18:14:24 +0000
@@ -878,10 +878,10 @@
   {
     HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
     lock_auto_increment();
+    DBUG_ASSERT(ha_data->auto_inc_initialized == TRUE);
     /* must check when the mutex is taken */
     if (nr >= ha_data->next_auto_inc_val)
       ha_data->next_auto_inc_val= nr + 1;
-    ha_data->auto_inc_initialized= TRUE;
     unlock_auto_increment();
   }
[20 Oct 2008 21:29] Elena Stepanova
Upon Trim's request also checked 5.1.28-rc: the problem does not appear there (neither with trigger nor with flush).
[23 Oct 2008 20: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/56944

2676 Mattias Jonsson	2008-10-23
      Bug#40176: update as first partitioning statement
      breaks auto increment
      
      The auto_increment value was not initialized if
      the first statement after opening a table was
      an 'UPDATE'.
      
      solution was to check initialize if it was not,
      before trying to increase it in update.
[6 Nov 2008 10:10] Mattias Jonsson
pushed into mysql-5.1-bugteam and mysql-6.0-bugteam
[10 Nov 2008 10:50] Bugs System
Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081023201407-azhl9kzz4pdhojmz) (version source revid:mattias.jonsson@sun.com-20081106061815-rub56lesg18z5ens) (pib:5)
[10 Nov 2008 11:35] Bugs System
Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081023201407-azhl9kzz4pdhojmz) (version source revid:mattias.jonsson@sun.com-20081106062907-gkpe2vr84le6c7wm) (pib:5)
[10 Nov 2008 15:40] Jon Stephens
Documented bugfix in the 5.1.31 and 6.0.8 changelogs as follows:

        For a partitioned table having an AUTO_INCREMENT column: If the first
        statement following a start of the server or a FLUSH TABLES statement
        was an UPDATE, the AUTO_INCREMENT column was not incremented correctly.

(Fix appears in 5.1.31 and not 5.1.30 per note from Joro.)
[19 Jan 2009 11:30] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:07] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:13] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)