自动增量已自动重置回 1

2022-01-15 00:00:00 sql ruby-on-rails mariadb mysql

我刚刚遇到了一个我无法解决的问题.

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

我有一个数据库表 project_queues 用作队列，我在其中存储一些记录.当记录被处理时，它们被删除.删除由 Rails 构造 record.destroy 在循环中调用，该循环触发 MySql 数据库上的 DELETE record FROM table.

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.

但现在我注意到，在 project_queues 表中，autoIncrement Id(主键)被设置回 1.(这损坏了我在审计表中的引用.相同的记录现在指向多个不同的项目队列)

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

我不使用 TRUNCATE project_queues 或删除表结构并在代码中重新创建它.

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.

我正在使用 Rails 5.2.3、MariaDB 10.1.

从应用程序到数据库的连接强制执行这种 SQL 模式:

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

  • NO_AUTO_VALUE_ON_ZERO
  • STRICT_ALL_TABLES
  • NO_AUTO_CREATE_USER
  • NO_ENGINE_SUBSTITUTION
  • NO_ZERO_DATE
  • NO_ZERO_IN_DATE
  • ERROR_FOR_DIVISION_BY_ZERO

但我不认为这些与人工智能有关.

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

推荐答案

已解决

我已将 10.1.29-MariaDB MariaDB Server 升级到 10.2.8-MariaDB MariaDB Server

版本 >= 10.2.4 已解决自动增量值的重置

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

相关文章