[FIXED] MySQL Rows Disappearing (Mysteriously)

Issue

I have 3 tables A, B and C which are directly linked and should contain the same number of rows, the rows for each table being created together.

I've recently noticed that after creating 1000 rows for test purposes (rows are definitely inserted, no transactions, system stable), and without any application activity, there seem to be rows missing. A random amount of rows (0-50) from each table (e.g -30, -24, -7 which vary) seem to be missing after several hours of inactivity.

This has happened several times, and at first I didn't notice, but now I am positive. There is no code with anything such as DELETE FROM table(A/B/C), so I have my doubts about a coding bug doing this.

I am using phpMyAdmin when I notice the missing rows, and a direct CLI query confirms this. I am certain I am not accidentally deleting the rows through the GUI. Also, this is on a local test environment, so no (hopefully) public access.

Is there something in MySQL (a limitation setting) that could be causing this? These are not unusual tables or rows, 10 columns each, CHAR/INT/ETC. types, 250kb each total each table.

Would a binary log file show which rows are being deleted and when and by whom? Any special considerations for configuring the logging?

• Language is PHP, MySQL is InnoDB, WAMP, Windows, viewed through phpMyAdmin

Solution

You are using phpMyAdmin.

The preview of a table gives a estimated row count, not the actual one if you have alot of rows in your table.

Try

SELECT COUNT(*) FROM tableA;
SELECT COUNT(*) FROM tableB;
SELECT COUNT(*) FROM tableC;

and see if there is really a different row count.

Otherwise, do you have any triggers set? Or any integrity issue with innoDB?

Answered By - Steini