Why do some user meta entries use the table prefix in the meta key?

[David Anderson]

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/

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.

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

Thank you!

David

[John Blackbourn]

Post by David Anderson
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.

Yeah it's not a good convention.

The reason it's like this is to support entries which can have
different values on different sites in a Multisite network. For
example, in Multisite a user can have different privileges on
different sites within the network. Each site has its own database
prefix (wp_1_, wp_2_... wp_n_) and the user meta key is based off of
this.

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

These are the others that I know of. There may be more though.

`wp_X_user-settings` and `wp_X_user-settings-time`, used to store your
user settings on a per-site basis
`wp_X_dashboard_quick_press_last_post_id`, used to store the ID of the
most recent QuickPress post
`wp_X_user_level`, only present for backwards compatibility with the
old user level system

John

[Andrew Nacin]

Post by David Anderson
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).
http://wordpress.org/extend/**plugins/change-table-prefix/<http://wordpress.org/extend/plugins/change-table-prefix/>
i. (prefix)_user_roles in the options table
ii. Then all those beginning with (prefix) in the user meta table.
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

Post by David Anderson
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

[David Anderson]

Post by John Blackbourn
The reason it's like this is to support entries which can have
different values on different sites in a Multisite network. For
example, in Multisite a user can have different privileges on
different sites within the network. Each site has its own database
prefix (wp_1_, wp_2_... wp_n_) and the user meta key is based off of
this.

I don't get this.... as long as all the sites within the network are
still using the same database and table prefix, then the meta key prefix
is still an invariant, isn't it? i.e. It only provides something useful
if the different sites within the network are actually using a different
table prefix. Otherwise, the same effect could be achieved by just using
a fixed string instead of the $table_prefix setting. Or am I missing
something?

Whatever the reason, it occurs to me that this implies a silent
limitation on use of meta keys - that you must be careful to not use a
meta key that could match a table prefix, because of errors if/when your
site is migrated. Alternatively, it could be considered as a limitation
on migrating - that you shouldn't change your table prefix.

Making this even uglier is the fact that underscore - used in almost
100% of table prefixes - needs escaping in SQL LIKE statements. If you
search for all the usermeta entries beginning with the table prefix,
then naively issuing SQL like this:

SELECT meta_key from ${table_prefix}usermeta WHERE meta_key LIKE
'${table_prefix}%'

will get you false positives. e.g. if your table prefix is wp_ then that
SQL actually matches all keys beginning simply with just wp - which
given that it's WordPress, could be quite a lot.

Very ugly.

David

Post by John Blackbourn
--
WordShell - WordPress fast from the CLI - www.wordshell.net

[David Anderson]

Post by David Anderson

Post by John Blackbourn
The reason it's like this is to support entries which can have
different values on different sites in a Multisite network. For
example, in Multisite a user can have different privileges on
different sites within the network. Each site has its own database
prefix (wp_1_, wp_2_... wp_n_) and the user meta key is based off of
this.

I don't get this.... as long as all the sites within the network are
still using the same database and table prefix, then the meta key
prefix is still an invariant, isn't it? i.e. It only provides
something useful if the different sites within the network are
actually using a different table prefix. Otherwise, the same effect
could be achieved by just using a fixed string instead of the
$table_prefix setting. Or am I missing something?

I could have been clearer here. I understand the need for the blog ID
1_, 2_ to vary. It's the reason for including the apparently invariant
wp_ that I'm not clear on.

David

[John Blackbourn]

Post by David Anderson
I don't get this.... as long as all the sites within the network are
still using the same database and table prefix, then the meta key
prefix is still an invariant, isn't it? i.e. It only provides
something useful if the different sites within the network are
actually using a different table prefix. Otherwise, the same effect
could be achieved by just using a fixed string instead of the
$table_prefix setting. Or am I missing something?

I could have been clearer here. I understand the need for the blog ID 1_, 2_
to vary. It's the reason for including the apparently invariant wp_ that I'm
not clear on.

The database prefix with the blog ID appended acts as a UUID within
the given database. This allows you to share your users and usermeta
tables between WordPress installs in the same database, because each
has a different database prefix.

Example: I have two Multisite installs which share a users and
usermeta table. The resulting usermeta keys would be, for example,
"foo_{n}_" for one site and "bar_{n}_" for the other. If the database
prefix wasn't prepended there would be key collisions.

John