What database structure is this?

  • Resolved 2bearstudio

    (@2bearstudio)


    11 years, 6 months ago

    Recently take over a maintenance work on a new WordPress website. The database is unusually large. Observing in phpMyAdmin, found that, the database has 499 tables. Almost every table has multiple duplications, just with different suffix.

    For example:
    For wp_users table, it has one table called:

    wp_users

    then followed by another 15 wp_users table list like this:
    [+] wp_users (15)

    Click on “+”, the 15 table names look like this:

    wp_users_20121214_020503
    wp_users_20121214_020503__20130917_0__20141109_014715__1
    ……
    wp_users_20141109_014715

    Any idea what is this, and how could this happen, and how did it happen? Can the table with (date) suffix be safely removed without taking down the site?

Viewing 5 replies - 1 through 5 (of 5 total)
  • chrishajer

    (@chrishajer)

    11 years, 6 months ago

    Those appear to be backup tables of some sort. Take a look in the site’s wp-config.php for the $table_prefix. It should look like this:

    $table_prefix = ‘wp_’;

    If so, and there are no other WordPress sites using the same database, then it’s safe to delete those tables. I would make a complete database backup before you delete anything, just in case they’re needed for something we did not consider.

    You’ll have to figure out what is creating those tables. Maybe there is a plugin installed or a backup job running on the server?

    Thread Starter 2bearstudio

    (@2bearstudio)

    11 years, 6 months ago

    Thank you, @chrishaje.

    $table_prefix = ‘wp_’;

    there is no doubt at this. The question is, all those redundant tables have the same prefix. I am pretty sure those redundant tables are useless to the active website (the only site in this hosting account).

    I just don’t know how were they created and why, for what purpose.

    Thread Starter 2bearstudio

    (@2bearstudio)

    11 years, 6 months ago

    (Sorry, didn’t mean to click the post button so soon)

    In terms of the backup job. The website employed “WordPress backup to Dropbox” to do backup. But the plugin can’t handle the size of the site, and never actually backup anything. I removed it and replaced it with another one that works.

    I don’t have the plugin installed on the site, and can’t find out if the weird database structure is caused by the plugin.

    Gadgetroid

    (@gadgetrophy)

    11 years, 6 months ago

    From experience it looks like some security feature of a plugin doing its thing and renaming the database tables. I had to rebuild the site when that happened. What you want to find out is if there is a difference in those tables. If its well written you will find that the original wp- tables or the one with the latest date wp_users_20121214_020503 -….wp_users_20141109_014715 will have the most upto date data. Then its easy coz you can delete everything else and go with the latest data. If there is differences you may need to spend some time in the backend merging that data into one again. After all these is done check how whatever that is causing it is configured. That kind of setting looks like for a very large organisation who have people in the backend full time. Have fun 🙂

    Thread Starter 2bearstudio

    (@2bearstudio)

    11 years, 6 months ago

    Thank you, @gadgetroid

    The tables with the date suffix are older version of that specific table, and not currently used. By comparing the size and content, I confirm my conclusion, and cleaned up the database.

    The website was maintained by a non technical person, who has not enough knowledge to manage WordPress, and doesn’t know what the backup is doing. She still think the backup was setup, but don’t know the backup didn’t actually happen.

    I still have no clue where the redundant copies come up. But glad to have it cleaned up without effect the website. Of course, backup is the first step.

    Thank you for the input.

Viewing 5 replies - 1 through 5 (of 5 total)

The topic ‘What database structure is this?’ is closed to new replies.