Make WordPress Core

Opened 8 years ago

Closed 8 years ago

Last modified 4 years ago

#4780 closed defect (bug) (invalid)

$table_prefix should not be encoded in table_names.

Reported by: docwhat Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Administration Keywords:
Focuses: Cc:


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


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.




Change History (7)

comment:1 @Otto428 years ago

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.

comment:2 @matt8 years ago

  • Resolution set to invalid
  • Status changed from new to closed

comment:3 @foolswisdom8 years ago

  • Milestone 2.4 (future) deleted

comment:4 @kaldari7 years ago

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.

comment:5 @Converting2wp4 years ago

  • Cc Converting2wp added

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


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

comment:6 @nacin4 years ago

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.

comment:7 @Converting2wp4 years ago

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.

Note: See TracTickets for help on using tickets.