My Sites broken in 4.7.2

[earl.fogel]

After upgrading to 4.7.2, My Sites shows an error:

You must be a member of at least one site to use this page.

This happens for both regular users and administrators.

I've fixed it for now by changing one line of code:

diff wp-admin/my-sites.php.orig wp-admin/my-sites.php
20c20
< $blogs = get_blogs_of_user( $current_user->ID );
---

$blogs = get_blogs_of_user( $current_user->ID, 1 );

[Ipstenu]

@earl.fogel - By 'admins' do you mean 'network admins' or site-admins? And if they're network admins, are they members of any sites?

(I can't reproduce this on my trunk or 4.7.2 site)

[earl.fogel]

It's both site admins and network admins. The users who reported it would be site admins -- I'm a network admin and had the same issue. I'm a member of two sites -- site admin on one and an author on another, but the My Sites drop-down just shows Network Admin.

The problem seems to be the code that removes archived, spam and deleted blogs from the list, because it works if I remove that code.

Since you can't duplicate it, maybe there's an issue with one of our plugins.

[Ipstenu]

The problem seems to be the code that removes archived, spam and deleted blogs from the list, because it works if I remove that code.

That sound ominous. Can you check and make sure someone didn't do something silly like mark all your sites as archived?

[earl.fogel]

Hmm, I checked and they're not marked archived/spam/deleted. I also disabled all plugins, but that didn't help.

I don't know what's going on, but I've found two ways to fix it:

1) Replace the get_blogs_of_user() function with the one from our previous version of WordPress (4.6.1).

2) Alternatively, comment-out the lines in that function that do the filtering, i.e.:

<?php
        if ( ! empty( $site_ids ) ) {
                $args = array(
                        'number'   => '',
                        'site__in' => $site_ids,
                );
                if ( ! $all ) {
//                      $args['archived'] = 0;
//                      $args['spam']     = 0;
//                      $args['deleted']  = 0;
                }

Could this be an issue with our PHP version? We currently run PHP 5.3.3.

[lymn]

I have the same problem after upgrading to version 4.7.2 four weeks ago.
The error message "You must be a member of at least one site to use this page." disappeared after
replacing the get_blogs_of_user() function with the one from WordPress 4.6.1.

I thought this will be fixed in version 4.7.3 but found that it's not.
(wp-includes/user.php is not revised yet)
May I ask if this bug will be fixed in the next update?

[flixos90]

@lymn Did the problem appear after 4.7.2 or after the 4.7 release? For 4.7 we modified get_blogs_of_user() to use get_sites() (see #37061), so we might have missed something there (although the function had sufficient unit tests we thought). I don't know what could cause your problem if it only occurs >= 4.7.2.

[lymn]

Replying to flixos90:

@lymn Did the problem appear after 4.7.2 or after the 4.7 release? For 4.7 we modified get_blogs_of_user() to use get_sites() (see #37061), so we might have missed something there (although the function had sufficient unit tests we thought). I don't know what could cause your problem if it only occurs >= 4.7.2.

I upgraded it from 4.6.1 to 4.7.2 and the problem appeared after the upgrade.
If get_blogs_of_user() was modified since 4.7, then I guess the problem started there.

[earl.fogel]

I added some debugging to capture the SQL queries. Here's what I get:

MariaDB [wordpress]> SELECT blog_id FROM wp_blogs WHERE blog_id IN ( 2,5 ) AND archived = 0 AND spam = 0 AND deleted = 0 ORDER BY blog_id ASC;
Empty set (0.00 sec)

For some reason the zeros don't match, even though there are zeros in the database:

MariaDB [wordpress]> SELECT blog_id, archived, spam, deleted FROM wp_blogs WHERE blog_id IN ( 2,5 ) ORDER BY blog_id ASC;
+---------+----------+------+---------+
| blog_id | archived | spam | deleted |
+---------+----------+------+---------+
| 2 | 0 | 0 | 0 |
| 5 | 0 | 0 | 0 |
+---------+----------+------+---------+
2 rows in set (0.00 sec)

Here's the table definition:

CREATE TABLE wp_blogs (

blog_id bigint(20) NOT NULL AUTO_INCREMENT,
site_id bigint(20) NOT NULL DEFAULT '0',
domain varchar(200) NOT NULL DEFAULT ,
path varchar(100) NOT NULL DEFAULT ,
registered datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
last_updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
public tinyint(2) NOT NULL DEFAULT '1',
archived enum('0','1') NOT NULL DEFAULT '0',
mature tinyint(2) NOT NULL DEFAULT '0',
spam tinyint(2) NOT NULL DEFAULT '0',
deleted tinyint(2) NOT NULL DEFAULT '0',
lang_id int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (blog_id),
KEY domain (domain(50),path(5)),
KEY lang_id (lang_id)

) ENGINE=MyISAM AUTO_INCREMENT=309 DEFAULT CHARSET=utf8;

[lymn]

Replying to earl.fogel:

For some reason the zeros don't match, even though there are zeros in the database:

It seems that the type for 'archived' was enum('0','1') previously.
It has been changed to tinyint(2) recently. (Not sure when it was changed)
Modifying the column definition to tinyint(2) will solve this problem.

Before modification:
mysql> describe wp_blogs;
+--------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------------------+----------------+
| archived | enum('0','1') | NO | | 0 | |
| spam | tinyint(2) | NO | | 0 | |
| deleted | tinyint(2) | NO | | 0 | |

mysql> ALTER TABLE wp_blogs MODIFY COLUMN archived tinyint(2) NOT NULL DEFAULT '0';

After modification:
mysql> describe wp_blogs;
+--------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------------------+----------------+
| archived | tinyint(2) | NO | | 0 | |
| spam | tinyint(2) | NO | | 0 | |
| deleted | tinyint(2) | NO | | 0 | |

After the modification, the value of archived will be set to '1'.
Update the value of archived to '0' then you will be able to view a list of your sites.

mysql> UPDATE wp_blogs SET archived=0;

That's all.

[earl.fogel]

Thanks, that works but has a side effect of unarchiving any previously archived blogs.

I found an alternate method that avoids this:

mysql> alter table wp_blogs modify column archived char(1);
mysql> alter table wp_blogs modify column archived tinyint(2) NOT NULL DEFAULT '0';

[lymn]

That is really a perfect solution to this problem.
It has been a great help to me. Thanks a lot.

[sicco]

Replying to earl.fogel:

Thanks, that works but has a side effect of unarchiving any previously archived blogs.

I found an alternate method that avoids this:

mysql> alter table wp_blogs modify column archived char(1);
mysql> alter table wp_blogs modify column archived tinyint(2) NOT NULL DEFAULT '0';

I had the same problem and your two commands fixed it for me as well. I had to make another small fix first though because I was getting this error when trying the above commands:

ERROR 1067 (42000): Invalid default value for 'registered'

I think that my MySQL version doesn't like datetime columns like registered and last_updated to default to 0, so I ran the following command to fix that (I guess this won't give any problems?):

alter table wp_blogs modify column registered datetime NOT NULL DEFAULT '1970-01-01 00:00:00', modify column last_updated datetime NOT NULL DEFAULT '1970-01-01 00:00:00';

After this command the two commands by @earl.fogel worked!