License checking code stalls database server

[maneeshblr]

In the PHP file Access.php (in system/ee/ExpressionEngine/Addons/pro/Service/Access/ folder), there is a function called "requiresValidLicense". It is running this query:

SELECT COUNT(DISTINCT(exp_members.member_id)) AS count
                FROM exp_members
                LEFT JOIN exp_members_roles ON (exp_members.member_id = exp_members_roles.member_id)
                LEFT JOIN exp_members_role_groups ON (exp_members.member_id = exp_members_role_groups.member_id)
                LEFT JOIN exp_roles_role_groups ON (exp_members_role_groups.group_id = exp_roles_role_groups.group_id)
                WHERE exp_members.role_id IN ({$cpRolesList})
                OR exp_members_roles.role_id IN ({$cpRolesList})
                OR exp_roles_role_groups.role_id IN ({$cpRolesList})

This query may be okay for databases where the exp_members table contains only few thousand rows. For tables that contain larger number, say 250k rows, it becomes a major bottleneck for the database server.

On a moderate to high traffic server (say 20-50 hits per second), this code spawns off numerous queries (probably one per hit on the web server) and the MySQL server takes nearly all of the CPU capacity even on a 16-core or larger server with 32+ GB RAM. Each query takes inordinately long amount of time to complete and identical queries start building up. The MySQL server immediately runs out of max permitted connections (even if that number is 512 or 1024) and the server becomes unresponsive.

Please review this query and make it less CPU intensive so that it takes much less than a millisecond. Once that is done, this query should also be cached and run only once a day and not on every web page request from the internet... it is only checking for license and that can be done once a day or less frequently.

Thanks.

[bryannielsen]

@maneeshblr thanks for bringing this to our attention. Would it be possible for you to send us a partial export/dump of your database that only includes the following tables exp_members, exp_members_roles, exp_members_role_groups, exp_roles_role_groups? We understand the sensitive nature of member data so it would be fine to give us the minimal amount of data from the exp_members table. You can email the file to our support inbox support@expressionengine.com. If the file is too large maybe include a link to it on dropbox or somewhere else.

[robinsowell]

I've run into this again, and it's extra odd. They upgraded from v3 and db query time increased by over a second. It was all due to this query. Weird thing is, if they run the query directly, either in EE or phpmyadmin, the query is zippy. But if you look at template debug, it's taking about 1.2 seconds for just the query. Frontend and backend, it's adding way to much time. I had them try putting a limit of 2 on it, thinking maybe that would matter, but it didn't. Unfortunately, I can't get access to the database.

[maneeshblr]

I have shared the database dump on 10-Nov-2022 to help investigate. Whatever the fix is, this query should not run at every page load. License check can be done weekly or on daily basis. Alternatively, a license key file can be required to be placed in a known location. That should work well too.