Click here to Skip to main content
15,123,118 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 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.
Posted
Updated 30-Mar-21 11:43am

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.
   
Comments
Bernhard Hiller 14-Sep-18 4:02am
   
In both cases, there was a crash of the machine, as can be seen in Windows eventlog with id 6008: "The previous system shutdown was unexpected". There were 70 (!) such crashes within 4 days, and they ended on Sep 11. I do not know what was changed on that machine, our admin will have to find out.
OriginalGriff 14-Sep-18 4:05am
   
70 in 4 days! Oooo f*ck! That's not good news at all.
Explains your corruption problems though.
Bernhard Hiller 19-Sep-18 3:39am
   
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.
OriginalGriff 19-Sep-18 3:47am
   
Sounds like somebody took a rogue app back off the server? :laugh:
I had the same issue when I was using a bulk insert. After changing to inserting one record at a time it worked fine.
   
Comments
Bernhard Hiller 1-Apr-21 7:05am
   
Thanks for your input, Floyd. MySQL turns out to be very strange, the longer you work with it the stranger it looks. We can be rather sure that your issue had some other chain of cause, but an interesting finding.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900