#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    12
    Rep Power
    0

    tables get corrupted


    Not really sure if this is a Mysql or PHP question

    Background info:
    Mysql 4.0.15a RH7.2 PHP 4

    I've started using a second set of tables as my update tables during a batch process to upload content that is done daily. Once updated I rename them and make them the active tables. This way I'm sure no one is using the tables during the content uploading.

    I've run several short batch processes and after every completion I check the tables using myisamchk. Each batch process adds bet
    100K to 200K rows to the tables.

    Every myisamchk -a tells me that 1 client is using or hasn't closed the table properly and suggests to repair the table. Only my upload script opens the tables during the batch process. No other script uses the tables. The upload script ends normally by the fact that my die statement "Process Complete" outputs to screen.

    My question is why does myisamchk keep telling me that the table needs to be repaired?

    Ok so I perform the repair using the myisamchk -r option. All seems well until I tried to optimize the table using phpmyadmin. phpmyadmin tells me that the table is bad, records pointing outside the table, etc. I go back to myisamchk -r once more then and only then can phpmyadmin optimize the table. This is really weird.

    Using mysqladmin proc stat command shows that there are no other mysql processes.

    I researched about closing tables and all I got is that once the script terminates the database connection is closed. I would assume that the tables are properly closed before that happens.

    Has anyone experienced this with Mysql? Mysql bug Or is there a PHP command that I have to issue to properly close the tables?

    I'm using INSERT and UPDATE in the upload script. INSERT if no record is found. UPDATE if a record is found. I don't see the need to lock the tables since no one else uses these tables.

    Thanks in advance for your help.

    Cheers
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    12
    Rep Power
    0
    Update:

    I tried to reverse the process. I optimize the tables (using phpmyadmin) after the upload script ends and then perform a myisamchk.

    Suprisingly the tables optimize and mysiamchk doesn't show any error.

    I perform the optimization and myisamchk every time the upload script ends because I noticed that if I process a larger batch (right now about 500 files at a time) the process slows down.

    I expect some slow down as the tables get larger (i'm using indexes) but not to a crawl like I experienced previously. I suspect that some time during the processing of large number of files the tables get corrupted somehow.

    At cetrain intervals I echo upload status to screen to see the progress of the script. The intervals are set so as not to overwhelm the buffer of the browser or max out the RAM of the pc initiating the script.

    cheers.

IMN logo majestic logo threadwatch logo seochat tools logo