MySQL: Rows Disappear from table. Any ideas?

[alexpr07]

My company developed and is administering a dating site. Recently we noted that some member profiles disappear.
We changed the database and server passwords (just in case) and enabled query log in mysql admin tools.

Today I noticed that another profile disappeared. I opened the log but could not find something like

DELETE FROM members WHERE username='username'

Any ideas how and why profiles disappear? Perhaps it was a different query that deleted the row?

If you need more information, PM me or write in this thread. Help is greatly appreciated.

[longneck]

some further suggestions:

1) make sure the user that you app logs in as has the absolute minimum permissions needed to work. for example, if your app shouldn't be able to delete users at all, then don't give it delete permissions.

2) compare a backup from a few days ago when you know the users were there to the current table. maybe the users aren't disappearing but instead have their usernames changed for some inexplicable reason.

3) do a search in the log for any reference to the username that you say disappeared.

[alexpr07]

Thanks for the advice. I did a search in the log for the username and did not find anything suspicious. Only admins can change the username, does not seem like any of them did, plus I didn't see it in the log.

[longneck]

now is not the time to rule things out because "only admins can change the username". i still suggest comparing backups to see what's different. you may find something that points you in the right direction.

however, if you can't find anything in the query log that shows the row being changed, then you likely have a data corruption issue caused by bad memory, faulty, drive, faulty drive controller, etc., so it might be time to start looking at that kind of stuff.

[alexpr07]

Thanks Longneck for your input.

I guess it's possible that something like

UPDATE members SET username='username2' WHERE member_number='number'

was executed, but I don't know how to find this kind of thing since I don't know the index (member number) of the missing record. I guess I'll look for all UPDATE queries, it might take a while though....

I also read that REPLACE query could delete a row in certain cases but did not find any REPLACE queries in the log.

The database is on VPS, I checked it with MySQL Admin tool for errors, did not find any.