The Support Forums will be in read-only mode for a scheduled maintenance window on 01 September 2016 14:00 UTC - 20:00 UTC. More information.

Changing Table Prefix - Current state of the art? (12 posts)

  1. converting2wp
    Posted 5 years ago #

    Okay, I've got a version of 3.1.3 that I want to clone to get a development environment. [Note the system has been running for a long time. This is a new requirement.]

    From articles like "Hardening WordPress" and "Editing wp-config.php", it looked as if I could start that process by
    1. Backup the database

    2. change the $table_prefix in the database (with a SQL script in phpMyadmin -- e.g. "rename table wp_posts to dev_posts", etc.

    3. In a new file-system installation, change the $table_prefix in the wp-config.php file from "wp_" to "dev_"

    4. Import the database back (with the original prefix) (to get the original system running again).

    However, I'm running into a problem reported before that trying to login as an administrator (and perhaps others) in the new system fails - with the messsage, "You do not have sufficient permissions to access this page."
    Support post from 3 years ago:
    has this script-

    UPDATE new_usermeta
    SET meta_key = REPLACE(meta_key,'old_','new_');

    UPDATE new_options
    SET option_name = REPLACE(option_name,'old_','new_');
    Support post from 4 years ago
    has a link to
    which enumerates the options to change:
    Table xxxoptions
    Option xxxuser_roles
    Table xxxusermeta
    Option xxxcapabilities
    Option xxxuser_level
    Option xxxautosave_draft_ids

    And these are the options listed in Trac from 4 years ago (and closed as invalid though some folks disagree):

    The script from 3 years ago doesn't look hard to implement, but I thought I'd check here to see if there's any downside (someone using "wp_" as a prefix string that is *not* related to the $table_prefix value, for instance.)

    The only ones I found though:
    xxxusermeta: wp_dashboard_quick_press_last_post_id
    xxxoptions: wp_optimize

    Anyone able to say if both of those should have the name changed to match the new prefix? Anything else?

    [The only other option I've seen is to "export blog from WP, create new installation with different table prefix, import the export file" -- but I've had terrible luck with importing things like users and links so I'm reluctant to go that route.]

    [And if anyone has link to how to use this "feature" of multiple blogs sharing these tables, please do post a link.]

  2. esmi
    Forum Moderator
    Posted 5 years ago #

  3. converting2wp
    Posted 5 years ago #

    Okay, I lied trying to simplify things -- but I don't see any mention of "table prefix" in that article on Moving WordPress.

    [I actually moved the site from an installation on another host -- so steps (1) and (4) were ginormous oversimplification of how things were done.]

    This article is about a problem is with changing the table_prefix *after* the installation. It's a problem that's been around for years, and I don't see any plans to fix it so I'm just checking to see if earlier solutions are still valid.

  4. esmi
    Forum Moderator
    Posted 5 years ago #

    Why would you want to change the table prefix after installation?

  5. converting2wp
    Posted 5 years ago #

    Because I want to support 3-4 different versions of the site from one database. And I want to install a "site" by moving a running system into place.

    On the other hand, there's a recommendation in the Hardening WordPress article that it's good to change the table_prefix -- and that's not apt to be the *first* article someone new to WP reads, so there *should* be a way to do that on a running system in a reliable way.

  6. converting2wp
    Posted 5 years ago #

    I did a little further investigating on an install where I *did* choose the table prefix when I installed things.

    Of the items noted above, the xxx_options table *does* have an entry with option_name equal "wp_optimize" (not xxx_optimize). But the xxx_usermeta table had an entry with meta_key equal to xxx_dashboard_quick_press_last_post_id

  7. esmi
    Forum Moderator
    Posted 5 years ago #

    You would need to change the table prefixes in the database using something like Phpmyadmin but remember to backup your database first. Then you'd need to amend the table prefix in wp-config.php

  8. converting2wp
    Posted 5 years ago #

    Yes. I did both of that. But there are really 3 steps, not two:

    1. Change the table prefixes
    2. Change $table_prefix in wp-config.php
    3. Change *entries* in the tableprefix_usermeta and tableprefix_options tables where the $table_prefix is embedded in the data, not just the name of the table.

    The Trac entry lists some of the fields where #3 is required. I'm asking if there are others that have been added since then (I can look at the tables and guess, but I'd prefer an answer from someone who knows the code).

  9. converting2wp
    Posted 5 years ago #

    Anyone? Or do I have to go back to one database per install?

  10. Kiopa_Matt
    Posted 5 years ago #

    Finally, after hours upon hours of screwing around, got it figured out! And sure enough, it's an easy solution.

    Check your new database tables. For example, I bet the 'ID' column of 'dev_posts' table isn't set to AUTO_INCREMENT. You copied the database structure wrong, or at least that was my problem.

    When copying the tables over, use SQL statements like:

    CREATE TABLE $new_table_name LIKE $table_name;
    INSERT INTO $new_table_name SELECT * FROM $table_name

    That will copy over the entire table structure including keys, plus all data. From there, it should work fine, or at least it did for me.

    Hope that helps!

  11. converting2wp
    Posted 5 years ago #


    The rename script I used was

    wp_commentmeta TO s_KifH_commentmeta,
    wp_comments TO s_KifH_comments,

    and while I'm no SQL expert, I would have expected that to copy keys and such as well as your CREATE/INSERT.

    The problem seems to be that after the rename, there is, for example, a row in the s_KifH_options table that has
    option_name = wp_user_roles
    that needs to be changed to
    option_name = s_KifH_user_roles

    It's not that much work, but it seems like changing all such "wp_*" entries isn't quite right. For instance, I think there's a

    option_name = wp_optimize

    that should be left alone.

    Since I'm not getting any definitive answers, and I don't want to (am not qualified to) read through the code, for now I'm just leaving the prefix at "wp_" and creating a new database for each install.

    Glad it worked for you, though!

  12. converting2wp
    Posted 5 years ago #

    I've updated the Trac ticket with some additional info:

    But the bottom line is that the only database entry that caused confusion above was XXX_options.wp_optimize (instead of XXX_options.XXX_optimize). On closer inspection the very existence of the wp_optimize option was a symptom my install had been hacked.

    See http://smackdown.blogsblogsblogs.com/2010/06/14/rackspace-hacked-clients-check-your-databases-wordpress-wp_optimize-backdoor-in-wp_options-table/

Topic Closed

This topic has been closed to new replies.

About this Topic