Changing database prefix from wp_ to something else

you’d need to change it in your config.php as well as renaming all the tables via PHPMyAdmin or similar.

all i need to do is change the table name? nothing else? no column names or anything?

and i would take these two steps simultaneously?

also, on a scale of 1 to 10, how important would you say it is to do this???

If it was that important I think WordPress would generate a random prefix.

But yes it is just table names, don’t touch column names!

@ Rich ‘elfin’ Pedley –

In my experience (limited so far), that isn’t true. You also have to change internal references within the table data. There are several sites which outline instructions, and list several modifications you have to make, otherwise you’ll get page telling you you can’t even log into your site.

However, I’ve found that these instructions don’t seem to catch all the occurrences anymore in WP 3.0.1. Some plugins also seem to store references to wp_ names (WassUp for example).

I’ve read on some sites to export the DBs, search and replace all ‘wp_’ with ‘yournewprefix_’, and then, drop the tables (not the db), then re-import the data. I have not tried this, but in doing a search, I come up with like 70+ instances of wp_, and some don’t look like they might be references, so I’m not sure about that bit of advice either (and this is after I hand fixed everything I could see, and have my site, apparently, running OK again).

So, I’m a bit confused at the moment, and hoping I can find some more answers, but I know it isn’t as simple as just changing the table names.

BTW, I used the WP Security Scan plugin to do the initial change, which did some of this for me, but didn’t completely work.

if plugins are referencing wp_ then the author needs to be contacted and asked to change it.

Good to know, I’ll send them an e-mail. I think it read the wp-config when it (the plugin) initially created tables, but then after I changed table names, it created a new set of tables, still named wp_ and started over recording data. Fixing the name it had in one of the wp_options table pointed it at the original tables (now renamed).

As for the remaining wp_ instances, I’m down to just a few. A few of them are maybe variables, and not meant to be changed (ex: there is a ‘wp_me’ under table: wp_options, col: option_name data: stats_options). The other few might need to be changed, but I guess I’ll wait till I run into some kind of issue or run across more info.

For example:
table: wp_options col: option_name data: wp_db_backup_excs

table: wp_options col: option_name=cron option_value: several wp_ variables in here

table: wp_postmeta col: meta_key data: _wp_page_template (a few of them)

table: wp_options col: option_name=sidebar_widgets option_value: wp_inactive_widgets in it)

There are a bunch of entries in table wp_options where option_name is:
_transient_xxxxxxxxxxxxxx (xxxx is some name). Many of these have wp_ things in the value, but I’ve not changed them, as I’m guessing they aren’t that important, and maybe are just non-related variables anyway.

Just for the sake of other readers… a few of the places you need to change:
(I’ll call them ‘wp_’ but you will have renamed to ‘yourname_’ already, so take that into account). BTW, this isn’t an exhaustive list…

table: wp_options col: option_name data: wp_user_roles (change to yourname_user_roles)

table: wp_usermeta col: meta_key data: wp_capabilities (several of them), wp_user_level (several of them), wp_autosave_draft_ids, wp_usersettings, wp_usersettingstime, wp_dashboard_quick_press_last_post_id (again, put yourname_ in place of wp_)

My initial 70-some included a bunch of records the stat plugin had been recording, so they are rather irrelevant for me to change or worry about.

remember some of these could be functions rather than directly connected to the database naming.

Yes, I think so… and the problem (for me anyway) is telling the difference. So, I think that neither strategy: a) changing only the table names, or b) doing a search and replace on everything ‘wp_’, are going to actually work. ??

The good news is that as far as I can tell, my blog is working fine. Hopefully I’ve picked enough of the right ones that what is left is obscure references I maybe don’t use anyway, and hopefully I didn’t change any of the functions I wasn’t supposed to.

So, in my experience now, DB renaming isn’t nearly as straight forward as some of the articles make it out to be. Proceed with caution folks. I think my other blogs are going to remain wp_ for now. I’ll just hope the WP devs have some really good ‘cleaning’ code on inputs to the DB.

My cron stopped working after renaming my table prefix. Have to find out how and what now…

Ok… the way to get this fixed for users that don’t know what to do.
Get a fresh install of the latest version of WP. Install this on your local server. In the new setup you can now set a custom table prefix by default. Make this qwe12s_ or whatever you want.
Once you set up this blog go into the database and check the tables that you are not sure about. Compare them with the blog that you just worked with and changed the table prefix on.

I found out that the prefix for cron needs to stay wp_

I hope I helped somebody with this info.