Corrupt tables - Whats the best approach to fix this?

My databases have been giving me problems for quite sometime, and now it's become crucial to fix.

I have corrupt tables that keep on crashing my server.

I've been told that the schema is old and the original old wp database was used for an wpmu install.

Anyway, many things wrong and a few people from here have had a look at it.

Those that have tried to fix it have only done so temporarily and no one else is very keen to jump in and dissect it.

Want to know what my options are:

I have multi db with 16 db's, is there a way that I can manually combine all the tables into a new db?

If so, how would I go about that?

Would that fix the corrupt tables?

What if I want to rescale to 256 db's?

I'm thinking a clean slate with new db's are the way to go, but don't want to lose any data.

What's the best thing to do with the least amount of effort that will be more attractive for programmers to take this on and fix it?

Thanks!

George

  • Barry

    @georgef - are there any specific tables that you keep finding corrupting?
    I used to have a lot of issues with the options table until I made a quick change to WP and it (touch wood) hasn't happened again since.

    is there a way that I can manually combine all the tables into a new db

    It will take a few minutes, but basically:
    1. back up all the databases to sql files
    2. create a new db
    3. run all of the backed up sql files on the new db.
    4. set wp-config.php to point to the new db
    5. remove the multi-db db.php file

    What if I want to rescale to 256 db's?

    Once you are in a single db - you could set up the new 256 db's and then use the move script to redistribute them back out to the new 256

  • georgef

    @georgef - are there any specific tables that you keep finding corrupting?

    I can ask the host. In emergency when the site goes down they always reply that they found a corrupt table and its fixed. I'll check with them.

    I used to have a lot of issues with the options table until I made a quick change to WP and it (touch wood) hasn't happened again since.

    Ivan mentioned in this thread that wp_posts is crippled.

    I also had Mike (@venturemaker) remove the wp_postarchive table that was 3.3gb.

    Come to think of it, he also tried to combine all the tables back to one db and we ran into a few problems.

    The hard drive may be failing. Have you contacted your host about it? I know you said "many people..." but I don;t see any mention of host.

    Mike also mentioned that. Although my host always mentions that there is major overload on my db.

    This message is from them last month...

    The site is up and working now.

    Following are the log message and that is making load

    22363 mysql 24 0 122m 28m 4224 S 178.4 0.5 16:43.03 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.THATM
    24817 thatmlmb 16 0 71824 55m 6860 S 61.5 0.9 0:03.86 /usr/bin/php
    24816 thatmlmb 15 0 65104 50m 6720 R 48.6 0.9 0:02.27 /usr/bin/php
    24819 thatmlmb 16 0 63816 49m 6708 R 47.6 0.8 0:02.20 /usr/bin/php
    24825 thatmlmb 17 0 60680 46m 6536 R 28.4 0.8 0:01.62 /usr/bin/php
    24644 thatmlmb 16 0 69980 53m 6824 R 18.8 0.9 0:03.15 /usr/bin/php
    24740 thatmlmb 16 0 0 0 0 Z 13.2 0.0 0:03.36 [php] <defunct>
    24753 thatmlmb 16 0 68876 52m 6800 R 12.9 0.9 0:02.62 /usr/bin/php
    24754 thatmlmb 16 0 69720 53m 6784 R 8.9 0.9 0:03.02 /usr/bin/php
    24485 thatmlmb 17 0 0 0 0 Z 6.9 0.0 0:03.27 [php] <defunct>
    24718 thatmlmb 16 0 41104 26m 6236 S 5.0 0.5 0:00.80 /usr/bin/php
    24818 thatmlmb 16 0 26540 12m 6032 R 3.3 0.2 0:00.19 /usr/bin/php
    24788 thatmlmb 15 0 39000 24m 6232 S 3.0 0.4 0:00.73 /usr/bin/php
    24755 thatmlmb 18 0 37632 23m 6216 R 0.7 0.4 0:00.64 /usr/bin/php
    24787 thatmlmb 21 0 27548 13m 6172 R 0.7 0.2 0:00.21 /usr/bin/php

    and This...

    The data bases thatmlmb_wpmuglob and thatmlmb_wpmuf was causing load on the server. Which made the server to respond slow. Please check these data base and tweak the settings accordingly with the help of a developer so that it uses less resources.

    Here we copy the log from the server.

    =========================================
    mysqld 21149 mysql 96u REG 253,0 1840 3802268 /var/lib/mysql/thatmlmb_wpmuc/wp_1_myrp_categories.MYD
    mysqld 21149 mysql 97u REG 253,0 11264 2430399 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_terms.MYI
    mysqld 21149 mysql 99u REG 253,0 1984 2430373 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_terms.MYD
    mysqld 21149 mysql 100u REG 253,0 11564704 3802322 /var/lib/mysql/thatmlmb_wpmuc/wp_1_posts.MYD
    mysqld 21149 mysql 101u REG 253,0 3304 2654357 /var/lib/mysql/thatmlmb_wpmuglob/wp_mass_mailer.MYD
    mysqld 21149 mysql 102u REG 253,0 273120 3802274 /var/lib/mysql/thatmlmb_wpmuc/wp_1_myrp_ratings.MYD
    mysqld 21149 mysql 103u REG 253,0 140184 2654461 /var/lib/mysql/thatmlmb_wpmuglob/wp_sitemeta.MYD
    mysqld 21149 mysql 104u REG 253,0 52948 3802388 /var/lib/mysql/thatmlmb_wpmuc/wp_1_term_taxonomy.MYD
    mysqld 21149 mysql 105u REG 253,0 4914 2430367 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_term_relationships.MYD
    mysqld 21149 mysql 106u REG 253,0 11264 2430381 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_options.MYI
    mysqld 21149 mysql 107u unix 0xe81c4a80 7219110 /var/lib/mysql/mysql.sock
    mysqld 21149 mysql 108u REG 253,0 40960 2654305 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_groups_groupmeta.MYI
    mysqld 21149 mysql 109u REG 253,0 66800 2654279 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_groups_groupmeta.MYD
    mysqld 21149 mysql 110u REG 253,0 66800 2654279 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_groups_groupmeta.MYD
    mysqld 21149 mysql 111u REG 253,0 15632 2654288 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_groups.MYD
    mysqld 21149 mysql 113u REG 253,0 14336 2430384 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_postmeta.MYI
    mysqld 21149 mysql 114u REG 253,0 8120 2430358 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_postmeta.MYD
    mysqld 21149 mysql 116u REG 253,0 176648 2430352 /var/lib/mysql/thatmlmb_wpmu5/wp_1954_options.MYD
    mysqld 21149 mysql 117u REG 253,0 140184 2654461 /var/lib/mysql/thatmlmb_wpmuglob/wp_sitemeta.MYD
    mysqld 21149 mysql 118u unix 0xf6035880 7218460 /var/lib/mysql/mysql.sock
    mysqld 21149 mysql 119u REG 253,0 11564704 3802322 /var/lib/mysql/thatmlmb_wpmuc/wp_1_posts.MYD
    mysqld 21149 mysql 121u REG 253,0 66104 3802391 /var/lib/mysql/thatmlmb_wpmuc/wp_1_terms.MYD
    mysqld 21149 mysql 122u REG 253,0 11564704 3802322 /var/lib/mysql/thatmlmb_wpmuc/wp_1_posts.MYD
    mysqld 21149 mysql 123u REG 253,0 1640140 3802313 /var/lib/mysql/thatmlmb_wpmuc/wp_1_options.MYD
    mysqld 21149 mysql 124u REG 253,0 3072 3706063 /var/lib/mysql/thatmlmb_mlmads/oiopub_config.MYI
    mysqld 21149 mysql 125u REG 253,0 10660 3704817 /var/lib/mysql/thatmlmb_mlmads/oiopub_config.MYD
    mysqld 21149 mysql 126u REG 253,0 1024 2654338 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_messages_notices.MYI
    mysqld 21149 mysql 127u REG 253,0 0 2654309 /var/lib/mysql/thatmlmb_wpmuglob/wp_bp_messages_notices.MYD
    mysqld 21149 mysql 130u REG 253,0 66104 3802391 /var/lib/mysql/thatmlmb_wpmuc/wp_1_terms.MYD
    mysqld 21149 mysql 138u REG 253,0 326864 2654491 /var/lib/mysql/thatmlmb_wpmuglob/wp_users.MYD
    mysqld 21149 mysql 140u REG 253,0 140184 2654461 /var/lib/mysql/thatmlmb_wpmuglob/wp_sitemeta.MYD
    root@server [/home/thatmlmb/public_html]# w
    09:30:45 up 4 days, 3:30, 1 user, load average: 16.96, 22.39, 16.71

  • georgef

    Just to keep this all under the same thread, I've seen a few posts on fixing Corrupt tables using myisamchk:

    http://www.thegeekstuff.com/2008/09/how-to-repair-corrupted-mysql-tables-using-myisamchk/

    http://www.ghacks.net/2010/02/04/check-and-repair-corrupted-tables-on-mysql-database/

    Would this work for a do it yourself approach?

    Doesn't look like I'm going to find anyone real soon that can assist with this and not sure if I trust (after a few bad incidents) anyone from odesk, elance, etc.

  • drmike

    . In emergency when the site goes down they always reply that they found a corrupt table and its fixed.

    In that case, it;s probably an index problem for that table. A repair from phpmyadmin should fix that issue is that's what the problem is.

    I also had Mike (@venturemaker) remove the wp_postarchive table that was 3.3gb.

    I;m lost. Where's wp_postarchive coming from? What plugin? 3.3gigs is huge though. The (ah heck, i forget the name of it) index of the hard drive and how it finds what sectors files are in (whatever it;s called. And I had coffee this morning even.) is probably failing.

    Although my host always mentions that there is major overload on my db.

    Doubt it;s causing the issue but running optimize once in a while may help. Especially if that 3.3gig you quote up there is full of wasted space.

  • georgef

    The hard drive may be failing. Have you contacted your host about it? I know you said "many people..." but I don;t see any mention of host.

    Just had them do a hardware test and came back with no errors.

    In that case, it;s probably an index problem for that table. A repair from phpmyadmin should fix that issue is that's what the problem is.

    OK, would i use something like myisamchk mentioned above? (not sure if that can be done in phpmyadmin)

    I;m lost. Where's wp_postarchive coming from? What plugin? 3.3gigs is huge though

    No idea how it got there, but we deleted it so its gone now.

    Doubt it;s causing the issue but running optimize once in a while may help

    Apologies for all the Q's as I'm new to db stuff, how would I go ahead with optimization?

    Thanks!

    George

  • georgef

    OK, a simple Google search got me this...

    To perform the optimization, log in to your phpMyAdmin and select the database whose tables you wish to optimize.

    A list with all the database's tables will appear. Tick the tables you wish to optimize, or simply click [Check All] to select all tables.

    From the [With selected:] drop-down menu choose Optimize table. This will execute the OPTIMIZE TABLE SQL query on the selected tables and they will be updated and optimized.

  • drmike

    FAT index. Knew I would remember it after awhile.

    Just had them do a hardware test and came back with no errors.

    That's good. Gotta admit though that I've deal with techs in the past who a hardware test is "Yup, that's hardware." I'm going to assume that you trust these folks.

    Where's wp_postarchive coming from?

    Google doesn;t pull anything up for it so I;m still at a loss. The file size would cause issues though. I know FAT32, it;s 4 gigs. But that's with a perfect system. Like the number of files you can have within a directory. The limit is 32k but after 12-15k, things start to get affected.

    how would I go ahead with optimization?

    That would actually be a host question as I;m not aware of what you;re running for software. You should have a program called phpmyadmin as you mention up there. You can do it within that program. Dealing with the corruption issue is a higher priority though.

    Here's some pretty pics though as to how to do it. Please see if you understand it: (edit: No offense intended by the way how I worded that.)

    http://www.siteground.com/tutorials/phpmyadmin/phpmyadmin_optimize_database.htm

    Optimizing gets rid of all the left over space from deleted information and it;s supposed to double check the index for the database.

    edit: I don;t know what level of access you have to this box but mysql does have an error logs, much like the webserver does. Should be something like /logs/mysql/error.log. Something under the /logs/ or /log/ root directory and with mysql in the name.

  • georgef

    Hi MIke, that's just awesome help thanks a lot!

    Here's some pretty pics though as to how to do it. Please see if you understand it: (edit: No offense intended by the way how I worded that.)

    All good, pretty pics do help, I'm learning here! :slight_smile:

    Thanks for all the help.

    I found a great Database Administrator that's going to check things out for me. Not familiar with wpmu, but sure that's fine.

    Or anything I should warn him about other than multi-db which I've done?

  • drmike

    I raised this question on another forum. I;ve been asked a few questions about the type of database you're running:

    http://www.lowendtalk.com/topic/mysql-corruption-issues

    Sounds like it's the same table all the time, right?

    Not sure if you;re running Innodb or MyISAM. I forget what CPanel defaults to. (We're a Direct Admin house.)

    As to the amount of writing, do you have any sort of caching in place? If so, which one is it?

    Multidb shouldn;t be an issue but I would mention it to him as well as the location of the alt config files. I;m sure (hopefully) that he's aware of how wordpress works but I doubt he or she knows how mutiltdb handles things.

  • georgef

    As to the amount of writing, do you have any sort of caching in place? If so, which one is it?

    I had WP Super Cache installed, but deactivated it and had someone try to configure W3 Total Cache, but that didnt work due to a clash with the multi-db plugin as discussed here in a thread somewhere.

    I need to reconfigure WP Super Cache again, but at this time, I have no caching in place.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.