MySQL Bugs: #65529: Any table trigger breaks multiple insert statement into single statements.

Bug #65529	Any table trigger breaks multiple insert statement into single statements.
Submitted:	6 Jun 2012 5:56	Modified:	8 Jun 2012 3:57
Reporter:	M K	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	multiple insert, trigger

Description:
Trigger affects multiple insert statement: in case of trigger one multiple statement processed as several single statements. 
You can see it in multiple threaded environment using autoincrementing field.

Such behaviour affects JDBC driver, so last returned identifiers which were calculated for new rows are incorrect.

How to repeat:
1. Create a talbe with autoincrement primary key field.
2. Create a trigger for table from step 1 with empty body, any event (update, insert, delete) and any timing.
3. Insert data into table from step 1 via several threads simultaneously using multiple insert statement. 
   Data should contain some feature of inserting thread (i.e. thread identifier).
   
   Create database statement:
   
   -- test database 
   create database if not exists db_test;
   use db_test;
   create table test_table (id int not null auto_increment, value varchar(255)) ENGINE = innodb;
   
   delimiter $$;
   create trigger trg1 before delete on test_table for each row
   begin
   end;$$
   delimiter ;$$
   -- test database
   
   Thread 1 insert statement:
   
   insert into test_table
   (value)
   values('test_data_1_1'),
   values('test_data_2_1'),
   values('test_data_3_1'),
   values('test_data_4_1'),
   values('test_data_5_1');

   Thread 2 insert statement:
   
   insert into test_table
   (value)
   values('test_data_1_2'),
   values('test_data_2_2'),
   values('test_data_3_2'),
   values('test_data_4_2'),
   values('test_data_5_2');
   
   ......................
   
   Thread n insert statement:
   
   insert into test_table
   (value)
   values('test_data_1_n'),
   values('test_data_2_n'),
   values('test_data_3_n'),
   values('test_data_4_n'),
   values('test_data_5_n');   

   Expected result:
   Inserted groups of data are not mixed in the table.
   
   id    | value
   -------------------------
   1     | test_data_1_1
   2     | test_data_2_1
   3     | test_data_3_1
   4     | test_data_4_1
   5     | test_data_5_1
         |
   6     | test_data_1_2
   7     | test_data_2_2
   8     | test_data_3_2
   9     | test_data_4_2
   10    | test_data_5_2
         |
   .........................
         |
   n*5+1 | test_data_1_n*5+1
   n*5+2 | test_data_2_n*5+2
   n*5+3 | test_data_3_n*5+3
   n*5+4 | test_data_4_n*5+4
   n*5+5 | test_data_5_n*5+5

   Current result:
   Data rows from different threads are mixed.
   
   id    | value
   -------------------------
   1     | test_data_1_1
   2     | test_data_1_2
   3     | test_data_2_2
   4     | test_data_2_1
   5     | test_data_1_3
         |
   6     | test_data_2_3
   7     | test_data_3_1
   8     | test_data_4_1
   9     | test_data_3_2
   10    | test_data_4_2
         |
   .........................

Suggested fix:
Multiple insert statement should be processed as atomic statement in any case

Please, send the output of:

show global variables like 'innodb_autoinc%';

from your server.

inc locking enabled. Its working perfect without any trigger.

mysql> show global variables like 'innodb_autoinc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.05 sec)

Thank you for the feedback.

I can not repeat described behavior with current development version, although it is repeatable with 5.5.13 for me. Please try with current version 5.5.25 and inform us if problem still exists in your environment.

We've tried with 5.5.25 and system works as expected. Bug is not repeatable under 5.5.25 version. Thank you so much.

Not repeatable with 5.5.25.