get_editable_roles() returns empty array in multisite

[neo2k23]

I have no clue where to look for this issue so i am posting the question here.

My website moved from the old server to the new one. Its a multisite.

But get_editable_roles() now returns a empty array. No roles.

I tried latest trunk 7.1 reverted to 6.9.4 but now roles are returned.

If i install a plugin like "user roles" the roles are also gone.

Even the woocommerce shop roles are gone.

How to fix this? Any idea's how to deal with this?

[neo2k23]

Missing user roles

[neo2k23]

If i goto users in a subsite the user role dropdown is also empty. If i create a new user it also shows no roles in the dropdown.

[neo2k23]

user roles missing in user settings

[neo2k23]

cpanel transfer changed the table prefix causing all issues.

never mind i restored another backup. issue resolved. please close

[neo2k23]

I want to reopen this ticket. as i discovered the why this happened.

Its a bug in cpanel copy when it changes the wp prefix. It forgets to change the old prefix in the options_table for the multisite old_prefix_xx_user_roles

f.e. in a multisite the user roles are stored in my case in OLDPREFIX_33_user_roles. After the database prefix changed to NEWPREFIX that option should have also been changed to NEWPREFIX_33_user_roles. but it remained OLDPREFIX_33_user_roles.

Since wordpress is calling databaseprefix_site_id_user_roles where site_id is a number of the site in the multisite it gets a empty user role list calling get_editable_roles()

The question is why does wordpress add the site_id into the option that stores the user roles as they are store in the option table of the sub site in the multi site and not in the main option table of the first site in the multi_site. It is not needed to add the site_id into that variable.

So why is the site_id number added into that variable? Its the only variable in the complete options stored that has this site_id added.

I have seen similar issues in a multisite with mapped domain names. The get_editable_roles() returns a empty array after the domain is mapped.

[neo2k23]

_wp_user_roles _wp_xx_user_roles not changed

[audrasjb]

Removing trunk version as this is not going to be shipped with WP 7.0 but in the next releases.

## Summary

• Fixes #65241: get_editable_roles() can return an empty array on multisite when user role options use a stale database prefix.
• Adds automatic detection and migration of misnamed *_user_roles options in WP_Roles::get_roles_data() when the expected option is missing but a legacy option exists (e.g. after cPanel or other migrations that rename tables but not option names).
• Adds PHPUnit coverage for single-site and multisite subsite scenarios, including get_editable_roles() after migration.

## Problem

On multisite subsites, roles are stored in each site’s options table under a key such as {prefix}{blog_id}_user_roles (e.g. wp_33_user_roles). After a table prefix change, migration tools often update table names but leave option names on the old prefix. WordPress then looks up newprefix_33_user_roles while the database still has oldprefix_33_user_roles, so roles load as empty and admin UI role dropdowns break.

## Solution

When the expected roles option is empty, core looks for a legacy option ending in {blog_id}_user_roles (multisite subsite) or user_roles (main site / single site), validates the data, copies it to the correct option name, and removes the legacy option.

Trac ticket: https://core.trac.wordpress.org/ticket/65241

## Use of AI Tools

[mindctrl]

This looks like a migration tool bug and not a WordPress bug. I would not expect core to try solving this internally.

[neo2k23]

@mindctrl yes and no.... and i disagree and lean more towards yes because...:

Why is this the only option that has this structure in the option table?

{prefix}{blog_id}{option_name}

if it had been {prefix}{option_name} it would have been not a single issue.

There is not a single valid reason to add the blog_id into the option itself as the option is already stored in {prefix}_{blog_id}_{options} table.

So the issue is more or less caused by the way wordpress creates the user_roles option in the database for multisite.

The issue does not occur in a single site as the {blog_id} is not present in the variable itself

So it sounds to me more like a "bug"

[mehul0810]

I have reviewed the ticket, and my current understanding is that this is more of a migration/data integrity issue than a WordPress Core bug.

WordPress uses the configured database prefix to determine the expected table names and related prefixed keys. If a multisite database is migrated and the table prefix is changed after installation, the migration process needs to update all prefix-dependent data, not only $table_prefix and the physical table names.

The [WordPress migration documentation](​https://developer.wordpress.org/advanced-administration/upgrade/migrating/#altering-table-prefixes) already calls out that changing table prefixes requires updating prefixed usermeta keys as part of the migration. In this ticket, the stale wp_10_user_roles option appears to be the same category of issue: prefix-dependent data was left behind under the old prefix.

Because of that, it feels this can be better handled by the migration tooling or by an explicit repair step, using tools such as WP-CLI or SQL commands or a migration plugin that is responsible for completing the prefix migration.

cc: @mindctrl @neo2k23

[neo2k23]

@mehul0810 i am sorry but i still disagree.

this article: (​​https://developer.wordpress.org/advanced-administration/upgrade/migrating/#altering-table-prefixs) you are refering to, is about the user meta data in the wordpress meta table. We are not talking about the user meta here.

Moreover the by you suggested replace is only for the main table as that is the only one without a multisite_id in the table names. All others have NEWPREFIX_MULTISITE_ID_usermeta

UPDATE newprefix_usermeta SET meta_key = REPLACE( meta_key , 'oldprefix_', 'newprefix_' );

In case you have a big multisite you have to run this per usermeta ... that is a lot of fun.

But here we are talking about a option in the options table that is the only option ever that uses the database prefix (and multisite_id) in a table that is already using the prefix (and in case of a multisite also the multisite_id) in the tables name.

The adding of the prefix and multisite id to that option is completely useless and illogical. They both should never have been added to that option in the first place in which case the transfer tool, or what ever tool you use the change a prefix, would never caused any issues.

[mindctrl]

They both should never have been added to that option in the first place

That may be true, but that's how it is now, and has been for a long time.

The migration tool was responsible for updating the key, but it didn't. What else did it do incorrectly? We don't know. That's one reason why I think WP should not attempt to fix it internally.

It would hide broken migrations, and it might not even fix all the data that was missed by the tool. It would also add code to the hot path and penalize every correctly configured site just to accommodate tools that don't migrate the data correctly. It seems like a very small edge case.

[neo2k23]

@mindctrl so "It seems like a very small edge case." is your reason for not fixing a bug?

The trunk patch added before did the job perfectly in either case and took care of the missed conversion of the variable in the option table. It was a easy patch to apply and was the perfect solution. It topok only care of this situation in case the xxxx_xxx_user_roles was not correctly converted.

The fact that one calls it a very small edge case must never be a valid reason for not patching a flaw in the system.

[wildworks]

I just want to confirm, the issues being discussed here are NOT new problems that first appeared in 7.0, are they? If so, I'd like to change the milestone from 7.0.1 to 7.1 or Future release.

[neo2k23]

Hi

it appears in any wp from 3.xx build to current version when a multisite is moved from one server to another with the table prefix changed.

[wildworks]

I'd like to change the milestone for this issue as it is not related to recent WordPress releases.