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

[10 Nov 2006 0:27] Sean Pringle
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.
[10 Nov 2006 0:28] Sean Pringle
Test Case

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

[11 Nov 2006 12:45] Valeriy Kravchuk
Thank you for a bug report. Verified just as described, also with 5.0.30-BK on Linux.
[28 Feb 2008 16:37] Mattias Jonsson
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;
}
[28 Feb 2008 21:11] 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/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.
[29 Feb 2008 10:23] 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/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)
[29 Feb 2008 10:29] 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/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)
[2 Apr 2008 12:29] Mattias Jonsson
Waiting on WL#4315
[29 Apr 2008 10:18] Mattias Jonsson
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.
[20 May 2008 20:55] Paul Dubois
Reclassifying as Documentation bug and assigning to myself.
[20 May 2008 21:02] Paul Dubois
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.