Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
The recursive privilege selection query issued by MaxScale does not scale well when having a non-trivial (but still realistic) number of users and roles.
WITH RECURSIVE t AS
|
(
|
SELECT u1.user, u1.host, d1.db, u1.select_priv, IF(u1.password <> '', u1.password, u1.authentication_string) AS password, u1.is_role, u1.default_role
|
FROM mysql.user AS u1
|
LEFT JOIN mysql.db AS d1
|
ON (u1.user = d1.user AND u1.host = d1.host)
|
WHERE u1.plugin IN ('', 'mysql_native_password')
|
UNION
|
SELECT u.user, u.host, t.db, u.select_priv, IF(u.password <> '', u.password, u.authentication_string), u.is_role, u.default_role
|
FROM mysql.user AS u
|
LEFT JOIN mysql.tables_priv AS t
|
ON (u.user = t.user AND u.host = t.host)
|
WHERE u.plugin IN ('', 'mysql_native_password')
|
)
|
, users AS
|
(
|
SELECT t.user, t.host, t.db, t.select_priv, t.password, t.default_role AS role
|
FROM t
|
WHERE t.is_role <> 'Y'
|
UNION
|
SELECT u.user, u.host, t.db, t.select_priv, u.password, r.role
|
FROM t
|
JOIN users AS u ON (t.user = u.role)
|
LEFT JOIN mysql.roles_mapping AS r ON (t.user = r.user)
|
)
|
SELECT DISTINCT t.user, t.host, t.db, t.select_priv, t.password
|
FROM users AS t
|
WHERE t.user <> 'root'
|
;
|
E.g. wiht a privileges database with the following row counts:
mysql.user - 410
|
mysql.roles_mapping - 177
|
mysql.db - 442
|
mysql.tables_priv - 24
|
the query returns 1488 rows, and runs for about two seconds on a MariaDB 10.3 instance on an otherwise idle host, being fully CPU bound.
+------+-----------------+------------+-------+---------------+---------+---------+--------------+------+--------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+-------+---------------+---------+---------+--------------+------+--------------------------------------------------------------+
|
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 2050 | Using where; Using temporary |
|
| 4 | DERIVED | <derived2> | ALL | NULL | NULL | NULL | NULL | 2050 | Using where |
|
| 2 | DERIVED | u | ALL | NULL | NULL | NULL | NULL | 410 | Using where |
|
| 2 | DERIVED | d | ref | PRIMARY,User | PRIMARY | 180 | mysql.u.Host | 4 | Using where; Using index |
|
| 3 | UNION | u | ALL | NULL | NULL | NULL | NULL | 410 | Using where |
|
| 3 | UNION | t | ref | PRIMARY | PRIMARY | 180 | mysql.u.Host | 1 | Using where; Using index |
|
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
| 5 | RECURSIVE UNION | <derived6> | ALL | NULL | NULL | NULL | NULL | 2050 | |
|
| 5 | RECURSIVE UNION | <derived4> | ref | key0 | key0 | 241 | t.user | 10 | |
|
| 5 | RECURSIVE UNION | r | index | NULL | Host | 660 | NULL | 177 | Using where; Using index; Using join buffer (flat, BNL join) |
|
| 6 | DERIVED | u | ALL | NULL | NULL | NULL | NULL | 410 | Using where |
|
| 6 | DERIVED | d | ref | PRIMARY,User | PRIMARY | 180 | mysql.u.Host | 4 | Using where; Using index |
|
| 7 | UNION | u | ALL | NULL | NULL | NULL | NULL | 410 | Using where |
|
| 7 | UNION | t | ref | PRIMARY | PRIMARY | 180 | mysql.u.Host | 1 | Using where; Using index |
|
| NULL | UNION RESULT | <union6,7> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
| NULL | UNION RESULT | <union4,5> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+------+-----------------+------------+-------+---------------+---------+---------+--------------+------+--------------------------------------------------------------+
|
As the query is running as part of the user data reload, it can be triggered quite a lot if unknown / invalid users try to connect often.
In the reported case leading to this bug report there were quite a lot of this query seen running in parallel, some already having been active for 30 seconds as all of them were competing for CPU time, so causing a massive overload situation ...
Attachments
Issue Links
- relates to
-
-
- Closed
-