错误的错误消息：“重复输入'284093'用于键'PRIMARY'” [英] Wrong error message: “duplicate entry '284093' for key 'PRIMARY'”

问题描述

在MySQL数据库（版本5.7.18）中， MyISAM 表 sensorhistory 有一列 id 类型 int（11），附加 auto_increment 。

数据是从用C＃编写的应用程序中插入的。当然， INSERT 查询不会直接写入id列。这就是auto_increment的用途。该表包含另外30个字段 float 和 varchar 类型，分别加上 DateTime （3）。参数化查询很长，所以我在这里省略它。

目前，4台机器每隔~30秒就会在表格中写一行。

我收到以下错误信息：

In a MySQL database (version 5.7.18), MyISAM table sensorhistory has a column id of type int(11) with Extra auto_increment.
Data are inserted from an application written in C#. The INSERT query does NOT write the id column directly, of course. That's what the "auto_increment" is for. The table contains further 30 fields of float and varchar types, resp., plus a DateTime(3). The parameterized query is long, so I omit it here.
Currently, 4 machines write a line into the table every ~30 seconds.
I receive following error message:

Duplicate entry '284093' for key 'PRIMARY'
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at DataStorage.SensorHistoryDatastore.StoreSensorHistory(IReadOnlyList'1 _reports, Boolean _canRetry) in C:\Users\bernhard.hiller\SVN\Product-SW\trunk\C_DataStorage\PhysicalContainers\SensorHistoryDatastore.cs:line 84

我尝试了什么：



奇怪的是，当我执行 SELECT max（id） FROM sensorhistory 在 MySQL Workbench 中，我得到的最大值 284092 ，即少于1这是错误信息错误的证据。



一个分析表在MySQL Workbench中显示该表已损坏：在第6585344页找到指示记录外部数据文件的键

它可以通过简单修复修复表sensorhistory



仅在1天后重新发生错误，相同的表，相同的错误消息（只是有了新的价值）。



我对如何发生表的损坏以及如何防止它重新发生感兴趣。

What I have tried:

Oddly, when I execute SELECT max(id) FROM sensorhistory in MySQL Workbench, I get a maximum value of 284092, i.e. 1 less than the "duplicate entry" of 284093. That is proof that the error message is wrong.

An Analyze table in MySQL Workbench showed that the table is corrupt: Found key at page 6585344 that points to record outside datafile
It could be repaired with a simple Repair table sensorhistory

The error re-occurred after just 1 day, same table, same error message (just with a new value).

I am interested in how that corruption of the table can have occurred, and how to prevent it from re-occuring.

推荐答案

首先检查机器日志：它在最近几天崩溃并重新启动？来自MySql本身或来自磁盘检查器的任何恶意？



可能值得运行磁盘检查以查看硬盘是否出现故障 - 作为一次性它有各种各样的可能性，但如果它反复发生，那么有点表明它可能是某个地方的硬件问题。

Start by checking the machine logs: has it crashed in the last few days and restarted? Any "nasties" from MySql itself, or from disk checkers?

Might be worth running a disk check to see if the HDD is failing - as a "one off" it's got all sorts of possibilities, but if it happens repeatedly then that kinda indicates it may be a hardware problem somewhere.

这篇关于错误的错误消息：“重复输入'284093'用于键'PRIMARY'”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！