SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot Technics's Avatar
    Join Date
    Aug 2000
    Posts
    184
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I've got a problem.

    Some databases on my server have gotten currpted. I've tryed deleting them to re-create them and no joy wont kill the databases.

    New ones we create work fine.

    I just cannot seem to fix/delete borken databases how would i do so?

  2. #2
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Check out a very good tutorial about this at http://www.eva2000.com/mysqlrepair.txt . It's by JEng who, if you don't know, is a mySQL god. Here it is...

    Code:
    Database Table Corruption (How to recognize, repair, and prevent)
    
    Introduction
    
    When your database starts misbehaving all of a sudden, it is possible that
    you may have a database or table corruption. I'm not referring about leaving
    a cold drink on a hot day on a wood table without a coaster, but the tables
    in the database are damaged.
    
    I hope this document will help you to recognize and repair any database
    damage. If you're database administrator and you choose to ignore repairing
    the database, then it might be important to know about using coasters in 
    your
    new career serving drinks.
    
    Generally, the techniques can be applied to most database servers. This
    document will use MySQL version 3.22 as an example. The utilities used to
    perform these functions should also be available to other database servers.
    Check your documentation. This document assumes that you have some 
    familiarity
    of database terminology, and where your database utilities and files are
    located.
    
    
    Recognizing that a problem exists
    
    A database server is very tolerant. In other words, it will do its best to
    get the data you need, even if a corruption exists. However, when it can't,
    you'll need to recognize that you may have a problem. Some common signs to
    look for are:
    
      o Obviously, a valid previously working request returns with some error
        message or number (e.g. table errors, index errors, etc.)
      o Results don't seem to be correct. For example, sort order may not be
        sorted or sorted randomly, random results, weird characters in known
        text-only fields, etc.
      o Unable to locate records that are known to exist (sometimes it can 
    locate
        it without using indexes, but if this is the case, the indexes are
        damaged)
      o General updating, inserting, and deleting of records may suddenly stop
        working.
    
    When these symptoms appears, don't panic. Do some preliminary checks. Do you
    have enough disk space? Is it a valid request (e.g. correct syntax, 
    spelling,
    etc.)? Attempt to locate the data differently. Can a single "known" record
    be retrieved? Can the last record be retrieved?
    
    If the results don't appear correctly (or not at all) and you confident in
    your request, then a corruption might exist. It's time to go to the next
    section.
    
    
    Repairing your database
    
    Now that the worse has happened, it's time to fix it. To reiterate, I'll be
    using MySQL version 3.22 as an example. This is my method of repairing a
    database. There are other techniques, but this my general method. (Besides,
    I can charge an absurd consulting fee to use some of my secret methods). An
    outline of the steps is listed below:
    
      o Remove and prevent all users from accessing the database.
      o Shut down the database server.
      o Create a backup of the existing database state, separate from a known
        valid backup.
      o Use the database utilities to check for database corruptions.
      o Repair any damaged database tables.
      o Check database for corruptions again (in case you missed any problems).
      o Restart database server and allow user access back to the database.
      o Clean up (i.e. remove that backup that you created above).
    
    Remove and Prevent Users to Database
    
    It is important to remove any users accessing the database before attempting
    any repairs. Obviously, you can't check and repair the database when it's
    constantly changing. There may be other database server that may allow
    concurrent usage while checking and repairing, but to be cautious, it's best
    to disable access to the general public.
    
    Shut Down the Database Server
    
    There may be database servers that require the server to be active. However,
    in MySQL case, you'll need to stop the database server before doing any
    maintenance. To do so, you can either use these commands:
    
      mysql.server stop  or  mysqladmin shutdown
    
    Once MySQL has stopped, you should see a message that it has ended. It is
    important to make sure that MySQL has stopped before proceeding on. It is
    possible that the MySQL may be in the middle of an un-interruptable 
    operation
    or has some requests left to finish. If this is the case, MySQL will finish
    those requests and then it will stop. To make sure, check your running
    processes. For Linux or Unix users:
    
      ps aux|grep mysql
    
    This command will display any processes that contains the word "mysql". Some
    of them will be the actual database server, and others may be a request. 
    Also,
    there will be one that is yourself, but if you see others, MySQL is still
    active. Give MySQL some time to finish up.
    
    If MySQL still refuses to leave, then you'll need to make it leave. 
    Depending
    on your user access (as you might need to be 'root' user), you'll need
    individually kill those process. Without being too violent, check the 
    running
    process again and note the first column, the process ID. There's two other
    columns that are important, CPU usage time and start time. Choose one that 
    is
    high on CPU usage or earliest start time and note the process ID. Then 
    execute
    the command:
    
      kill {process-id}
    
    Hopefully, MySQL will end. Check your running process list again. If that 
    one
    process that you "killed" is not listed, select another and repeat.
    
    If that process you attempted to "kill" but refused to die (for lack of a
    better term), then it's time to get a little violent. Instead of using sharp
    objects, repeat the "kill" with the option "-9" as follow (but read the
    caution below first):
    
      kill -9 {process-id}
    
    The -9 option forces that process to leave - no matter what. This should
    remove the process. Caution: Use this as a last resort since haphazardly
    immediately stopping processes without letting them gracefully finish can
    cause memory leaks, lost resources, possibly more database corruptions, and
    other operating system problems. Personally, I have never need to get this
    violent.
    
    Continue looking for any lingering MySQL processes and repeat. Eventually,
    MySQL will get the hint.
    
    Create a Backup
    
    Create a separate backup of the database files. This is optional, but in
    case your repair doesn't go as planned, then at least you can go back to
    something and try a different method. If worse comes to worse, and the 
    repair
    process decides to lop off chunks of data, you do have a valid backup, 
    right?
    
    Anyway, you can either use mysqldump to get a text dump of your database, or
    use a file copy (i.e. "cp"). If you use a file copy, be sure to copy all of
    the files.
    
    NOTE: mysqldump may or may not give you a complete backup. Remember, we're
    working on a possible corrupt database. mysqldump will do its best, but you
    should be wary of what you'll get. This is why you want to make it separate
    from a known good backup. Also, if you use mysqldump, you'll need to do this
    step prior to shutting down the database server.
    
    Check for Corruption and Repair
    
    After all of the preparations above, it's finally time to check the 
    database.
    Use the MySQL utility isamchk to check all of the tables. If a corruption
    exist on one of the tables, it's likely that other corruptions may exist.
    Use the following syntax:
    
      isamchk {table name}...  or  isamchk *.ISM
    
    If isamchk reports any problems or recommends fixing that table, repeat
    isamchk with a "-r" option on that table to repair. During the repair, 
    isamchk
    will do its best to repair any corruption without losing any data, but there
    are no guarantees.
    
    Once you're finished repairing any damaged table, repeat checking all of the
    tables again to make sure (just to be safe).
    
    NOTE: MySQL is a relational database, but as of version 3.23, it doesn't
    contain referential integrity. Without going into boring details, 
    referential
    integrity is an extra validation handled by the database server across
    multiple tables that the data is valid (or loosely speaking - properly
    linked). In MySQL case, referential integrity is manually handled by the
    application (or the programmer). The point that I'm trying to emphasize here
    is: although, isamchk will repair damaged database tables, it can't check
    if the data integrity is correct (i.e. correctly linked). Hope that wasn't
    too boring.
    
    
    Restart the Database Server
    
    To restart the MySQL database server, use the command:
    
      mysql.server start
    
    If everything looks and works OK, clean up any files that you created during
    the repair process.
    
    
    Preventive Maintenance (subtitled "Why Me?")
    
    Database servers are made to be fault-tolerant, but there are times when
    problems can occur. Should a power outage occur (or if some technician
    trips over the power cord), I/O hardware failures, or if the machine reboots
    without letting MySQL finish up, then it's possible for corruption or data
    loss.
    
    With MySQL, you can minimize data loss by having MySQL flush the tables 
    (i.e.
    write all outstanding data from memory cache to the disk). You can manually
    flush the tables with "mysqladmin flush-tables", or set MySQL to 
    automatically
    flush the tables on a regular time interval.
    
    In any case, when such an event happens, then as a database administrator,
    it's your responsibility to check the database. Follow the steps outlined
    above and you'll keep your database running nice and happy. If not, the next
    time you go to a restaurant, be sure to ask the person who is serving cold
    water if he used to be a database administrator who didn't maintain his
    database (or a technician who trips over power cords). Good luck!
    
    
    Reference
    
    http://www.MySQL.com/documentation/
    <Edited by qslack on 01-21-2001 at 11:47 AM>

  3. #3
    SitePoint Zealot Technics's Avatar
    Join Date
    Aug 2000
    Posts
    184
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i read that and tryed what it said but i dont have a clue which tables to check.

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The MySQL Administration chapter of my PHP/MySQL article explains how to diagnose and repair corrupted databases as well.

    How do you know your database is corrupt if you don't know which tables to check?
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  5. #5
    SitePoint Zealot Technics's Avatar
    Join Date
    Aug 2000
    Posts
    184
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when logged in via phpmyadmin - we get errors saying cannot find.


  6. #6
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Do the errors say what they cannot find? I have trouble believing the error message is

    Error: cannot find
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  7. #7
    SitePoint Zealot Technics's Avatar
    Join Date
    Aug 2000
    Posts
    184
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Warning: Unable to save MySQL query result in /home/sites/path/to/left.php on line 299

    Thats the exact error. Well i modifyed the error path but thats what it results in.


  8. #8
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hmm that doesn't strike me as a result of database corruption... Anyone here familiar enough with the internals of phpMyAdmin to comment here?

    If not, you might want to post in the phpMyAdmin support forums.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


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
  •