Around April 2017 I noticed that my database lost records in a few columns of 2 tables, this happened inside 5 of databases my website is using. They are clones just with different data records. This happened after hosting company server maintenance. A few days ago the exact same issue happened again. I’ve rolled back the data from the backups again.

I’m really stressed about this and I’m searching for the solutions.

The perfect thing would be to prevent it from happening ever again, but I don’t know how to do it and first of all what is the exact mechanism behind this data loss. Hosting company technical support could not tell me either. As long as I’m aware about data loss fast (within 1 day) and it happens extremely rarely I’m fine with backups, however, I’m afraid that as database will grow larger I might not notice what have happened in time.

Are there any quick ways to detect data loss inside MySQL database?

The only ways I see for now for detecting it is to inspect each of the databases manually, open them with some database manager and sort the columns to see if there are any empty cells. Or dumping them and restoring locally on my pc, and then comparing tables with more advanced database managing software. However this is painful as it takes time, it would take probably 30-60 min daily.

Any suggestions would be greatly appreciated.

  • I'm on MySQL 5.5.41, FreeBSD & Apache.

enter image description here

  • 1
    It's more likely that it is your application that causes "empty cells". – mustaccio Jan 1 '18 at 15:18
  • If you mean by this PHP script that is running those databases, I think it’s unlikely, cause there was no any user actions/input during time when it happened, nor any script code updates. So… it’s like it’s fine for weeks and then suddenly after hosting service server maintenance half of cells in some columns are empty. However, I’m not excluding this option entirely. – Cyber Jan 1 '18 at 15:51
  • 1
    If, by "lost columns", you mean the equivalent of ALTER ... DROP COLUMN ..., then that is quite strange. If you mean "the columns now have NULLs where they used to have values, then please rephrase the Question. – Rick James Jan 1 '18 at 16:15
  • So, you have more confidence in your PHP script than in the actively maintained open source DBMS used by millions daily? – mustaccio Jan 1 '18 at 16:31
  • Rick James, - In the table there is several columns, in some rows for certain 4 columns there are empty cells while there was some data there before. I have attached a screen with a bit of visualization above. Mustaccio, - I’m really not sure where are you going with this. :) I’ve coded some parts of the script myself, It’s simple script, and I’m not seeing anything in the code that could be the cause. But for the sake of the answer, no, I don’t have full confidence in anything. – Cyber Jan 1 '18 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.