$table_prefix should not be encoded in table_names.

[docwhat]

Currently, the following columns have the $table_prefix within their name:

table_prefix_options.table_prefix_user_roles
table_prefix_usermeta.table_prefix_capabilities
table_prefix_usermeta.table_prefix_user_level
table_prefix_usermeta.table_prefix_autosave_draft_ids

Where 'table_prefix_' is the $table_prefx.

Using the $table_prefix this way causes needless headaches(1) and
makes the database schema more brittle than it needs to be.

Making this change will allow changing the $table_prefix to be trivial.

Ciao!

(1)

​http://www.eligeotravez.net/2007/04/17/changing-table-prefix-for-wordpress/
​http://markjaquith.wordpress.com/2006/03/28/wordpress-error-you-do-not-have-sufficient-permissions-to-access-this-page/

[Otto42]

No, I believe these are necessary in case you have two or more independent blogs sharing one users table with the use of the CUSTOM_USER_TABLE and CUSTOM_USER_META_TABLE defines. While the user_roles in the options table may not need the table prefix, I think that you would not necessarily want a shared userbase to inherit capabilities and such across the usermeta table. Also, the draft ID's would break if it was shared across two tables.

Suggest marking this as invalid. It's plugin territory. Also, you should not really be changing the $table_prefix to begin with, there's virtually no point in doing so.

[kaldari]

Arg, is it really worth creating a dependency between the data and the table structure just some someone can theoretically share a user table between several blogs but keep the permissions discrete (why would someone want to do that?). This is a big headache for lots of WordPress users. I finally found this bug after tracking down the identical issue as exemplified in the links above in my blog. The way it works now seems like a bad idea to me. Just my two cents.

[Converting2wp]

I ran into this problem recently [1]. I'm migrating an old site (that had "wp_" as the table prefix) to a new host and wanted to run a dev site as well as production off one database in the new environment. I wanted to import the old database, change its prefix to "dev_xxx_" and eventually build out a new set of tables with "prod_xxx_" as their prefix. (That's my answer to the question above, "Why would someone want to change a table prefix?", if an answer other than "implementing the recommended obfuscation sometime after install" is not sufficient.)

Anyway, I found the following values

table_prefix_usermeta.wp_dashboard_quick_press_last_post_id,
table_prefix_options.wp_optimize

that aren't in the above list. It appears that the second needs to have the column value changed, but perhaps the second doesn't? Are there other dependencies on the table prefix that would need to be changed along with the names of the columns?

[I'll probably just take the path of creating new databases and using the default prefix.]

[1] ​http://wordpress.org/support/topic/changing-table-prefix-current-state-of-the-art?replies=9

[nacin]

That's a disaster waiting to happen. If you actually want to run production and development running off two databases, you're going to want to actually run them off two databases. Otherwise database-wide operations like SHOW TABLES or repair maintenance might affect both installs.

WordPress keys numerous things off the table prefix that it then stores in usermeta or options. You found one -- dashboard_quick_press_last_post_id, which uses the user options API to do so. There's also important ones like {$prefix}capabilities in the options table.

[Converting2wp]

For my attempts to put two installs into one database, I was just using the info at ​http://codex.wordpress.org/Installing_Multiple_Blogs#Single_Database [Of course, with 3.0+, a network of sites may make more sense for "related blogs" but not, of course, for dev/stage/test.]

Yes, the XXX_options.XXX_user_roles and XXX_user_meta.XXX_capabilities entries were mentioned in the original post.

But I'm making an update here because the "wp_optimize" that I mentioned above is, I think, a red herring. It turns out that was a symptom of a hacked install, as described in ​http://smackdown.blogsblogsblogs.com/2010/06/14/rackspace-hacked-clients-check-your-databases-wordpress-wp_optimize-backdoor-in-wp_options-table/

So my current hypothesis, assuming there is a reason to change the table prefix of a running system, the way to do it is

1. Change all the table names (of course)
2. Search the options table for entries with "option_name" starting with the old table prefix and change those entries to match the new prefix
3. Search the user_meta table for entries with "meta_key" starting with the old table prefix and change those entries to match the new prefix

I expect that's what I'll be doing even if I do separate the installs in separate databases.

Thanks for listening.