Bug #40176 Combination of event, trigger and partitioning breaks auto increment

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' );

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.

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)

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();
   }

Upon Trim's request also checked 5.1.28-rc: the problem does not appear there (neither with trigger nor with flush).

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.

pushed into mysql-5.1-bugteam and mysql-6.0-bugteam

Pushed into 6.0.8-alpha  (revid:mattias.jonsson@sun.com-20081023201407-azhl9kzz4pdhojmz) (version source revid:mattias.jonsson@sun.com-20081106061815-rub56lesg18z5ens) (pib:5)

Pushed into 5.1.30  (revid:mattias.jonsson@sun.com-20081023201407-azhl9kzz4pdhojmz) (version source revid:mattias.jonsson@sun.com-20081106062907-gkpe2vr84le6c7wm) (pib:5)

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.)

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)

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)

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)