Pi in the Sky

April 2007
M	T	W	T	F	S	S
		May »
	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

Changing table prefix for WordPress

Posted on April 17th, 2007 at 6:58am by Pi.
Categories: WordPress.

After I discovered that my host didn’t give me unlimited databases, I thought that I should optimize the database usage. So I went and changed the WordPress table prefix, thinking of future expansions of the database I use for WordPress (maybe more blogs in the future?) But this gave me some problems which took me a little bit to overcome.

Changing the table prefix was not so simple, taking into account how WordPress manages user information. The steps needed to do so are actually dead easy. Step 1 is to change the table prefix in wp-config.php. Let’s say we still have the default prefix wp_. So:
$table_prefix = 'wp_';
becomes
$table_prefix = 'wp_new_';

Step 2 consists in changing manually the table names in your database. Use your favorite tool for this: MySQL Query Browser, phpMyAdmin, or run a query thru some PHP. The problem comes now, as you can browse the site properly (since both the prefix in wp-config.php and the table names in the database match) but you can’t admin anything. As soon as you enter the /admin/ domain in your WordPress blog, a message is displayed: You do not have sufficient permissions to access this page.

I looked in Google, of course, but couldn’t find much more than people having my same problem. I experimented and tried with many things, thinking that maybe WordPress had some kind of validation code so you couldn’t change tables that easily (although I wouldn’t understand why).

Finally I stumbled upon what seems to be a bug report for WordPress: Changing table prefix in wp-config doesn’t update database. It was not really a proper bug report, but it gave me some clues. After seeing that the report was dismissed as they think WordPress doesn’t need this functionality, I started following the advices, discovering that they were incomplete. So here it is the third step: manually change some option names.

The details: some user data is saved in options which have the table prefix as part as the option name. These options are:

Table xxxoptions

Option xxxuser_roles

Table xxxusermeta

Option xxxcapabilities
Option xxxuser_level
Option xxxautosave_draft_ids

The last option doesn’t exist unless you have saved drafts of posts while editing them, and it’s the part not mentioned in the original bug report. So, you just need to change the option names, so the old table prefix is substituted with the new table prefix. So wp_user_roles becomes wp_new_user_roles.

I can envision a plugin for WordPress which is able to change all the table prefixes and option names for you, in case you need to change the table prefix of your existing WordPress blog. However, I’m not going to code one, although it should be dead easy…

Said all this, I still don’t understand why WordPress uses the table prefix for user information, it can’t be to not share user information across blogs, because if you change the table prefix, you can’t access anything else anyway… Well, in any case, I hope this little information is of help for you.

14 comments.

Insecurely secure »« Start

Neerav

Comment on 2:59am.

Thanks a million for your instructions, once I was prepared i changed my WP table prefix in 3 minutes start to finish.

Quickly Change the WordPress MySQL Database Table Prefix < Rambling Thoughts Blog - Neerav Bhatt

Pingback on 3:29am.

[…] It took a while but I eventually found instructions at the Pi in the Sky Blog. […]

admin

Comment on 2:39pm.

It’s don’t works for WordPress 2.2.1: in this version you need also copy string from field ‘xx_user_roles’ from “clean” installation of WP (with needed prefix) to your working installation of WP.

Pi

Comment on 3:24pm.

I have not tried this method with 2.2.1 yet, when I update, I’ll check and update this information too.

Edit: you seem to have misunderstood the method I describe. I explain that you need to change the prefix of some option names too. You don’t need to copy xx_user_roles from a clean WP installation, you can keep your current user roles just changing the prefix of the already existing option. I’m sure that it will be the same in 2.2.1.

yongfook

Comment on 3:40am.

thanks for this tip! Just altered my prefix and couldn’t log in - this saved me from pulling out some hair

Philipp

Comment on 3:40pm.

Hi Pi,
I developed for BlogSecurity.org a plugin which does exactly what you covered with your post here. I got the post after some researches( after it initially didn’t work). And you saved me some time as I had normally would have to check all values within wp_tables for it. Anyway I would like to mention that it would be better to change xx_user_roles….and so on to xxuser_roles as, as well the _ can be a prefix for the table and isn’t needed( Or Am I on the wrong track?). And can you maybe tell me when the xx_user_level record is generated, as I couldn’t find it while working on the Plugin, is it maybe only created if your blog accepts User signups or multiple authors?

Pi

Comment on 8:36pm.

You’re not in the wrong track; the underscore usage slipped accidentally. I’ve edited the article to reflect that the underscore is not part of the option names, but of the prefix.

I thought of making a plugin, but honestly I am too lazy ^_^

Related to xx_user_level, I am not sure. I’d say it’s set when an user is first edited (as opposed to created). Look into ‘wp-includes/user.php’ to see some usage.

The Doctor What

Comment on 5:09pm.

I have added a bug to remove the column names using the $table_prefix.

http://trac.wordpress.org/ticket/4780

Pi

Comment on 8:56am.

That’s a sane decision, it’s a change that was needed for a long time.

jameswillisisthebest

Comment on 10:29pm.

This is my first post
just saying HI

juan

Comment on 8:00pm.

sencillamente genial, muchas gracias por una exposición tan sencilla y clara de un problema que nos ha dado muchos quebraderos de cabeza.

Wodpress-prefix, marknadsföringslänkar, tidningsprenumeration och ny jaktblogg — Jonny Elofsson

Pingback on 2:34pm.

[…] Har lagt ned lite tid nu på att förstå varför det ska vara så klurigt att ändra prefix på databastabellerna för en befintlig Wordpress-installation. Att ändra prefixet i tabellnamnet och i inställningsfilen wp-config.php skulle räcka trodde jag, men icke. Det fungerar för att siten ska se bra ut men det går inte att logga in som administratör. För detta måste man även ändra några inställningar i tabellerna [ev. prefix]_options och [ev.prefix]_usermeta. Läs vad som behöver ändras hos Pi in the Sky. […]

ND B

Comment on 11:15am.

Thanks! You saved me a lot of work!

Paulino Michelazzo

Comment on 9:52pm.

Great! Just a little detail to make me crazy. Thanks a lot for your info. Save my day

Best regards from Brazil

Comments can contain some xhtml. Names and emails are required (emails aren't displayed), url's are optional.

Insecurely secure »« Start

Un buen plan hoy es mejor que un plan perfecto mañana. — George S. Patton

Pi in the Sky is powered by WordPress. Dressed with Vistered Little. Hosted at MochaHost.

Post Information

Pages

Categories

Blogroll

Site Meta