In this article find out how to fix WordPress after changing the table prefix. In your wordpress wp-config.php file you have a $table_prefix variable, which by default is set to “wp_”. If you need to change that on an existing wordpress site then you’re in for a bit of a battle.
On a site I’m working with we needed to change the table prefix when moving the site to a new server. Changing the table prefix meant that we weren’t able to access the wordpress admin area any more. We got the wordpress black bar and the name of the site, but no other wordpress links were present in the black bar (like Add New, etc). Also, when clicking on the link it just came straight back to the home page, not the wordpress admin area as expected:
Why can’t I access the WordPress admin area after changing the table prefix?
The problem you’re experiencing is due to roles and capabilities not being correctly linked in your database after changing the table prefix in wp-config.php in your WordPress site. This means that your user profile effectively has no permissions to access the wordpress admin area.
How to fix the wordpress admin area after changing the table prefix?
You’ll need to make 2 changes in your database. The first one is in the nnn_options table (where nnn is whatever your table prefix is).
- Open your database (command line or phpmyadmin or whatever you’re comfortable with).
- Assuming you changed the table prefix from wp_ to mynewprefix_, run the following command:
- update mynewprefix_options set option_name = ‘mynewprefix_user_roles’ WHERE option_name = ‘wp_user_roles’;
- Next, you’ll need to make several edits to the nnn_usermeta table:
- First, find what they are by running: select * from mynewprefix_usermeta WHERE meta_key like ‘wp\_%’;
- Then for each table update its prefix. For my installation I had 5 tables to edit:
- update mynewprefix_usermeta set meta_key = ‘mynewprefix_capabilities’ WHERE meta_key =’wp_capabilities’;
- update mynewprefix_usermeta set meta_key = ‘mynewprefix_dashboard_quick_press_last_post_id’ WHERE meta_key =’wp_dashboard_quick_press_last_post_id’;
- update mynewprefix_usermeta set meta_key = ‘mynewprefix_user-settings’ WHERE meta_key =’wp_user-settings’;
- update mynewprefix_usermeta set meta_key = ‘mynewprefix_user-settings-time’ WHERE meta_key =’wp_user-settings-time’;
- update mynewprefix_usermeta set meta_key = ‘mynewprefix_user_level’ WHERE meta_key =’wp_user_level’;
You should now be able to log back into your wordpress admin area after having changed your database table prefix. Please leave comments or questions in the comments section below.
John McMurray is a freelance PHP developer. Experienced in PHP, Laravel and WordPress he can code anything you need.
Based in Plettenberg Bay, Western Cape I am available for PHP, Laravel or WordPress freelance jobs in Johannesburg, Pretoria, Cape Town, Port Elizabeth. I also often do remote PHP freelance work all over South Africa and the rest of the world.