1

I have a live WordPress installation and a local one for development purposes. I just exported the live database, replaced all occurences of the live siteurl with the local one, and imported on my dev machine.

Problem is that on my dev machine all users have lost any permissions. When logging in I get:

Notice: Undefined offset: 2 in /var/www/mysite/wp-admin/includes/plugin.php on line 1390 

On a page template I dumped out the current userinfo with

global $current_user;
var_dump( get_currentuserinfo() );

This returns NULL on my dev machine and the correct userinfo on my live installation. I double checked the table prefixes, permissions, tried a clean WordPress installation, etc but had no luck. The confusing thing about this is, that I can query the userinfo perfectly fine with a custom sql query.

EDIT: Upon further inspection I found out that the global $wp_roles contains no user roles and capabilities. WordPress somehow can't retrieve this from the database...

EDIT 2: Sorry for the edits. But I found the problem. Inside the wp_options table there is a row "wp_user_roles". The value, an unserialized string did somehow include whitespaces which lead to errors when trying to unserialize the string. I replaced the database value with the live site's one and everything works now.

2 Answers 2

2

I just exported the live database, replaced all occurences of the live siteurl with the local one, and imported on my dev machine.

Finding/replacing with a text editor breaks serialized data; you must change values with MySQL queries in phpmyadmin:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.olddomain.com/', 'http://www.newdomain.com/') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://www.olddomain.com/blog/','http://www.newdomain.com/');

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.olddomain.com/blog/', 'http://www.newdomain.com/');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://www.olddomain.com/blog/', 'http://www.newdomain.com/');
3

Just in case anyone else has this issue:

I had two sites with a custom table prefix. This prefix is also used for some rows in the options table. When moving the options table from one site to another, I renamed the options table itself, but didn't know I had to rename some of the option names too, in particular the wp_user_roles option which was actually prefix_user_roles. There were several options in my wp_options (prefix_options) table with that prefix.

Wordpress automatically created the prefix_user_roles option when it did not recognize the otherprefix_user_roles option which locked me out.

2
  • Was very helpful! I too have the setup of a single database with multiple Wordpress installations segregated by different table prefixes. Started my first cloning with phpMyAdmin by selecting all the tables wp_staging_* then opened dropdown command menu → With Selected → Copy table with prefix → From wp_staging_ to wp_production_. All worked fine, users could also login, but permissions were flaky. After updating prefixes in the _options and _usermeta tables too the user roles/permissions where fine again!
    – porg
    Mar 9, 2023 at 1:09
  • In depth description, including the SQL query which not only finds but also replaces the prefixes for you at: wp-staging.com/…
    – porg
    Mar 9, 2023 at 1:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.