WL#4315: AUTO_INCREMENT counter for MERGE engine
Affects: Server-9.x — Status: Un-Assigned — Priority: Medium
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, 2017, Oracle Corporation and/or its affiliates. All rights reserved.