CODEWITHSUNDEEP
mysqlsqlruby-on-railsmariadb
Jan Krupa
Jan Krupa

Reputation: 506

Auto Increment has been reset back to 1 automatically

I've just run into an issue which I'm not able to solve.

I have a database table project_queues which is used as a queue, where I store some records. When the records are processed, they are deleted. Deletion is invoked by rails construction record.destroy in a loop which triggers DELETE record FROM table on MySql database.

But now I've noticed, that in the table project_queues the autoIncrement Id (primary key) was set up back to 1. (Which damaged my references in the audit table. The Same record now points to multiple different project queues)

show create table project_queues;

CREATE TABLE `project_queues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   ...
   ...
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1

I do not use TRUNCATE project_queues or drop the table construction and created it again in code.

Did somebody run into a similar issue like me? I can't find any anomalies in the log either.

I'm using Rails 5.2.3, MariaDB 10.1.

The connection from the application to database enforces this SQL modes:

But I don't think that these have something to do with AI.

Upvotes: 3

Views: 1391

Answers (3)

Jan Krupa
Jan Krupa

Reputation: 506

Solved

I've upgraded 10.1.29-MariaDB MariaDB Server to 10.2.8-MariaDB MariaDB Server

Version >= 10.2.4 has solved the resetting of Auto Increment value

Upvotes: 1

Jan Krupa
Jan Krupa

Reputation: 506

Well ok, I found the problem (it's a known issue from 2013).

Here is how to reproduce the problem.

# Your MariaDB Server version: 10.1.29-MariaDB MariaDB Server
# Engine InnoDB

create database ai_test;
use ai_test;

CREATE TABLE IF NOT EXISTS ai_test(id INT AUTO_INCREMENT PRIMARY KEY, 
                                   a VARCHAR(50));

show table status like 'ai_test' 
  >  Auto_increment: 1

INSERT INTO ai_test(a) VALUES ('first');
INSERT INTO ai_test(a) VALUES ('second');
INSERT INTO ai_test(a) VALUES ('third');

show table status like 'ai_test' 
  >  Auto_increment: 4

MariaDB [ai_test]> Delete from ai_test where a = 'first';
MariaDB [ai_test]> Delete from ai_test where a = 'second';
MariaDB [ai_test]> Delete from ai_test where a = 'third';

show table status like 'ai_test' \G
  >  Auto_increment: 4

# Restart Server
sudo service rh-mariadb101-mariadb stop
sudo service rh-mariadb101-mariadb start

show table status like 'ai_test' \G
  >  Auto_increment: 1

I'll try to find some workaround to solve this problem, but I think this cause havoc to many use cases which refers to some archive tables or something like that.

References:

Upvotes: 1

PiGo
PiGo

Reputation: 149

Auto_increment can be reset by updates. Since you're using it as a queue I suppose you don't make updates, but it's worth asking.

Also, what table implementation do you use, MyIsam, InnoDB, other ... ?

Upvotes: 1

Related Questions