Bug #20195 INSERT DELAYED with auto_increment is assigned wrong values

Description:
When using delayed inserts a separate counter is used to maintain the next value used. The result is discarded rows and reused values if delayed inserts is combined with normal inserts.

This is actually much easier to explain with an example. See the "How to repeat" section.
What you should see is that as long as the delayed insert thread is living, it maintains its own counter which is not synchronized with the normal counter for auto_increment.
The discarding rows is a bad behavior. Since the database returns ok, you will never know that you are loosing data.
Reusing id's is not as bad, but really annoying, if you are watching the table, looking for new id's.

A few comments before you go:

You have to do the queries within a fairly short time, since the tread handling the delayed inserts, times out and is killed (delayed_insert_timeout defaults to 5 min.). The next delayed insert will create a new thread with a new counter, causing this test case not to prove the actual problem.

The database still returns ok, even though your insert delayed query is discarded.

This test case actually explains bug #20043, and most likely others too.

The test case has been testet on release 5.0.18, 5.0.21 and 5.0.22 with an out of the box installation.

How to repeat:
#
# TESTCASE
#

# create new table
DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (
  id int(10) unsigned NOT NULL auto_increment,
  info varchar(255) ,
  PRIMARY KEY (id)
);

# Make one delayed insert to start the separate thread
insert delayed into mytable (id,info) values(null,'delayed');

# Do some normal inserts
insert into mytable (id,info) values(null,'normal');
insert into mytable (id,info) values(null,'normal');

# Check what we have so far
select * from mytable order by id;

# +----+---------+
# | id | info    |
# +----+---------+
# |  1 | delayed |
# |  2 | normal  |
# |  3 | normal  |
# +----+---------+

# Discarded, since the delayed-counter is 2, which is already used
insert delayed into mytable (id,info) values(null,'delayed');

# Discarded, since the delayed-counter is 3, which is already used
insert delayed into mytable (id,info) values(null,'delayed');

# Works, since the delayed-counter is 4, which is unused
insert delayed into mytable (id,info) values(null,'delayed');

# Check what we have now
select * from mytable order by id;

# +----+---------+
# | id | info    |
# +----+---------+
# |  1 | delayed |
# |  2 | normal  |
# |  3 | normal  |
# |  4 | delayed |
# +----+---------+

# Do some more inserts
insert into mytable (id,info) values(null,'normal');
insert into mytable (id,info) values(null,'normal');
insert into mytable (id,info) values(null,'normal');

# Delete one of the above to make a hole
delete from mytable where id=6;

# Discarded, since the delayed-counter is 5, which is already used
insert delayed into mytable (id,info) values(null,'delayed');

# Works, since the delayed-counter is 6, which is unused (the row we deleted)
insert delayed into mytable (id,info) values(null,'delayed');

# Discarded, since the delayed-counter is 7, which is already used
insert delayed into mytable (id,info) values(null,'delayed');

# Works, since the delayed-counter is 8, which is unused
insert delayed into mytable (id,info) values(null,'delayed');

# Check what we have now
select * from mytable order by id;

# +----+---------+
# | id | info    |
# +----+---------+
# |  1 | delayed |
# |  2 | normal  |
# |  3 | normal  |
# |  4 | delayed |
# |  5 | normal  |
# |  6 | delayed |
# |  7 | normal  |
# |  8 | delayed |
# +----+---------+

The Binary Log for this test case

Attachment: bin-log.txt (text/plain), 2.82 KiB.

Hi!

Thanks for your bug report.  I was able to reproduce this bug under FC5 using 5.0.23bk:

mysql> CREATE TABLE IF NOT EXISTS mytable (
    ->   id int(10) unsigned NOT NULL auto_increment,
    ->   info varchar(255) ,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> # Make one delayed insert to start the separate thread
mysql> insert delayed into mytable (id,info) values(null,'delayed');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Do some normal inserts
mysql> insert into mytable (id,info) values(null,'normal');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytable (id,info) values(null,'normal');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Check what we have so far
mysql> select * from mytable order by id;
+----+---------+
| id | info    |
+----+---------+
|  1 | delayed | 
|  2 | normal  | 
|  3 | normal  | 
+----+---------+
3 rows in set (0.00 sec)

mysql> 
mysql> insert delayed into mytable (id,info) values(null,'delayed');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Discarded, since the delayed-counter is 3, which is already used
mysql> insert delayed into mytable (id,info) values(null,'delayed');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Works, since the delayed-counter is 4, which is unused
mysql> insert delayed into mytable (id,info) values(null,'delayed');
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> # Check what we have now
mysql> select * from mytable order by id;
+----+---------+
| id | info    |
+----+---------+
|  1 | delayed | 
|  2 | normal  | 
|  3 | normal  | 
|  4 | delayed | 
+----+---------+
4 rows in set (0.00 sec)

mysql> insert into mytable (id,info) values(null,'normal');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytable (id,info) values(null,'normal');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mytable (id,info) values(null,'normal');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Delete one of the above to make a hole
mysql> delete from mytable where id=6;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Discarded, since the delayed-counter is 5, which is already used
mysql> insert delayed into mytable (id,info) values(null,'delayed');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Works, since the delayed-counter is 6, which is unused (the row we deleted)
mysql> insert delayed into mytable (id,info) values(null,'delayed');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Discarded, since the delayed-counter is 7, which is already used
mysql> insert delayed into mytable (id,info) values(null,'delayed');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Works, since the delayed-counter is 8, which is unused
mysql> insert delayed into mytable (id,info) values(null,'delayed');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # Check what we have now
mysql> select * from mytable order by id;
+----+---------+
| id | info    |
+----+---------+
|  1 | delayed | 
|  2 | normal  | 
|  3 | normal  | 
|  4 | delayed | 
|  5 | normal  | 
|  6 | delayed | 
|  7 | normal  | 
|  8 | delayed | 
+----+---------+
8 rows in set (0.00 sec)

mysql>

As pointed by Mr Olsen, BUG#20043 has large connections with this one (the "delayed has its own counter" observation explains the absence of one row from the table in BUG#20043; however the absence of another row from the binlog remains a specificity of BUG#20043 so far).

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/7446

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/7573

Fixed in 5.0.23, 5.1.12

The INSERT DELAYED should not maintain its own private auto-increment
counter, because this is assuming that other threads cannot insert
into the table while the INSERT DELAYED thread is inserting, which is
a wrong assumption.

Noted in 5.0.23, 5.1.12 changelogs.