Click here to Skip to main content
14,422,059 members
Rate this:
Please Sign up or sign in to vote.
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 11-Sep-18 23:38pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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:

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100