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.