|
Thanks for reporting.
Which version of MariaDB are you using?
|
|
Server version: 10.2.14-MariaDB-log openSUSE package
|
|
It appears there is some problem with the users-query MaxScale uses against MariaDB.
Can you try the below sql directly against MariaDB (e.g. using command line mysql). You can uncomment one or the other of the first two lines to get some diagnostics from MariaDB.
-- EXPLAIN EXTENDED
|
-- ANALYZE
|
WITH recursive t
|
AS
|
(
|
SELECT u.user,
|
u.host,
|
d.db,
|
u.select_priv,
|
IF(u.password <> '', u.password, u.authentication_string) AS password,
|
u.is_role,
|
u.default_role
|
FROM mysql.user AS u
|
LEFT JOIN mysql.db AS d
|
ON (
|
u.user = d.user
|
AND u.host = d.host)
|
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)), 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'
|
;
|
|
|
zd-dbsrv-2:~ # mysql mysql < test.sql
ERROR 5 (HY000) at line 3: Out of memory (Needed 306653464 bytes)
zd-dbsrv-2:~ # mysql mysql < test.sql
ERROR 5 (HY000) at line 3: Out of memory (Needed 941291032 bytes)
zd-dbsrv-2:~ # mysql mysql < test.sql
ERROR 5 (HY000) at line 3: Out of memory (Needed 877424408 bytes)
EXPLAIN EXTENDED:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 725 100.00 Using where; Using temporary
4 DERIVED <derived2> ALL NULL NULL NULL NULL 725 100.00 Using where
2 DERIVED u ALL NULL NULL NULL NULL 145 100.00 Using where
2 DERIVED d ref PRIMARY,User User 240 mysql.u.User 2 100.00 Using where
3 UNION u ALL NULL NULL NULL NULL 145 100.00 Using where
3 UNION t ref PRIMARY PRIMARY 180 mysql.u.Host 3 100.00 Using where; Using index
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
5 RECURSIVE UNION r system NULL NULL NULL NULL 0 0.00 const row not found
5 RECURSIVE UNION <derived6> ALL NULL NULL NULL NULL 725 100.00
5 RECURSIVE UNION <derived4> ref key0 key0 240 t.user 10 100.00
6 DERIVED u ALL NULL NULL NULL NULL 145 100.00
6 DERIVED d ref PRIMARY,User User 240 mysql.u.User 2 100.00 Using where
7 UNION u ALL NULL NULL NULL NULL 145 100.00
7 UNION t ref PRIMARY PRIMARY 180 mysql.u.Host 3 100.00 Using where; Using index
NULL UNION RESULT <union6,7> ALL NULL NULL NULL NULL NULL NULL
NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL
ANALYZE:
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 725 20155.00 100.00 99.31 Using where; Using temporary
4 DERIVED <derived2> ALL NULL NULL NULL NULL 725 306.00 100.00 100.00 Using where
2 DERIVED u ALL NULL NULL NULL NULL 145 145.00 100.00 100.00 Using where
2 DERIVED d ref PRIMARY,User User 240 mysql.u.User 2 1.37 100.00 80.81 Using where
3 UNION u ALL NULL NULL NULL NULL 145 145.00 100.00 100.00 Using where
3 UNION t ref PRIMARY PRIMARY 180 mysql.u.Host 3 7.04 100.00 1.47 Using where; Using index
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 306.00 NULL NULL
5 RECURSIVE UNION r system NULL NULL NULL NULL 0 NULL 0.00 NULL const row not found
5 RECURSIVE UNION <derived6> ALL NULL NULL NULL NULL 725 306.00 100.00 100.00
5 RECURSIVE UNION <derived4> ref key0 key0 240 t.user 10 10077.50 100.00 100.00
6 DERIVED u ALL NULL NULL NULL NULL 145 145.00 100.00 100.00
6 DERIVED d ref PRIMARY,User User 240 mysql.u.User 2 1.37 100.00 80.81 Using where
7 UNION u ALL NULL NULL NULL NULL 145 145.00 100.00 100.00
7 UNION t ref PRIMARY PRIMARY 180 mysql.u.Host 3 7.04 100.00 1.47 Using where; Using index
NULL UNION RESULT <union6,7> ALL NULL NULL NULL NULL NULL 306.00 NULL NULL
NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL
|
|
Clearly the MaxScale query does not work correctly against the MariaDB version you have. Right now I cannot verify what exactly is wrong, but the changed query (MaxScale 2.2.15 => 2.2.17) clearly caused a problem. Can I ask you to run the below query, it is from the latest MaxScale version (2.3.2). If the query works correctly, we can backport the change to a new MaxScale 2.2.x version.
There are two more options for you:
1. If the query works, you can upgrade to MaxScale 2.3.2
2. You could upgrade MariaDB to the latest 10.2 version, or even 10.3, but I cannot know if that will solve your issue.
WITH recursive t AS
|
(
|
SELECT u.user,
|
u.host,
|
d.db,
|
u.select_priv,
|
IF(u.password <> '', u.password, u.authentication_string) AS password,
|
u.is_role,
|
u.default_role FROM mysql.user AS u LEFT JOIN mysql.db AS d ON (
|
u.user = d.user
|
AND
|
u.host = d.host
|
)
|
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
|
)), 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'
|
;
|
|
|
the 2.3.2 shows the same behavior. After a fresh restart of mysql/mariadb the query works sometimes, but takes a lot of time. Sometimes there is another error message: webapp4:~ # mysql mysql < test.sql
ERROR 1815 (HY000) at line 3: Internal error: Using too big key for internal temp tables
after an upgrade of mariadb from 10.2.14 to 10.2.19 on our test system BOTH QUERIES WORK. However, we have just 10 users on the test system, on production we have about 200.
So now maxscale 2.2.17 works with mariadb 10.2.19 on our test system. We will upgrade our production environment as soon as possible, probably after Christmas.
|
|
Hello Guys,
I'm seeing the same issue when running the above queries and when starting up 2.2.17 and also starting 2.2.18. Backends version is 10.2.7-MariaDB-10.2.7+maria~xenial-log.
2018-12-17 16:27:52.940 error : [MySQLAuth] (get_users_from_server): Failed to load users from server '192.168.3.1': Out of memory (Needed 71806136 bytes)
|
2018-12-17 16:27:52.941 notice : [MySQLAuth] (mysql_auth_load_users): [replication-rwsplit-service] No users were loaded but 'inject_service_user' is enabled. Enabling service credentials for authentication until database users have been successfully loaded.
|
2018-12-17 16:27:53.038 error : [MySQLAuth] (get_users_from_server): Failed to load users from server '192': Out of memory (Needed 3307973560 bytes)
|
2018-12-17 16:27:53.040 notice : [MySQLAuth] (mysql_auth_load_users): [replication-rwsplit-service] No users were loaded but 'inject_service_user' is enabled. Enabling service credentials for authentication until database users have been successfully loaded.
|
2018-12-17 16:27:53.046 error : [MySQLAuth] (get_users_from_server): Failed to load users from server '192': Out of memory (Needed 2360249880 bytes)
|
2018-12-17 16:27:53.047 notice : [MySQLAuth] (mysql_auth_load_users): [replication-rwsplit-service] No users were loaded but 'inject_service_user' is enabled. Enabling service credentials for authentication until database users have been successfully loaded.
|
2018-12-17 16:27:53.562 error : [MySQLAuth] (get_users_from_server): Failed to load users from server '192': Internal error: Using too big key for internal temp tables
|
2018-12-17 16:27:53.565 notice : [MySQLAuth] (mysql_auth_load_users): [replication-rwsplit-service] No users were loaded but 'inject_service_user' is enabled. Enabling service credentials for authentication until database users have been successfully loaded.
|
2018-12-17 16:27:53.570 error : [MySQLAuth] (get_users_from_server): Failed to load users from server '192': Out of memory (Needed 1196207640 bytes)
|
2018-12-17 16:27:53.571 notice : [MySQLAuth] (mysql_auth_load_users): [replication-rwsplit-service] No users were loaded but 'inject_service_user' is enabled. Enabling service credentials for authentication until database users have been successfully loaded.
|
Running the above queries, I see I have an issue on MariaDB Server, which is reporting back to MaxScale the Out Of Memory issue:
ERROR 5 (HY000): Out of memory (Needed 2368507576 bytes)
|
How can we have a simpler query here so we can remove the roadblock?
Thanks, guys!
|
|
Possibly caused by MDEV-15840 which would explain why 10.2.19 works. As a workaround for older versions, the version string faking in 10.2 can be used to force MaxScale to think that it's talking to an older server: https://mariadb.com/kb/en/library/server-system-variables/#version
|
|
This was caused by a bug in MariaDB and is fixed by using different SQL for older versions. MaxScale 2.2.19 will only use CTEs with MariaDB 10.2.15 and newer.
|
|
This was due to a bug in MariaDB. See comments.
|