I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, the row vanishes (despite no other queries being executed).

This table has an integer ID as a primary key, with auto-increment set, and the primary key is auto-incremented as expected for new rows. This leads me to believe there is not some kind of transactional rollback (and my table is MyISAM, so that shouldn't be possible anyways) or anything else that is somehow reverting the database to a previous state.

What logs should I be checking to see what is going on here? The contents of my '/var/log/mysql/error.log' are below, but I don't see anything unusual.

120815 21:01:01 [Note] Plugin 'FEDERATED' is disabled.
120815 21:01:02  InnoDB: Initializing buffer pool, size = 8.0M
120815 21:01:02  InnoDB: Completed initialization of buffer pool
120815 21:01:03  InnoDB: Started; log sequence number 0 44233
120815 21:01:03 [Note] Event Scheduler: Loaded 0 events
120815 21:01:03 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.63-0ubuntu0.11.10.1'  socket: '/var/run/mysqld/mysqld.sock'      port: 3306  (Ubuntu)
120815 21:01:04 [ERROR] /usr/sbin/mysqld: Table './appname/users' is marked as crashed and should be repaired
120815 21:01:04 [Warning] Checking table:   './appname/users'
120815 21:10:34 [Note] /usr/sbin/mysqld: Normal shutdown

120815 21:10:34 [Note] Event Scheduler: Purging the queue. 0 events
120815 21:10:34  InnoDB: Starting shutdown...
120815 21:10:39  InnoDB: Shutdown completed; log sequence number 0 44233
120815 21:10:39 [Note] /usr/sbin/mysqld: Shutdown complete

I noted the 'crashed' mark on the appname/users table, but mysqlcheck suggests the table is OK.

Any thoughts?

  • Have you tried myisamchk instead? – ESG Oct 23 '13 at 1:36
  • Is there any EVENT running? – ypercubeᵀᴹ Oct 23 '13 at 6:23

The disappearing rows are a common behavior of mysqlcheck (or myisam check), because it tries to repair the corrupted blocks. Other engines (eg. Oracle) have more advanced repairing algorithms, that can actually preserve your data.

Corruption usually appears when there's a problem with the disk (space, partition headers etc), so start by checking them. Then check the integrity of your data files, and make sure they can expand correctly.

PS: When runing mysqlcheck, you're using the --auto-repair argument?


Look for a trigger on the table (it may be removing the row).

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.