[MDEV-6076] Persistent AUTO_INCREMENT for InnoDB - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Fix Version/s: 10.2.4
Component/s: Storage Engine - InnoDB
Labels:
- innodb

Sprint:
10.2.4-1

Description

The current auto_increment behavior in the InnoDB engine is sub-optimal. As it currently functions, the auto_increment value is stored until the server shuts down or resets, and then is rebuilt based on values in the table when it starts up again. Furthermore, in 5.6 this ought to become even worse, because tables can be evicted from the InnoDB data dictionary cache. We may get a too low auto-increment value even without shutdown/restart. When a table is evicted, InnoDB will forget the current auto-increment value, and it will do SELECT MAX(auto_inc_column) next time when the table is accessed.

Attachments

Issue Links

blocks

MDEV-11578 Remove the requirement to have an INDEX on AUTO_INCREMENT column

Open

causes

MDEV-20775 InnoDB: Failing assertion: !page_zip || page_zip_validate(page_zip, page, index)

Closed

is duplicated by

MDEV-13074 AliSQL: [Feature] Issue #42 Persistent AUTO_INCREMENT value for InnoDB table

Closed

relates to

MDEV-12123 Page contains nonzero PAGE_MAX_TRX_ID

Closed

MDEV-18288 Transportable Tablespaces leave AUTO_INCREMENT in mismatched state, causing INSERT errors in newly imported tables when .cfg is not used.

Closed

MDEV-20892 AUTO_INCREMENT is set lower than the max value of the primary_key

Closed

MDEV-22357 Clearing InnoDB autoincrement counter when upgrading from MariaDB < 10.2.4

Open

MDEV-26224 InnoDB fails to remove AUTO_INCREMENT attribute

Closed

MDEV-12848 InnoDB: Assertion failure in file mariadb-10.2.5/storage/innobase/handler/ha_innodb.cc line 2780

Closed

MDEV-13094 SHOW CREATE TABLE can report non-persistent AUTO_INCREMENT value before server restart

Confirmed

MDEV-20357 Strange auto_increment counter setting changes on direct upgrade from 10.1 to 10.3

Closed

MDEV-33277 In-place migration from MySQL 5.7 causes invalid AUTO_INCREMENT values

Closed

(7 relates to)

Activity

Ascending order - Click to sort in descending order

View 16 older comments

Marko Mäkelä added a comment - 2016-12-15 20:12

I pushed a fix to the conflict with ~~MDEV-5800~~/WL#8114/WL#8149 (virtual columns).
The new function innodb_col_no() will determine the dict_table_t::cols[] offset of a Field.

Marko Mäkelä added a comment - 2016-12-15 20:12 I pushed a fix to the conflict with MDEV-5800 /WL#8114/WL#8149 (virtual columns). The new function innodb_col_no() will determine the dict_table_t::cols[] offset of a Field.

Jan Lindström (Inactive) added a comment - 2016-12-16 07:55

This looks good now.

Jan Lindström (Inactive) added a comment - 2016-12-16 07:55 This looks good now.

Marko Mäkelä added a comment - 2016-12-16 08:35

Thanks! I just pushed to bb-10.2-mdev-6076 rebased to latest 10.2, so that we avoid one test failure due to a bug in slow shutdown that was introduced in ~~MDEV-5800~~ and fixed later.

The only code change since the review is the correction of a debug assertion that I had added late yesterday:

diff --git a/storage/innobase/page/page0zip.cc b/storage/innobase/page/page0zip.cc

index 39eadda318f..798aeeed74f 100644

--- a/storage/innobase/page/page0zip.cc

+++ b/storage/innobase/page/page0zip.cc

@@ -4851,8 +4851,8 @@ page_zip_copy_recs(

 	} else {

 		/* The PAGE_MAX_TRX_ID must be nonzero on leaf pages

 		of secondary indexes, and 0 on others. */

-		ut_a(dict_table_is_temporary(index->table)

-		     || page_is_leaf(src) == !page_get_max_trx_id(src));

+		ut_ad(dict_table_is_temporary(index->table)

+		      || !page_is_leaf(src) == !page_get_max_trx_id(src));

 	/* Copy all fields of src_zip to page_zip, except the pointer

I also removed many restarts from the test innodb.autoinc_persist, testing more things across each restart.

Marko Mäkelä added a comment - 2016-12-16 08:35 Thanks! I just pushed to bb-10.2-mdev-6076 rebased to latest 10.2, so that we avoid one test failure due to a bug in slow shutdown that was introduced in MDEV-5800 and fixed later. The only code change since the review is the correction of a debug assertion that I had added late yesterday: diff --git a/storage/innobase/page/page0zip.cc b/storage/innobase/page/page0zip.cc index 39eadda318f..798aeeed74f 100644 --- a/storage/innobase/page/page0zip.cc +++ b/storage/innobase/page/page0zip.cc @@ -4851,8 +4851,8 @@ page_zip_copy_recs( } else { /* The PAGE_MAX_TRX_ID must be nonzero on leaf pages of secondary indexes, and 0 on others. */ - ut_a(dict_table_is_temporary(index->table) - || page_is_leaf(src) == !page_get_max_trx_id(src)); + ut_ad(dict_table_is_temporary(index->table) + || !page_is_leaf(src) == !page_get_max_trx_id(src)); } /* Copy all fields of src_zip to page_zip, except the pointer I also removed many restarts from the test innodb.autoinc_persist, testing more things across each restart.

Marko Mäkelä added a comment - 2016-12-29 13:31

Now that 10.2.3 was released and 10.1 was merged to 10.2, I finally pushed this.

Marko Mäkelä added a comment - 2016-12-29 13:31 Now that 10.2.3 was released and 10.1 was merged to 10.2, I finally pushed this.

Marko Mäkelä added a comment - 2017-04-19 05:22

~~MDEV-12123~~ fixed a related bug. When using IMPORT TABLESPACE with data files on which IMPORT TABLESPACE has been used before the ~~MDEV-12123~~ fix, the AUTO_INCREMENT value will be restored to a bogus value (something that was a transaction ID in the previous IMPORT).

Marko Mäkelä added a comment - 2017-04-19 05:22 MDEV-12123 fixed a related bug. When using IMPORT TABLESPACE with data files on which IMPORT TABLESPACE has been used before the MDEV-12123 fix, the AUTO_INCREMENT value will be restored to a bogus value (something that was a transaction ID in the previous IMPORT).

People

Assignee:: Marko Mäkelä

Reporter:: Jan Lindström (Inactive)

Votes:: 2 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2014-04-11 16:26

Updated:: 2024-01-19 07:23

Resolved:: 2016-12-29 13:31

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server