MYSQL db problem

Discussion in 'Web Design & Coding' started by jimi_81, Jul 12, 2006.

  1. jimi_81

    jimi_81 Moderator Political User

    Messages:
    820
    Location:
    Stoney Creek, ON, Canada
    This is the second time this has happened.
    I have lost my db. The hosting company (ipowerweb) cannot explain why this is happening.

    How can a db be deleted/removed, without having access to a control panel or the db itself. Is that even possible? Do you think it is possibly a security breach? Can an SQL server be overwhelmed to the point where db's are lost?

    I have a backup of last nights data, so i am OK, but this is SUCH a pain in the ass.

    any insight would be appreciated, thanks guys
     
  2. j79zlr

    j79zlr Glaanies script monkey Political User

    Messages:
    2,725
    Location:
    Chicago
    You can lose a db in many ways, anyone with root access to the server can delete it. I would complain first, and if this is a common occurence, switch hosts.
     
  3. LordOfLA

    LordOfLA Godlike!

    Messages:
    7,027
    Location:
    Maidenhead, Berkshire, UK
    Welcome to one of mysql's major flaws.

    Mysql can and does totally loose databases out of the blue at random with narry a whisper as to why.

    ipowerweb quite possibly genuinly dont' know why its happening.
     
  4. jimi_81

    jimi_81 Moderator Political User

    Messages:
    820
    Location:
    Stoney Creek, ON, Canada
    thanks for the words guys.

    Tech support had a look at the issue. They were telling me that some of my tables were corrupt.. and needed repairing. I repaired them all. Not sure why they were corrupt.

    i read up on some mysql knowledge base articles and found a rather extensive list of why this happens. doesnt appear to be a way to fix things tho. all my database connections close when they are supposed to.. so i guess its time to get a cron job running daily to backup the db.

    grrr
     
  5. X-Istence

    X-Istence * Political User

    Messages:
    6,498
    Location:
    USA
    MySQL does this quite often. There are a plethora of reasons why it could happen, none of them really make any sense.

    Like LordOfLa said, ipowerweb really can't know why, however if it happens often, I suggest you look for another database solution, since many of these problems can be resolved just by putting MySQL under less stress (thus having less databases, and less load).

    I would suggest as alternative, take a look at PostGreSQL, if it is possible with what you need it to do.
     
  6. Mainframeguy

    Mainframeguy Debiant by way of Ubuntu Folding Team

    Messages:
    3,763
    Location:
    London, UK
    and if you truly need to go "industrial" strength on your data integrity and make this a most remote possibility I would suggest moving to IBM's UDB (born out of DB2).... But there will be major re-engineering and licensing considerations.... it just depends how much you want to put in to remove this pain - I suspect the investment in this to be too much - but the pay back is pretty guaranteed.

    Oracle is anothe possibility, but I know less about that so hesitate to commend it personally.

    GL with the chron job ;)
     
  7. X-Istence

    X-Istence * Political User

    Messages:
    6,498
    Location:
    USA
    Free for the win!
     
  8. jimi_81

    jimi_81 Moderator Political User

    Messages:
    820
    Location:
    Stoney Creek, ON, Canada
    haha, guys, i get paid 2500 a YEAR to do this site: http://www.stoneycreeksoccer.com

    ipowerweb has been the host provider for us for about 5 years now. This year we totally changed the statistics program around, and now we are starting to run into db issues. I repaired all tables, despite having knowledge about how a client fails to close a table. could it be that i mixed in my amateur style query statements with my buddy's inner join and left join combo queries. is an unclosed table referring to opening a table, grabbing a result and then immediately querying another table.. with a couple more queries.. all in a loop?
    thats pretty confusing.. can someone explain what a table not closing means?

    thanks!!
     
  9. X-Istence

    X-Istence * Political User

    Messages:
    6,498
    Location:
    USA
    Not closing a table has to do with the MySQL connection being interrupted before properly closing the connection.

    MySQL should not be showing this behaviour at all. Your SQL queries should have nothing to do with this problem, as I have seen this problem even for big forums based on vBulletin. This is strictly a MySQL is under too much load, and is not writing tables to disk in time. Like many have said before me, and as have I, find a different host, or talk to ipowerweb and ask them to move your database to a different server.

    There is no clear explanation, and no clear reason why. Just know that it can and will happen, and it looks like you are prepared with backups.
     
  10. X-Istence

    X-Istence * Political User

    Messages:
    6,498
    Location:
    USA