[Solved] Wrong error message: “duplicate entry '284093' for key 'PRIMARY'”

相关文章推荐

瘦瘦的绿茶 · 异步编程 - ADO.NET ...· 1 年前 ·

精明的饺子 · Vue中使用matomo进行访问流量统计 - 掘金· 1 年前 ·

还单身的镜子 · C++字符串赋值给字符指针的问题 | 西海岸· 2 年前 ·

风流的啄木鸟 · 使用keras时报错AttributeErr ...· 2 年前 ·

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
What I have tried:
Oddly, when I execute


   SELECT max(id) FROM sensorhistory


   MySQL Workbench

, I get a maximum value of

, 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. 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.

The last machine crash was on Sep 11, 5:06 in the morning. No crashs since then, and no database problems either. What a coincidence! A postmortem would be nice, but my impression is that our admin prefers to repeat that at a customer site (the problems happened on our test system in the office): obviously, it works NOW.

Read the question carefully.

Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.

If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.

Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question. Let's work to help developers, not make them feel stupid.