Bug #16255
openNeed to define 'MULTISITE' constant in wp-config.php
0%
Description
While debugging the WebAuthn plugin database prefix problem in #16245, I found out that we have never set the following line in our wp-config.php file:
define( 'MULTISITE', true );
This explains why our main site's database tables are prefixed with wp_1_ rather than just wp_. See https://github.com/WordPress/WordPress/blob/c70a5c547f187e3a8c58779ae18155874cb412df/wp-includes/wp-db.php#L1065-L1069.
I just did a SQL query to see how many DB tables are affected and this is the result:
mysql> show tables like 'wp\_1\_%'; +---------------------------------+ | wp_1_2fa_webauthn_credentials | | wp_1_2fa_webauthn_users | | wp_1_ak_twitter | | wp_1_avhec_category_groups | | wp_1_bdprss_errors_v3 | | wp_1_bdprss_items_v3 | | wp_1_bdprss_lists_v3 | | wp_1_bdprss_sites_v3 | | wp_1_cac_invites | | wp_1_cac_library_items | | wp_1_commentmeta | | wp_1_comments | | wp_1_contact_form_7 | | wp_1_eo_events | | wp_1_eo_venuemeta | | wp_1_flickr_post | | wp_1_links | | wp_1_ngg_album | | wp_1_ngg_gallery | | wp_1_ngg_pictures | | wp_1_now_reading | | wp_1_now_reading_books2tags | | wp_1_now_reading_meta | | wp_1_now_reading_tags | | wp_1_options | | wp_1_podpress_statcounts | | wp_1_podpress_stats | | wp_1_pollsa | | wp_1_pollsip | | wp_1_pollsq | | wp_1_postmeta | | wp_1_posts | | wp_1_quotescollection | | wp_1_ratings | | wp_1_redirection_404 | | wp_1_redirection_groups | | wp_1_redirection_items | | wp_1_redirection_logs | | wp_1_redirection_modules | | wp_1_rg_form | | wp_1_rg_form_meta | | wp_1_rg_form_view | | wp_1_rg_incomplete_submissions | | wp_1_rg_lead | | wp_1_rg_lead_detail | | wp_1_rg_lead_detail_long | | wp_1_rg_lead_meta | | wp_1_rg_lead_notes | | wp_1_subscribe2 | | wp_1_term_relationships | | wp_1_term_taxonomy | | wp_1_termmeta | | wp_1_terms | | wp_1_wordtube | | wp_1_wordtube_med2play | | wp_1_wordtube_playlist | | wp_1_wpo_campaign | | wp_1_wpo_campaign_category | | wp_1_wpo_campaign_feed | | wp_1_wpo_campaign_post | | wp_1_wpo_campaign_word | | wp_1_wpo_log | | wp_1_wysija_bounce | | wp_1_wysija_campaign | | wp_1_wysija_campaign_list | | wp_1_wysija_custom_field | | wp_1_wysija_email | | wp_1_wysija_email_user_stat | | wp_1_wysija_email_user_url | | wp_1_wysija_form | | wp_1_wysija_list | | wp_1_wysija_queue | | wp_1_wysija_url | | wp_1_wysija_url_mail | | wp_1_wysija_user | | wp_1_wysija_user_field | | wp_1_wysija_user_history | | wp_1_wysija_user_list | +---------------------------------+ 78 rows in set (0.27 sec)
Most of these tables are for plugins we do not use on the main site anymore, so we can safely delete and ignore them. But before we add in the define( 'MULTISITE', true ) line to wp-config.php, we'd have to copy the affected DB tables to use the wp_ prefix first.
This is not really urgent to address (we've gone this long without the MULTISITE constant!), so we can look at this when Boone is back from vacation.
Updated by Boone Gorges over 2 years ago
My understanding (from long ago!) is that WPMU named tables like this, and after the MU merge in WP 3.0, you'd have to change table names if you wanted to add the MULTISITE flag. Does that seem right?
The PR linked to in #16245 is interesting, but I wonder whether it's a bug that really needs fixing on our end. The assumption there (if I'm understanding it on a quick reading) was that the main site's table prefix would be identical to the global base prefix. I've never assumed that this is the case (in part because of the MU issues and the Commons). Do we know of other bugs that might be caused by our current configuration? What kinds of side effects might we expect if we made the switch? Are there places in our custom tools where we've hardcoded wp_1_?
Updated by Boone Gorges over 1 year ago
- Assignee set to Jeremy Felt
- Target version set to Future release
I think this is worth doing if it's fairly easy. Jeremy, have you every come across this sort of migration in your travels? What gotchas should we be looking out for?
Updated by Jeremy Felt over 1 year ago
I've been lucky enough in that most multisite instances I've managed were created late enough that MULTISITE was likely to be defined. I think that every time I've run into this, I've just let it be OR it was early enough that I reinstalled.
Something more involved than altering table names are prefixed option and meta keys.
In wp_1_options, the user roles option is wp_1_user_roles. That is easy because it's one record to change.
These are the prefixed keys for users in wp_1_usermeta: (there may be more)
- wp_1_capabilities
- wp_1_user_level
- wp_1_persisted_preferences
- wp_1_user-settings
- wp_1_user-settings-time
- wp_1_dashboard_quick_press_last_post_id
When I look at my local copy of the commons database, I see keys (with different values?) for wp_capabilities and wp_1_capabilities. I'm not sure which of those WordPress is looking at and there are some differences. For example, user ID 49 (Boone) has:
wp_capabilities a:2:{s:13:"administrator";b:1;s:15:"bbp_participant";b:1;}
wp_1_capabilities a:4:{s:13:"Administrator";b:1;s:10:"can_runPHP";s:4:"true";s:10:"subscriber";b:1;s:13:"bbp_keymaster";b:1;}
My guess is that wp_1_capabilities is the source of truth, but I don't know what the consequences of overwriting wp_capabilities are. (e.g. Is there a chance it was accidentally set by WordPress or a plugin using the wrong prefix?)
If we do find a clear path for changing wp_1_capabilities and wp_1_user_level, then the others are much less consequential.
Changing all of that is usually the most painful part of moving or extracting a site from a multisite network.
This is where I would carefully measure my confidence in the change with the annoyance that it's causing and probably let it be. :)
Updated by Boone Gorges over 1 year ago
Jeremy, your comment makes me wonder if the wp_ vs wp_1_ distinction is part of why certain members of our team occasionally lose their roles on the main site. It could be that bbPress is hardcoding a wp_ check on multisite. See #8860 and related tickets.
I'm typically in the "let's not mess with it", but the fact that there are conflicting values for these permissions in the database suggests to me that there are latent bugs here, and it might be worth the effort to clear it up.
Here's the list of usermeta keys from the production site:
mysql> select distinct meta_key from wp_usermeta where meta_key like 'wp\_1\_%'; +-----------------------------------------+ | meta_key | +-----------------------------------------+ | wp_1_autosave_draft_ids | | wp_1_capabilities | | wp_1_classic-editor-settings | | wp_1_dashboard_quick_press_last_post_id | | wp_1_media_library_mode | | wp_1_persisted_preferences | | wp_1_s2_authors | | wp_1_s2_autosub | | wp_1_s2_cat1 | | wp_1_s2_format | | wp_1_s2_subscribed | | wp_1_user-settings | | wp_1_user-settings-time | | wp_1_usersettings | | wp_1_usersettingstime | | wp_1_user_level | | wp_1_yoast_notifications | | wp_1__bbp_engagements | | wp_1__bbp_favorites | | wp_1__bbp_last_posted | | wp_1__bbp_reply_count | | wp_1__bbp_subscriptions | | wp_1__bbp_topic_count | +-----------------------------------------+ 23 rows in set (0.06 sec)
On our current configuration, most users don't have or need to have any caps on the main site. Yet most users have this meta key:
mysql> select count(*) from wp_usermeta where meta_key = 'wp_1_capabilities'; +----------+ | count(*) | +----------+ | 34747 | +----------+ 1 row in set (0.03 sec)
Here are the distinct values for that key:
mysql> select distinct meta_value from wp_usermeta where meta_key = 'wp_1_capabilities';
+--------------------------------------------------------------------------------------+
| meta_value |
+--------------------------------------------------------------------------------------+
| a:2:{s:6:"author";b:1;s:15:"bbp_participant";b:1;} |
| a:2:{s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;} |
| a:1:{s:10:"subscriber";b:1;} |
| a:3:{s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;s:14:"courses_editor";b:1;} |
| a:3:{s:10:"can_runPHP";s:0:"";s:10:"subscriber";s:1:"1";s:15:"bbp_participant";b:1;} |
| a:2:{s:10:"subscriber";s:1:"1";s:15:"bbp_participant";b:1;} |
| a:1:{s:13:"administrator";b:1;} |
| a:1:{s:10:"subscriber";s:1:"1";} |
| a:1:{s:15:"bbp_participant";b:1;} |
| a:3:{s:10:"subscriber";s:1:"1";s:10:"can_runPHP";s:0:"";s:15:"bbp_participant";b:1;} |
| a:2:{s:10:"subscriber";s:1:"1";s:10:"can_runPHP";s:0:"";} |
| a:2:{s:10:"can_runPHP";s:0:"";s:10:"subscriber";s:1:"1";} |
| a:3:{s:10:"can_runPHP";b:0;s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;} |
| a:2:{s:10:"can_runPHP";s:0:"";s:10:"subscriber";b:1;} |
| a:3:{s:10:"can_runPHP";s:0:"";s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;} |
| a:2:{s:10:"can_runPHP";b:0;s:10:"subscriber";b:1;} |
| a:1:{s:10:"can_runPHP";s:0:"";} |
| a:2:{s:6:"editor";s:1:"1";s:15:"bbp_participant";b:1;} |
| a:2:{s:10:"can_runPHP";s:4:"true";s:15:"bbp_participant";b:1;} |
| a:0:{} |
| a:1:{s:6:"author";b:1;} |
| a:2:{s:15:"bbp_participant";b:1;s:10:"can_runPHP";b:0;} |
| a:2:{s:6:"editor";b:1;s:15:"bbp_participant";b:1;} |
| a:2:{s:13:"administrator";b:1;s:13:"bbp_keymaster";b:1;} |
+--------------------------------------------------------------------------------------+
24 rows in set (0.31 sec)
The vast majority of these are either "subscriber" or "bbp_participant". For the sake of bbPress, these users probably need to continue to have their permissions:
mysql> select meta_value, count(*) from wp_usermeta where meta_key = 'wp_1_capabilities' group by meta_value;
+--------------------------------------------------------------------------------------+----------+
| meta_value | count(*) |
+--------------------------------------------------------------------------------------+----------+
| a:0:{} | 777 |
| a:1:{s:10:"can_runPHP";s:0:"";} | 5 |
| a:1:{s:10:"subscriber";b:1;} | 5993 |
| a:1:{s:10:"subscriber";s:1:"1";} | 264 |
| a:1:{s:13:"administrator";b:1;} | 3 |
| a:1:{s:15:"bbp_participant";b:1;} | 20661 |
| a:1:{s:6:"author";b:1;} | 3 |
| a:2:{s:10:"can_runPHP";b:0;s:10:"subscriber";b:1;} | 1 |
| a:2:{s:10:"can_runPHP";s:0:"";s:10:"subscriber";b:1;} | 1 |
| a:2:{s:10:"can_runPHP";s:0:"";s:10:"subscriber";s:1:"1";} | 1 |
| a:2:{s:10:"can_runPHP";s:4:"true";s:15:"bbp_participant";b:1;} | 1 |
| a:2:{s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;} | 6925 |
| a:2:{s:10:"subscriber";s:1:"1";s:10:"can_runPHP";s:0:"";} | 7 |
| a:2:{s:10:"subscriber";s:1:"1";s:15:"bbp_participant";b:1;} | 78 |
| a:2:{s:13:"administrator";b:1;s:13:"bbp_keymaster";b:1;} | 1 |
| a:2:{s:15:"bbp_participant";b:1;s:10:"can_runPHP";b:0;} | 5 |
| a:2:{s:6:"author";b:1;s:15:"bbp_participant";b:1;} | 8 |
| a:2:{s:6:"editor";b:1;s:15:"bbp_participant";b:1;} | 1 |
| a:2:{s:6:"editor";s:1:"1";s:15:"bbp_participant";b:1;} | 1 |
| a:3:{s:10:"can_runPHP";b:0;s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;} | 1 |
| a:3:{s:10:"can_runPHP";s:0:"";s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;} | 3 |
| a:3:{s:10:"can_runPHP";s:0:"";s:10:"subscriber";s:1:"1";s:15:"bbp_participant";b:1;} | 3 |
| a:3:{s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;s:14:"courses_editor";b:1;} | 1 |
| a:3:{s:10:"subscriber";s:1:"1";s:10:"can_runPHP";s:0:"";s:15:"bbp_participant";b:1;} | 3 |
+--------------------------------------------------------------------------------------+----------+
24 rows in set (0.50 sec)
Interestingly, there's only 240 users on the production site with wp_capabilities. Only a handful are not 'subscriber':
mysql> select meta_value, count(*) from wp_usermeta where meta_key = 'wp_capabilities' group by meta_value;
+------------------------------------------------------------+----------+
| meta_value | count(*) |
+------------------------------------------------------------+----------+
| a:1:{s:10:"subscriber";b:1;} | 232 |
| a:1:{s:13:"administrator";b:1;} | 3 |
| a:1:{s:15:"bbp_participant";b:1;} | 3 |
| a:2:{s:10:"subscriber";b:1;s:15:"bbp_participant";b:1;} | 1 |
| a:2:{s:13:"administrator";b:1;s:15:"bbp_participant";b:1;} | 1 |
+------------------------------------------------------------+----------+
5 rows in set (0.14 sec)
Given the fact that relatively few users would have conflicting values, I think it's probably do-able to do the necessary merge.
Here's the beginning of an action plan:
0. Back up wp_usermeta and wp_1_ tables 1. Dealing with capabilities a. Look at the 8 user accounts from my last query who have non-Subscriber values for 'wp_capabilities' and determine what should be done with them. b. Delete all instances of 'wp_capabilities' c. Rename all instances of 'wp_1_capabilities' to 'wp_capabilities' 2. Change the rest of the 'wp_1_' keys in wp_usermeta to their 'wp_' equivalents. I'd like to script this rather than doing it manually or trying to run a single replace query. 3. In 'wp_1_options', change 'wp_1_user_roles' to 'wp_user_roles' 4. Change all 'wp_1_' tables to their 'wp_' equivalents. I'd like to script this rather than doing it manually or trying to run a single query for it. 5. Set the MULTISITE flag.
Does that largely cover it? If so, I can try doing it locally before we set up a time to test on cdev.