4

I have a table that holds some data.

From the log, I can see that it inserted a record, and got its ID.

The ID came from MySQL as it’s an auto-increment. Then, about 2 seconds later, that record must have vanished as, from a log, I can see it could not find it anymore.

This happened at 2019-11-18 21:06.

In the DB I can see that the record is missing. The next inserted record does exist. This is the first time a record vanished, or that I have been aware of, in about 5 years running this.

In the MySQL log, I got messages like these and they are about the table which has a vanished entry, (myfield, myservername, mydb.mytable I have replaced the original names), there are also messages like these before this event.

Nov 18 00:33:49 myservername MySQL: 2019-11-18 0:33:49 139725497882368 [Warning] InnoDB: Cannot add field myfield in table mydb.mytable because after adding it, the row size is 8840 which is greater than maximum allowed size (8126) for a record on index leaf page.

The actual question, can this be the reason for a record disappearing?

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
user5542121
  • 1,051
  • 12
  • 28
  • 4
    I am not familiar with MySQL kicking out older records in favor of newer ones. I think if space were the issue, then the insert would have failed. – Tim Biegeleisen Nov 18 '19 at 10:42
  • 2
    Try this https://dba.stackexchange.com/a/90140 – CaffeinatedCod3r Nov 18 '19 at 10:58
  • 3
    How sure are you that the record was really inserted into the db? Is there a chance that the insert was part of a transaction that had multiple statements and the transaction was aborted after the row was inserted into the db? That would explain why the id was taken from the auto-increment sequence and why the row does not exist at the moment. – Dimitar Spasovski Nov 18 '19 at 11:00
  • 99% sure that it got inserted. There are no transactions used. But I will verify that now - to be sure (I am not the only one touching the code). – user5542121 Nov 18 '19 at 11:02
  • Double checked, there is no transaction, autocommit is on, strict mode is on, its 10.2.27-MariaDB, table is innodb. – user5542121 Nov 18 '19 at 11:09

1 Answers1

0

It is a user problem. An administrator has deleted the entry - he managed to delete an entry which was just created 5 sec ago.

I still must figure out, what those errors mean, since the field exists. But that is an entirely different question. Thanks for comments, reassured me it can’t be a bug in MySQL, so I continued to search.

For reference I found this on dba.stackexchange, but as far as I can tell, that would not cause a vanishing row, the way I had it experienced. https://dba.stackexchange.com/questions/47109/mysql-row-does-not-persist-but-primary-key-with-auto-increment-is-incremented

user5542121
  • 1,051
  • 12
  • 28