WL#4315: AUTO_INCREMENT counter for MERGE engine
Affects: Server-9.x
—
Status: Un-Assigned
Implement AUTO_INCREMENT counter for MERGE engine. Make it possible to set AUTO_INCREMENT value by CREATE/ALTER TABLE.
Current handling of AUTO_INCREMENT in the MERGE engine is incomplete:
- it is not possible to set AUTO_INCREMENT value by CREATE/ALTER table;
- AUTO_INCREMENT value is calculated as biggest AUTO_INCREMENT field value
across all tables. That means removing all rows from underlying tables will
implicitly reset AUTO_INCREMENT to 0. Removing a row with biggest
AUTO_INCREMENT value and insert a new row, will reuse AUTO_INCREMENT value of
deleted row.
Suggested implementation:
- AUTO_INCREMENT value that is set by CREATE/ALTER merge table is
*stored in a merge table counter* and used for the merge table
updates.
- MERGE table counter is stored in dot-MRG file.
- when writing to an underlying table directly, AUTO_INCREMENT value is
calculated as biggest AUTO_INCREMENT field value *of that table* + 1.
In other words: as it works now.
- when writing to a MERGE table, AUTO_INCREMENT value is a biggest of:
* biggest AUTO_INCREMENT field value across all underlying;
* AUTO_INCREMENT value of MERGE table;
* AUTO_INCREMENT value of underlying table we're going to write to
(we don't want to decrease AUTO_INCREMENT value of that table).
Alternative implementation that was proposed as fix for BUG#24159:
- when writing to a MERGE table, AUTO_INCREMENT value is calculated as a
biggest AUTO_INCREMENT field value of a table that we're going to
write to + 1;
- when writing to an underlying table directly, AUTO_INCREMENT value is
calculated as biggest AUTO_INCREMENT field value + 1;
- AUTO_INCREMENT value that is set by CREATE/ALTER merge table is silently
ignored.
A few low-level ideas below. Q: When to write AUTO_INCREMENT to disk? On external_lock(F_UNLCK). Q: How to avoid bottlenecks when writing AUTO_INCREMENT counter to disk? There is no need to rewrite the whole dot-MRG file, it is sufficient to update short area, where AUTO_INCREMENT value resides. AUTO_INCREMET value must have fixed size. E.g. write it as a [u]longlong hexadecimal string: "#AUTO_INCREMENT=0x0000000000000001". AUTO_INCREMENT line should be either first line in the dot-MRG file (constant position to a counter), or any other position that we store in a variable on open. On-disk AUTO_INCREMENT value should be updated conditionally, that is if it hasn't changed since we opened a table, it must not be written. Q: What about compatibility? When we open a table by older MySQL version, which doesn't have AUTO_INCREMENT counter, we should: - set in-memory AUTO_INCREMENT value as defined in HLS; - inform AUTO_INCREMENT update function, that there is no "#AUTO_INCREMENT" line. When we enter AUTO_INCREMENT update function, we should check if there was "#AUTO_INCREMENT" line in a dot-MRG file. If there wasn't, either rewrite the whole file, or append "#AUTO_INCREMENT" line to the end of file.
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.