Mysql DB Table Rows Disappearing

Question

Hello,

A really weird (for me) problem is occurring lately. In an application that accepts user submitted data the following occurs at random:

Rows from the Database Table where the user submitted data is stored are disappearing.

Please note that there is NO DELETE, DROP, TRUNCATE or other SQL statement issued on the database table except from the INSERT statement.

Could this be a bug of Mysql? Did some research on mysql.com (forums, bugs, etc) and found 2 similar cases but without getting a solid answer (just suggestions).

Some info you might find useful: Storage Engine: InnoDB User Submitted Data sanitized and checked for SQL Injection attempts

Appreciate any suggestions, info.

regards,

Answer 1

A table on which you only ever insert (and presumably select) and never update or delete should be really stable. Are you absolutely certain you're protecting thoroughly against SQL injection attacks? Because those could (of course) delete rows and such if successful.

You haven't mentioned which table engine you're using (there are several), but it's well worth running whatever diagnostic tools there are for it on the table in question. For instance, on a MyISAM table, run myisamchk. Or more generically (this works for several table types), use the CHECK TABLE statement.

Have you had issues with the underlying storage? It may be worth checking for those.

Answer 2

Here's 3 possibilities:

1. The data never got to the database in the first place. Something happened elsewhere so the data disappeared. Maybe intermitten network issues, overloaded server, application bug.

2. A database transaction was not commited, and got rolled back. Maybe a bug in your application code, maybe some invalid data screwd things up, maybe a concurrency exception occured etc.

3. A bug in mysql.

I'd look at 1. and 2. first.