September 16, 2014

AUTO_INCREMENT and MERGE TABLES

How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table… which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting AUTO_INCREMENT set for the Merge Table itself. Neither of these expectations really true:

So you can see merge table behaves quite smart. Even though the maximum value in table a2 was 1 it finds out what other subtable had value 2 and assigns the value 3 to the new row.

Let us see how stable merge table auto_increment values are by truncating the table we just inserted data to:

As you see newly inserted row got value 3, which is maximum value which remained in the table plus 1. So auto_increment values are reusable just as with old ISAM tables. Such behavior does not only corresponds to TRUNCATE – deleting last row will cause auto_increment value to be reused too.

Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not:

As you can see Merge table does not even stores auto_increment value (and of course does it without any warnings)

Neither setting auto_increment value for underlying MyISAM tables works:

So for bad or for good you should remember auto_increment for Merge Tables works differently from both MyISAM and Innodb tables and being similar to what ISAM tables used to have. As the side effect of this – if you’re using auto_increment columns inserting into Merge Table and in the last table directly will have different results.

Another thing I should remind you should be very careful while running ALTER TABLE for underlying tables. Unlike with TRUNCATE TABLE there is no warning displayed but the MERGE TABLE may continue having old file descriptors open not seeing changes done to original table:

As you can see delete is invisible in the merge table after we did alter table until we have run flush tables.

I’ve done my tests with MySQL 5.0.62.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Speak Your Mind

*