[MDEV-30796] Auto_increment values changed after restart Created: 2023-03-06  Updated: 2023-08-29  Resolved: 2023-07-26

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.11.2, 10.10.3, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.9.8, 10.10.6, 10.11.5, 11.1.2, 11.2.1

Type: Bug Priority: Major
Reporter: Martin Štěpař Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: not-10.6, regression
Environment:

Ubuntu 22.04.2 LTS, Hyper-V virtual machine

Attachments: File 1-create-tables.sql     File 2-import-broken.sql     File 3-import-fixed.sql     PNG File image-2023-03-06-14-09-34-196.png     PNG File table-status-result.png    
Issue Links:
Problem/Incident
causes MDEV-32008 auto_increment value on table increme... Closed
is caused by MDEV-24621 In bulk insert, pre-sort and build in... Closed

 Description   

I imported sql dump from MySQL 5.5.62 to MariaDB. Imported file has about 2GB. Import was successful, but after MariaDB restart AUTO_INCREMENT values on some tables changed (for example: on table with max ID 11504 AUTO_INCREMENT changed from 11505 to 2).

After some testing I figured out that removing UNIQUE_CHECKS option from import file fixes this. I cannot see any common cause, some affected tables have unique keys and some doesn't. There are no errors on log or anywhere. Import was tested on newly installed Ubuntu server without any additional software running.

What other information should I provide?

SQL dump was created with mysqldump Ver 10.13 Distrib 5.5.62: 

mysqldump -p --single-transaction --no-create-db --no-create-info --skip-add-drop-table --skip-comments "database_name" > db.sql

Command for file import:

mariadb -u root "database_name" < "db.sql"

Using default configuration with these changes:



 Comments   
Comment by Daniel Black [ 2023-03-06 ]

Is the AUTO_INCREMENT on the primary or a secondary key? What table types are affected? Can you include some samples of SHOW CREATE TABLE for affected and unaffected tables? Obscure column names if you want to keep them private, but do keep them consistent with indexes. Text is preferred over images so its easier to recreate the test case.

Comment by Martin Štěpař [ 2023-03-07 ]

Thanks for your reply. I have done some additional tests and simplify whole issue to few steps:

Install MariaDB

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash -s -- --skip-maxscale --skip-tools
 
apt install -y mariadb-server
 
 
 
mariadb-secure-installation
 
	Switch to unix_socket authentication: n
 
	Change the root password? n
 
	Remove anonymous users? y
 
	Disallow root login remotely? y
 
	Remove test database and access to it? y
 
	Reload privilege tables now? y
 
 
 
cat <<EOT >> /etc/mysql/mariadb.conf.d/90-custom.cnf
 
[mysqld]
 
skip_name_resolve		= on
 
bind_address			= 0.0.0.0
 
performance_schema		= on
 
max_connections			= 80
 
slow_query_log			= off
 
slow_query_log_file		= /var/log/mysql/mariadb-slow.log
 
long_query_time			= 0.4
 
log_slow_verbosity		= query_plan,explain
 
collation_server		= utf8mb4_unicode_ci
 
old_mode			=
 
innodb_buffer_pool_size		= 8G
 
innodb_log_file_size		= 2G
 
innodb_print_all_deadlocks	= on
 
join_buffer_size		= 1M
 
EOT
 
 
 
systemctl restart mysql

Create database and import data

Using these SQL files: 1-create-tables.sql 2-import-broken.sql 3-import-fixed.sql 

mariadb -e "CREATE DATABASE import_issues;"
 
mariadb -u root "import_issues" < "1-create-tables.sql"
 
mariadb -u root "import_issues" < "2-import-broken.sql"
 
mariadb -u root "import_issues" < "3-import-fixed.sql"
 
reboot

Retrieve AUTO_INCREMENT values

mariadb
 
use import_issues
 
show table status\G

I got this:

Comment by Sergei Golubchik [ 2023-03-30 ]

I'm sorry, I cannot repeat this, after loading your files both tables show 

Auto_increment  27

There must be something more that I need to do to repeat this

Comment by Martin Štěpař [ 2023-04-11 ]

Even after system reboot? I tried it once again on DigitalOcean and captured whole procedure (from creating droplet to table status), so you can reproduce it exactly: https://youtu.be/BPwyYDHFNcA

Comment by Martin Štěpař [ 2023-05-11 ]

Hi guys, can you please watch the video above (https://youtu.be/BPwyYDHFNcA)? You will see this behavior on a new pure Ubuntu instance in the DigitalOcean cloud, step by step to reproduce it (exactly following the instructions from the previous comments).

Comment by Alice Sherepa [ 2023-06-19 ]

Thank you for the report!
I repeated as described, on 10.7+, while 10.6 and earlier versions behaved as expected.

--source include/have_innodb.inc
 
 
 
SET NAMES utf8;
 
SET foreign_key_checks = 0;
 
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
 
 
 
SET NAMES utf8mb4;
 
 
 
DROP TABLE IF EXISTS `fixed`;
 
CREATE TABLE `fixed` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(50) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `name` (`name`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 
 
DROP TABLE IF EXISTS `broken`;
 
CREATE TABLE `broken` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(50) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `name` (`name`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 
 
 
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
 
 
LOCK TABLES `broken` WRITE;
 
/*!40000 ALTER TABLE `broken` DISABLE KEYS */;
 
INSERT INTO `broken` VALUES (2,'Celková šířka'),(14,'Celková tloušťka'),(6,'Celková výška'),(1,'Jmenovitá šířka'),(5,'Jmenovitá výška'),(10,'Korekce tloušťky'),(15,'Přesah šířky'),(16,'Přesah šířky pro olep'),(17,'Přesah výšky'),(18,'Přesah výšky pro olep'),(19,'Šířka pláště'),(4,'Šířka polotovaru'),(3,'Šířka pro formátování'),(23,'Šířka průchodu'),(24,'Šířka stavebního otvoru'),(25,'Šířka ve falci'),(11,'Tloušťka rámu'),(13,'Tloušťka spodní desky'),(22,'Tloušťka spodní výplňové desky'),(12,'Tloušťka vrchní desky'),(21,'Tloušťka vrchní výplňové desky'),(9,'Tloušťka výplně'),(20,'Výška pláště'),(8,'Výška polotovaru'),(7,'Výška pro formátování'),(26,'Výška ve falci');
 
/*!40000 ALTER TABLE `broken` ENABLE KEYS */;
 
UNLOCK TABLES;
 
 
 
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 
 
 
 
 
INSERT INTO `fixed` VALUES (2,'Celková šířka'),(14,'Celková tloušťka'),(6,'Celková výška'),(1,'Jmenovitá šířka'),(5,'Jmenovitá výška'),(10,'Korekce tloušťky'),(15,'Přesah šířky'),(16,'Přesah šířky pro olep'),(17,'Přesah výšky'),(18,'Přesah výšky pro olep'),(19,'Šířka pláště'),(4,'Šířka polotovaru'),(3,'Šířka pro formátování'),(23,'Šířka průchodu'),(24,'Šířka stavebního otvoru'),(25,'Šířka ve falci'),(11,'Tloušťka rámu'),(13,'Tloušťka spodní desky'),(22,'Tloušťka spodní výplňové desky'),(12,'Tloušťka vrchní desky'),(21,'Tloušťka vrchní výplňové desky'),(9,'Tloušťka výplně'),(20,'Výška pláště'),(8,'Výška polotovaru'),(7,'Výška pro formátování'),(26,'Výška ve falci');
 
 
 
 
 
--vertical_results
 
show table status;
 
 
 
--source include/restart_mysqld.inc
 
 
 
--vertical_results
 
show table status;
 
 
 
insert into broken(name) values ('a');


MariaDB Version 10.7.8-MariaDB-debug
 
 - SSL connections supported
 
 - binaries are debug compiled
 
 - binaries built with wsrep patch
 
Collecting tests...
 
Installing system database...
 
 
 
==============================================================================
 
 
 
TEST                                      RESULT   TIME (ms) or COMMENT
 
--------------------------------------------------------------------------
 
 
 
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
 
SET NAMES utf8;
 
SET foreign_key_checks = 0;
 
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
 
SET NAMES utf8mb4;
 
DROP TABLE IF EXISTS `fixed`;
 
Warnings:
 
Note	1051	Unknown table 'test.fixed'
 
CREATE TABLE `fixed` (
 
`id` int(11) NOT NULL AUTO_INCREMENT,
 
`name` varchar(50) NOT NULL,
 
PRIMARY KEY (`id`),
 
UNIQUE KEY `name` (`name`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
DROP TABLE IF EXISTS `broken`;
 
Warnings:
 
Note	1051	Unknown table 'test.broken'
 
CREATE TABLE `broken` (
 
`id` int(11) NOT NULL AUTO_INCREMENT,
 
`name` varchar(50) NOT NULL,
 
PRIMARY KEY (`id`),
 
UNIQUE KEY `name` (`name`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 
LOCK TABLES `broken` WRITE;
 
/*!40000 ALTER TABLE `broken` DISABLE KEYS */;
 
Warnings:
 
Note	1031	Storage engine InnoDB of the table `test`.`broken` doesn't have this option
 
INSERT INTO `broken` VALUES (2,'Celková šířka'),(14,'Celková tloušťka'),(6,'Celková výška'),(1,'Jmenovitá šířka'),(5,'Jmenovitá výška'),(10,'Korekce tloušťky'),(15,'Přesah šířky'),(16,'Přesah šířky pro olep'),(17,'Přesah výšky'),(18,'Přesah výšky pro olep'),(19,'Šířka pláště'),(4,'Šířka polotovaru'),(3,'Šířka pro formátování'),(23,'Šířka průchodu'),(24,'Šířka stavebního otvoru'),(25,'Šířka ve falci'),(11,'Tloušťka rámu'),(13,'Tloušťka spodní desky'),(22,'Tloušťka spodní výplňové desky'),(12,'Tloušťka vrchní desky'),(21,'Tloušťka vrchní výplňové desky'),(9,'Tloušťka výplně'),(20,'Výška pláště'),(8,'Výška polotovaru'),(7,'Výška pro formátování'),(26,'Výška ve falci');
 
/*!40000 ALTER TABLE `broken` ENABLE KEYS */;
 
Warnings:
 
Note	1031	Storage engine InnoDB of the table `test`.`broken` doesn't have this option
 
UNLOCK TABLES;
 
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 
INSERT INTO `fixed` VALUES (2,'Celková šířka'),(14,'Celková tloušťka'),(6,'Celková výška'),(1,'Jmenovitá šířka'),(5,'Jmenovitá výška'),(10,'Korekce tloušťky'),(15,'Přesah šířky'),(16,'Přesah šířky pro olep'),(17,'Přesah výšky'),(18,'Přesah výšky pro olep'),(19,'Šířka pláště'),(4,'Šířka polotovaru'),(3,'Šířka pro formátování'),(23,'Šířka průchodu'),(24,'Šířka stavebního otvoru'),(25,'Šířka ve falci'),(11,'Tloušťka rámu'),(13,'Tloušťka spodní desky'),(22,'Tloušťka spodní výplňové desky'),(12,'Tloušťka vrchní desky'),(21,'Tloušťka vrchní výplňové desky'),(9,'Tloušťka výplně'),(20,'Výška pláště'),(8,'Výška polotovaru'),(7,'Výška pro formátování'),(26,'Výška ve falci');
 
show table status;
 
Name	broken
 
Engine	InnoDB
 
Version	10
 
Row_format	Dynamic
 
Rows	26
 
Avg_row_length	630
 
Data_length	16384
 
Max_data_length	0
 
Index_length	16384
 
Data_free	0
 
Auto_increment	27
 
Create_time	2023-06-19 13:48:30
 
Update_time	2023-06-19 13:48:30
 
Check_time	NULL
 
Collation	utf8mb4_unicode_ci
 
Checksum	NULL
 
Create_options	
Comment	
Max_index_length	0
 
Temporary	N
 
Name	fixed
 
Engine	InnoDB
 
Version	10
 
Row_format	Dynamic
 
Rows	26
 
Avg_row_length	630
 
Data_length	16384
 
Max_data_length	0
 
Index_length	16384
 
Data_free	0
 
Auto_increment	27
 
Create_time	2023-06-19 13:48:30
 
Update_time	2023-06-19 13:48:30
 
Check_time	NULL
 
Collation	utf8mb4_unicode_ci
 
Checksum	NULL
 
Create_options	
Comment	
Max_index_length	0
 
Temporary	N
 
# restart
 
show table status;
 
Name	broken
 
Engine	InnoDB
 
Version	10
 
Row_format	Dynamic
 
Rows	26
 
Avg_row_length	630
 
Data_length	16384
 
Max_data_length	0
 
Index_length	16384
 
Data_free	0
 
Auto_increment	3
 
Create_time	2023-06-19 13:48:30
 
Update_time	NULL
 
Check_time	NULL
 
Collation	utf8mb4_unicode_ci
 
Checksum	NULL
 
Create_options	
Comment	
Max_index_length	0
 
Temporary	N
 
Name	fixed
 
Engine	InnoDB
 
Version	10
 
Row_format	Dynamic
 
Rows	5
 
Avg_row_length	3276
 
Data_length	16384
 
Max_data_length	0
 
Index_length	16384
 
Data_free	0
 
Auto_increment	27
 
Create_time	2023-06-19 13:48:30
 
Update_time	NULL
 
Check_time	NULL
 
Collation	utf8mb4_unicode_ci
 
Checksum	NULL
 
Create_options	
Comment	
Max_index_length	0
 
Temporary	N
 
insert into broken(name) values ('a');
 
main.1_my 'innodb'                       [ fail ]
 
        Test ended at 2023-06-19 13:48:32
 
 
 
CURRENT_TEST: main.1_my
 
mysqltest: At line 51: query 'insert into broken(name) values ('a')' failed: ER_DUP_ENTRY (1062): Duplicate entry '3' for key 'PRIMARY'

Regression after 045757af4c301757ba4492693 commit (MDEV-24621 In bulk insert, pre-sort and build indexes one page at a time)

Comment by Marko Mäkelä [ 2023-07-26 ]

OK to push after addressing some minor review comments.

Basically, in MDEV-24621 we forgot to take MDEV-6076 into account.

Generated at Thu Feb 08 10:18:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.