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.1 and newer

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 and then 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.