I was recently contacted about an Email Users issue on a site that is using bbPress and S2Member. The person had come to the conclusion that there was some sort of conflict between the three plugins which resulted in the Email Users -> Users Settings screen being empty and asked me to look into it.
Clearly something is amiss as in order to access the WordPress Dashboard you need to login and in order to login, there needs to be at least one user! This isn’t the sort of situation I can easily replicate. Fortunately the person with this problem provided me access to their server so we could do some debugging.
The functionality in Email Users is based on the WP_List_Table class and an example plugin which you can find in the WordPress Plugin Repository. When I first implemented it I was retrieving the list of users with a call to get_users() however I never got the sorting working correctly. After playing around with it for a while, I gave up and wrote my own query. The query I wrote yielded the results and sorting I wanted so I didn’t think much about it. Until today.
This the query that WordPress generates and sends to MySQL:
SELECT ID, display_name, user_email, user_login, m1.meta_value first_name, m2.meta_value last_name, m3.meta_value massemail, m4.meta_value notifications
FROM wp_users u
LEFT JOIN wp_usermeta m1 ON (m1.user_id = u.ID AND m1.meta_key = 'first_name')
LEFT JOIN wp_usermeta m2 ON (m2.user_id = u.ID AND m2.meta_key = 'last_name')
LEFT JOIN wp_usermeta m3 ON (m3.user_id = u.ID AND m3.meta_key = 'email_users_accept_mass_emails')
LEFT JOIN wp_usermeta m4 ON (m4.user_id = u.ID AND m4.meta_key = 'email_users_accept_notifications')
ORDER BY last_name ASC LIMIT 0,100
What this query does is select all of the WordPress User Ids, first names, and last names, which have the proper Email Users user meta keys. The data is returned and stored in a format which can be rendered in the List Table.
Because the table was empty, I wanted to see what the results of the query were as that would tell me where the issue might be. Because I had access to the MySQL database via phpMyAdmin, I was able to run the query directly against MySQL. When I did so, this is what I found:
This certainly was unexpected but it explained why the List Table was empty. I have never run into this sort of problem, in fact, I had never heard of either the SQL_MAX_JOIN_SIZE or the SQL_BIG_SELECTS MySQL system variables.
I was able to alter SQL_BIG_SELECTS using the following SQL:
SET SQL_BIG_SELECTS=1
When I did this and re-ran the query, phpMyAdmin returned the results I would have expected to see. This was good news – it appears this users problem is due to a MySQL setting. The bad news is sometimes it is hard to change these settings and it isn’t something I thought I could add to the query itself.
As a test, I modified the source for one of Email Users files so it was calling the original method I had coded based on get_users() to see if it would work and it did. This is very good news as I suspect fixing the sorting issue (or living with it) will be easier than changing the MySQL system variable settings.
My next step is to see if I can fix the sorting problem and roll out an update.
