[wp-hackers] Why do some user meta entries use the table prefix in the meta key?

[Andrew Nacin]

On Sat, May 11, 2013 at 4:51 PM, David Anderson <david at wordshell.net> wrote:

>
> I've developed some code that imports a WordPress database, and allows a
> change of $table_prefix whilst you do so. i.e. If the site you're importing
> had a different table prefix, then you can change it as you import.
>
> The running of the SQL import itself works fine - that's just some regexes
> before executing the SQL.
>
> However, testing it out has revealed something I did not know before -
> that various entries in the usermeta table have meta_key entries that
> depend upon the table prefix. Essential example: if your prefix is wp_,
> then your user will have an entry wp_capabilities, which contains your
> capabilities.
>
> So, those also need renaming - otherwise you can't even log in (because
> the entries being looked for aren't there).
>
> Whilst researching, I came across this plugin:
> http://wordpress.org/extend/**plugins/change-table-prefix/<http://wordpress.org/extend/plugins/change-table-prefix/>
>
> It appears to know about this phenomena. It makes two adjustments:
>
> i. (prefix)_user_roles in the options table
> ii. Then all those beginning with (prefix) in the user meta table.
>
> Questions:
>
> 1. Why does WordPress do this? On the face of it, it seems like a gross
> layering violation - table keys depend on the names of tables. But the WP
> core coders are smart guys, so there must be a reason


As John indicates, users and their meta are "global". If you have a network
of WordPress sites, or are manually sharing user tables across multiple
installs, we need a way to know that the user's capabilities are for a
particular site.

To do this, we have a user options API. Unlike
get_/add_/delete_/update_user_meta() which are global for the user,
get_/delete_/update_user_option() act on the per-blog level.

The use of the prefix here is also why not using a prefix is prevented in
the installer.

 2. Are there any others that anyone knows about? Any other hidden hazards
> to changing your table prefix?


Hazards are two-fold:

 * Make sure you change all matching usermeta keys. Both core and plugins
add more than just the one you found.

 * There is an option in the options table that stores the user roles, that
is also prefixed. I believe its exact name is $wpdb->get_blog_prefix() .
'user_roles'. There is no reason why this option has a prefix; as far as I
know, it was done unintentionally back when roles were added in 2.0.

I have long thought about creating a script in wp-admin/maint/ that can
handle these "gotchas". It would be nice to fix the options table as well —
there is a ticket, http://core.trac.wordpress.org/ticket/20152.

Nacin