MaxScale Troubleshooting

You are viewing an old version of this article. View the current version here.

Authentication errors

For all authentication erros, executing the following query will show what MaxScale sees and could help you understand if some grants are missing.

MaxScale 2.2.10 and newer

This query is used with MariaDB versions 10.1.1 and newer.

SELECT t.user, t.host, t.db, t.select_priv, t.password FROM 
( 
    SELECT u.user, u.host, d.db, u.select_priv, u.password AS password, u.is_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, u.password AS password, u.is_role 
    FROM mysql.user AS u LEFT JOIN mysql.tables_priv AS t 
    ON (u.user = t.user AND u.host = t.host) 
) AS t 
-- Discard any users that are roles
WHERE t.is_role <> 'Y'
UNION 
-- Then select all users again
SELECT r.user, r.host, u.db, u.select_priv, t.password FROM 
( 
    SELECT u.user, u.host, d.db, u.select_priv, u.password AS password, 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, u.password AS password, 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) 
) AS t 
-- Join it to the roles_mapping table to only have users with roles
JOIN mysql.roles_mapping AS r 
ON (r.user = t.user AND r.host = t.host) 
-- Then join it into itself to get the privileges of the role with the name of the user
JOIN 
( 
    SELECT u.user, u.host, d.db, u.select_priv, u.password AS password, u.is_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, u.password AS password, u.is_role 
    FROM mysql.user AS u LEFT JOIN mysql.tables_priv AS t 
    ON (u.user = t.user AND u.host = t.host) 
) AS u 
ON (u.user = r.role AND u.is_role = 'Y') 
-- We only care about users that have a default role assigned
WHERE t.default_role = u.user;

MaxScale 2.1 up to 2.2.10

This query is also used when a pre-10.1.1 MariaDB version or a MySQL variant is used.

SELECT u.user, u.host, d.db, u.select_priv, u.password
    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, u.password
    FROM mysql.user AS u LEFT JOIN mysql.tables_priv AS t
    ON (u.user = t.user AND u.host = t.host);

MaxScale 2.0 and 1.4

SELECT DISTINCT
    user.user AS user,
    user.host AS host,
    user.password AS password,
    concat(user.user,user.host,user.password,
      IF((user.Select_priv+0)||find_in_set('Select',Coalesce(tp.Table_priv,0)),'Y','N') ,
      COALESCE( db.db,tp.db, '')) AS userdata,
    user.Select_priv AS anydb,
    COALESCE( db.db,tp.db, NULL) AS db
    FROM
    mysql.user LEFT JOIN
    mysql.db ON user.user=db.user AND user.host=db.host LEFT JOIN
    mysql.tables_priv tp ON user.user=tp.user AND user.host=tp.host
    WHERE user.user IS NOT NULL AND user.user <> '';

Pre-1.4.0

SELECT
    user.user AS user,
    user.host AS host,
    user.password AS password,
    concat(user.user,user.host,user.password,user.Select_priv,IFNULL(db,'')) AS userdata,
    user.Select_priv AS anydb,
    db.db AS db
    FROM mysql.user LEFT JOIN mysql.db
    ON user.user=db.user AND user.host=db.host
    WHERE user.user IS NOT NULL;

IF you are suspecting some users are lacking permissions, execute the following and check that the right grants are given.

SHOW GRANTS FOR user@IP

Executing SHOW GRANTS though MaxScale will tell you which user and which grans are being used.

Access denied errors for user root!

If you want to connect as root, you need to set enable_root_user=1 in the service declaration within your MaxScale.cnf.

Access denied for user@localhost

If you have added your users with wildcard host in MariaDB, you may try the localhost_match_wildcard_host=1 option in the service declaration within your maxscale.cnf. Also read the MaxScale documentation regarding permissions.

Access denied on databases/tables containing underscores

There seems to be a bug for databases containing underscores. Connect as root and use "SHOW GRANTS FOR user".

GRANT SELECT ON `my\_database`.* TO 'user'@'%' <-- bad

GRANT SELECT ON `my_database`.* TO 'user'@'%' <-- good

If you got a grant containing a escaped underscore, you can add the `strip_db_esc=true` parameter to the service to automatically strip escape characters or just replace the grant with a unescaped one.

If you have a lot of grants you want to update, you can use the pt-show-grants utility from percona toolkit like that:

pt-show-grants -pyourpw | grep '\_' | sed 's/\_/_/g' > fixedgrants.sql

after reviewing fixedgrants.sql, you can apply them.

Error 23: Too many open files

This is a common error when system limits for open files is too low. The fix to this is to increase the limits.

SysV

Edit the values in /etc/security/limits.conf.

Systemd

Add LimitNOFILE=<number of files> under the [Service] section in /usr/lib/systemd/system/maxscale.service.

Listeners not starting

While having multiple listeners, it takes time to load all user data from the backend servers. The default value of TimeoutStartSec in /usr/lib/systemd/system/maxscale.service for MaxScale is 120. If all listeners do not start before Systemd times out, increase the value from 120 to 360.

Avrorouter

For most problems, resetting the conversion state helps.

Resetting conversion state

  • Stop MaxScale
  • Remove the avro.index and avro-conversion.ini files along with any generated .avro files from the director where the Avro files are stored
  • Start MaxScale

Binlog files are not found

Make sure the start_index parameter is set to the lowest binlog file number. For example, to start from mariadb-bin-000005, set start_index=5.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.