MDEV-32008 MariaDB 10.11: Auto Increment behavior

[Nezisi]

Hi, I'm seeing a very strange behavior only in MariaDB 10.11, previous MariaDB LTS version 10.6 doesn't exhibit the behavior.

What's happening:

• we use a Dockerfile to copy an SQL file
• docker-entrypoint script runs the import
• added SHOW CREATE TABLE to the SQL file shows that the auto-increment value is correct

After finishing, the server shuts down.

The next time the server starts, the auto-increment values of all tables were incremented by one.

We've set innodb_flush_log_at_trx_commit = 1. Additionally I disabled aio_async, tried to use fsync and set innodb_fast_shutdown=0 as I suspected some kind of database corruption...

Anyone got an idea what is happening?

[grooverdan]

Can you attach a minimal example of the SQL file?

[Nezisi]

-- MySQL dump 10.13  Distrib 5.7.22, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: XY
-- ------------------------------------------------------
-- Server version   5.7.25

SET foreign_key_checks=0;
SET time_zone='+00:00';
SET unique_checks=0;
SET sql_mode='NO_AUTO_VALUE_ON_ZERO';
...
--
-- Table structure for table `pcfeature`
--
DROP TABLE IF EXISTS `pcfeature`;
CREATE TABLE `pcfeature` (
  ...
) ENGINE=InnoDB AUTO_INCREMENT=62;
--
-- Dumping data for table `pcfeature`
--
LOCK TABLES `pcfeature` WRITE;
INSERT INTO `pcfeature` VALUES ...
UNLOCK TABLES;

I added a "SHOW CREATE TABLE pcfeature after the UNLOCK TABLES statement. It shows as expected that AUTO INCREMENT is set to 62.

The server then shuts down. After the next start, a SHOW CREATE TABLE shows 63 for the pcfeature table.

[dr-m]

Could this have been fixed by MariaDB/server@4700f2ac70f8c79f2ac1968b6b59d18716f492bf?

[Nezisi]

@dr-m It seems more like a regression introduced by it.

https://github.com/Nezisi/mariadb-autoinc-reproducer

I tried to whack together a small reproducer based on the docker images, with test.sh building / starting the testcontainers closely to what we do in our images, except based on this image instead of our own.

I can reproduce the bug, the output of the test.sh script is the following:

Script dir: /home/hh/Projekte/cto/mysql-test
File name: 'test.sql', **MariaDB Version: '10.6.15'**
sha256:ae048cad73928d624b1d9b0f171176c06518eb80d86a3e71be3476d5df74a618
Build docker image: 'testcontainer:10.6.15'
c5e6b0a8a0b25ac86b999c2e326dcd0a5cec82f9a50e09f9f56ff0a610c58a73
sleeping 5 secs for container start
Table   Create Table
pcfeature       CREATE TABLE `pcfeature` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB **AUTO_INCREMENT=62** DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
testcontainer
File name: 'test.sql', **MariaDB Version: '10.11.4'**
sha256:108e60a8437bd13e2852cd52ec76a4cb19b7faa1a76c6918fd513641e52ef7a9
Build docker image: 'testcontainer:10.11.4'
8127bf38319bdbbe7b0ebe6522ed42c3735df421c0a51ccf7082e2cd29110dbe
sleeping 5 secs for container start
Table   Create Table
pcfeature       CREATE TABLE `pcfeature` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB **AUTO_INCREMENT=62** DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
testcontainer
File name: 'test.sql', **MariaDB Version: '10.11.5'**
sha256:e950c622adff2655a06c970ce85fade39c1111e7a582e8181099f4ba580c8530
Build docker image: 'testcontainer:10.11.5'
cbbde42ca7e4259ef8f2522322f11c75d279bc75d08897577308ec98837e428b
sleeping 5 secs for container start
Table   Create Table
pcfeature       CREATE TABLE `pcfeature` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB **AUTO_INCREMENT=63** DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
testcontainer

10.6.15 is good, 10.11.4 is good, 10.11.5 exhibits the strange auto increment anomaly.

[grooverdan]

quite right - regression to the commit noted by @dr-m. https://jira.mariadb.org/browse/MDEV-32008 for upstream bug.

[Nezisi]

Thanks a lot @grooverdan @dr-m for the fast help.

[grooverdan]

and @Thirunarayanan for fixing it :-)

[grooverdan]

10.11.6 is now released and available as a container. Thanks for reporting this bug.