SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL: Rows Disappear from table. Any ideas?

    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
    Code:
    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.
    Quick References:
    http://www.LiveDateLove.com - free dating site
    http://www.getspeed.org - free internet bandwidth test
    http://www.alinasguide.com - web guide to internet services

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Quick References:
    http://www.LiveDateLove.com - free dating site
    http://www.getspeed.org - free internet bandwidth test
    http://www.alinasguide.com - web guide to internet services

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Longneck for your input.

    I guess it's possible that something like
    Code:
    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.
    Quick References:
    http://www.LiveDateLove.com - free dating site
    http://www.getspeed.org - free internet bandwidth test
    http://www.alinasguide.com - web guide to internet services


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •