mySQL自动递增问题:密钥1的条目"4294967295"重复 [英] mySQL auto increment problem: Duplicate entry '4294967295' for key 1

问题描述

我有一张电子邮件表.

其中最后一个自动递增ID的记录是3780，这是合法记录.我现在插入的任何新记录都将插入到那里.

The last record in there for an auto increment id is 3780, which is a legit record. Any new record I now insert is being inserted right there.

但是，在我的日志中偶尔会有:

However, in my logs I have the occasional:

Query FAIL: INSERT INTO mail.messages (timestamp_queue) VALUES (:time);
Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '4294967295' for key 1
)

不知何故，自动增量跃升到INT最大4294967295

Somehow, the autoincrement jumped up to the INT max of 4294967295

为什么在神的绿色土地上这会跳得那么高?我没有带有ID字段的插入内容.

Why on god's green earth would this get jumped up so high? I have no inserts with an id field.

该表的显示状态(自动增量表)现在显示为:4294967296

The show status for that table, Auto_increment table now reads: 4294967296

这样的事情怎么会发生?我意识到id字段可能应该是一个很大的整数，但是我担心的是，这件事会以某种方式跳回去.

How could something like this occur? I realize the id field should perhaps be a big int, but the worry I have is that somehow this thing jumps back up.

乔什

更新

修补了mysql 5.0.45版红色帽子

mysql version 5.0.45 red hat patched

由于我将ID设置为BIGINT，所以最后几个ID如下:

Since I set the id to BIGINT the last few id's look like:

3777
3778
3779
3780
4294967295
4294967296
4294967297
4294967298
4294967299
4294967300

如您所见，它们是增量的，没有间隙(到目前为止).完全奇怪.

As you can see, they are incremental, with no gaps (so far). Totally weird.

推荐答案

对我来说，这还不是很清楚，但我想我应该跟进.

Its still not totally clear to me what happened here, but I thought I'd follow up.

在我的持久性引擎中，我有一种类型的对象，其对象具有自动递增的ID，并且具有一个具有GUID ID的子类.

In my persistence engine, I had one type of object with a auto-increment id, and a subclass with a GUID id.

显然这两个是不兼容的.我有理由将对象转换为其父对象，然后保存(基本上，子类是具有其他功能的电子邮件模板，但是当我想实际发送电子邮件时，我将其转换为父对象并将其保存到常规传出邮件队列).愚蠢的是，我没有意识到id格式是不同的.这导致尝试将长度为36个字符的字符串的记录插入int.字符串在准备好的语句中解析为"0"，并且由于任何原因，这都会导致自动递增系统WIG OUT并最大化原始表上的INT自动递增id字段.

Obviously the two were incompatible. I have a reason to convert the object to its parent and then save it (basically the subclass is an email TEMPLATE that has additional functionality, but when i want to actually SEND the email, I convert it to the parent object and save it to the regular outgoing mail queue). Stupidly, I didn't realize the id formats were different. This resulted in trying to insert a record with a 36 character long string into an int. The string resolved to '0' in the prepared statement and for whatever reason this cause the auto-increment system to WIG OUT and max out the INT auto increment id field on the original table.

长话短说，我一直在记录日志.

Long story short, good thing I was keeping logs.

乔什

这篇关于mySQL自动递增问题:密钥1的条目"4294967295"重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！