Skip to content

Obviate.io

To anticipate and prevent

  • Home
  • About Us
  • History
  • Privacy Policy
  • Toggle search form

Serendipity to WordPress – Changing table prefix

Posted on 2010-06-17 By Jon No Comments on Serendipity to WordPress – Changing table prefix

While this isn’t strictly part of the migration process from S9y to WordPress, I decided to take some advice I read on the net and change the prefix for WordPress tables.  Most use this feature for when they have to share a database, but the suggestions I read said to do it to help prevent from SQL injection hacks.  Basically the majority of these injections presume a default of ‘wp_’ prefix.

I made this after install very simple by going in MySQL and running

RENAME TABLE `wp_posts` TO `prefix_posts` against each table (where “prefix_” was the new prefix).  After changing it in MySQL, I changed the wp-config.php line of $table_prefix  = ‘wp_’; to read as my new prefix.  I popped up to the web and the blog worked fine.  Except… I couldn’t access the administrative interface.

You do not have sufficient permissions to access this page.

LIAR!!! What the hell? Yes I do.  I did 10 minutes ago and I should now.  I tried my other (administrative) users, same problem.  This was quite displeasing to me. After a little googling about, I found out that the answer is that the `meta_key` prefix of ‘wp_’ (in `wp_usermeta`) and certain values in `wp_options` are also dependent of this prefix value.  Fortunately it is really easy to fix:

  • UPDATE `<strong>PREFIX</strong>_usermeta` SET `meta_key` = REPLACE( `meta_key` , 'wp_', '<strong>PREFIX</strong>_' );  -- Change <strong>PREFIX</strong>_ to your new prefix.
  • UPDATE `<strong>PREFIX</strong>_options` SET `option_name` = '<strong>PREFIX</strong>_user_roles' WHERE `option_name` = 'wp_user_roles' AND `blog_id` = 0; -- Change <strong>PREFIX</strong>_ to your new prefix.

Execute those two lines of SQL (after you’ve replaced it with the proper prefix), and you’ll be ready to rock and roll again.  This is one of those odd little bits that they don’t explain anywhere in the manual — probably because most people don’t use anything other than the defaults… which brings us right back to the security concern (i.e. easy to guess/default password = bad).

Having keys in tables change because the table prefix is different is one of the stranger things done in a web application that I’ve worked with.  I can’t really think of a reason to do this, but I’m sure the developers had a good reason (as strange as it might be).  Fortunately this problem is easy enough to fix, but it certainly is frustrating when your admin user gets “You do not have sufficient permissions to access this page.” and you know better.

Database, WordPress Tags:access denied, MySQL, s9y, serendipity, wordpress

Post navigation

Previous Post: “USB Drive not formatted” on one computer, but fine in another
Next Post: Recent Changes Camp is Next Week!

More Related Articles

s9y-to-wp has a new home on GitHub Code
Let WordPress (not) snow Site Maintenance
WordPress: Smart backups BackWPup and Amazon S3 Cloud

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

June 2010
S M T W T F S
 12345
6789101112
13141516171819
20212223242526
27282930  
« May   Jul »

Copyright © 2022 Obviate.io

Powered by PressBook Premium theme