MySQL Bugs: #24159: Altered base table AUTO_INCREMENT not taking effect when inserting into MERGE

Bug #24159	Altered base table AUTO_INCREMENT not taking effect when inserting into MERGE
Submitted:	10 Nov 2006 0:27	Modified:	20 May 2008 21:02
Reporter:	Sean Pringle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.30-BK, 5.0.27	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Changing the next AUTO_INCREMENT value on a base MyISAM table is not honoured in INSERT operations applied to a parent MERGE table (via INSERT_METHOD=LAST), until at least one record has been inserted directly into the base table.

How to repeat:
(Test case file described below also attached)

1) Create tables.

use test;

drop table if exists base_table;
drop table if exists merge_table;

CREATE TABLE `base_table` (
  `id` int(10) NOT NULL auto_increment primary key
) ENGINE=MyISAM;

CREATE TABLE `merge_table` (
  `id` int(10) NOT NULL auto_increment primary key
) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(`base_table`);

FLUSH TABLES;

2) Change base_table's next AUTO_INCREMENT value.

ALTER TABLE base_table AUTO_INCREMENT=1000;

3) Insert into merge_table and see base_table's auto_inc not taking effect:

INSERT into merge_table values ();
SELECT * from merge_table;

+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

4) Insert into base_table and see auto_inc take effect:

INSERT into base_table values ();
SELECT * from merge_table;

+------+
| id   |
+------+
|    1 |
| 1000 |
+------+
2 rows in set (0.00 sec)

5) Insert into merge_table again and now base_table's auto_inc works as normal:

INSERT into merge_table values ();
SELECT * from merge_table;

+------+
| id   |
+------+
|    1 |
| 1000 |
| 1001 |
+------+
3 rows in set (0.00 sec)

Suggested fix:
Don't know. The manual notes that MERGE does not enforce uniqueness across all the base tables, so perhaps this is a side effect. At the very least the manual needs to note this as a limitation.

Test Case

Attachment: test_case.sql (text/x-sql), 598 bytes.

Thank you for a bug report. Verified just as described, also with 5.0.30-BK on Linux.

I have a working prototype where it works if I implement this:
ulonglong ha_myisammrg::get_auto_increment()
{
  /* use default if secondary part of multi column index */
  if (table->s->next_number_key_offset)
    return handler::get_auto_increment();

  if (file->merge_insert_method == MERGE_INSERT_TO_FIRST)
    file->current_table= file->open_tables;
  else if (file->merge_insert_method == MERGE_INSERT_TO_LAST)
    file->current_table= file->end_table-1;
  else /* unsupported insertion method */
    return ~(ulonglong) 0;
  return file->current_table->table->s->state.auto_increment+1;
}

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

ChangeSet@1.2578, 2008-02-28 22:09:13+01:00, mattiasj@witty. +4 -0
  Bug#24159: Altered AUTO_INCREMENT not effective when inserting in MERGE
  
  Problem was that it used the default handler::get_auto_increment,
  which gets next higher value, instead of using the auto_increment directly
  from myisam.
  
  Solved by adding ha_myisammrg::get_auto_increment and using the
  share->state.auto_increment variable (that is what myisam really does,
  and should be safe since there should be a table lock taken.)
  
  Will probably have to rewrite/update this in 5.1+ when merging, due to
  changes in the get_auto_increment call.

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

ChangeSet@1.2578, 2008-02-29 11:21:57+01:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +4 -0
  Bug#24159: Altered AUTO_INCREMENT not effective when inserting in MERGE
  
  Problem was that it used the default handler::get_auto_increment,
  which gets next higher value, instead of using the auto_increment directly
  from myisam.
  
  Solved by adding ha_myisammrg::get_auto_increment and using the
  share->state.auto_increment variable (that is what myisam really does,
  and should be safe since there should be a table lock taken.)
  
  Will probably have to rewrite/update this in 5.1+ when merging, due to
  changes in the get_auto_increment call.
  (Updated with DBUG_RETURN in ha_myisammrg.cc)

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

ChangeSet@1.2531, 2008-02-29 11:28:16+01:00, mattiasj@client-10-129-10-147.upp.off.mysql.com +2 -0
  Bug#24159: Altered AUTO_INCREMENT not effective when inserting in MERGE
  
  This is the updated 5.0 patch for 5.1+
  (get_auto_increment changed from 5.0 to 5.1)

Waiting on WL#4315

Hi doc team.

Sergey V. suggested that we explain this behavior of auto_increment in MERGE (as in WL#4315), instead of applying this patch. It should be fixed properly by WL#4315.

Reclassifying as Documentation bug and assigning to myself.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Add the following paragraph to http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html (and corresponding section of other manual versions):

The INSERT_METHOD table option for a MERGE table indicates which
underlying MyISAM table to use for inserts into the MERGE table.
However, use of the AUTO_INCREMENT table option for that MyISAM table
has no effect for inserts into the MERGE table until at least one row
has been inserted directly into the MyISAM table.