MaxScale Troubleshooting

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

Authentication Errors

Access Denied

If you are receiving authentication errors like this:

ERROR 1045 (28000): Access denied for user 'bob'@'office' (using password: YES)

Make sure you create users for both 'bob'@'office' and 'bob'@'maxscale'. The host 'office' is where the client is attempting to connect from and 'maxscale' is the host where MaxScale is installed.

Verifying that a user is allowed to connect

  • MaxScale connection
    1. SSH to the server where MaxScale is installed
    2. Connect to MariaDB
    3. Check output of SHOW GRANTS
  • Client connection
    1. SSH to theserver where client is connecting from
    2. Connect to MariaDB
    3. Check output of SHOW GRANTS

Checking MaxScale has correct grants

Service Grants

Make sure that the MaxScale services have a user configured and that it has the correct grants.

CREATE USER 'maxscale'@'maxscalehost' IDENTIFIED BY 'maxscale-password';
GRANT SELECT ON mysql.user TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.db TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'maxscalehost';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'maxscalehost';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'maxscalehost';

Monitor Grants

The monitor user requires different grants than the service user and each monitor type requires different grants.

SSL/TLS Errors on connect

If you are using self-signed certificates, turn off ssl_verify_peer_certificate. This skips the certificate validation that OpenSSL does when it connects to a server.

Loaded 0 MySQL users

If the databases are configured with init_connect = 'SET NAMES utf8mb4' , then MaxScale will receive the following error:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '<>'

This will result in zero users being loaded and all authentication attempts will fail. To prevent this, remove the init_connect line from my.cnf.

Other Errors

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

Authentication queries

MaxScale 2.5.0 and newer

MaxScale 2.5.0 simplified the user loading by moving the processing into MaxScale. As a result of this, MaxScale 2.5.0 does the same queries for all server versions:

SELECT * FROM mysql.user;
SELECT DISTINCT * FROM (
    (SELECT a.user, a.host, a.db FROM mysql.db AS a) UNION 
    (SELECT a.user, a.host, a.db FROM mysql.tables_priv AS a) UNION 
    (SELECT a.user, a.host, a.db FROM mysql.columns_priv AS a) ) AS c;
SELECT a.user, a.host, a.role FROM mysql.roles_mapping AS a;
SELECT DISTINCT a.user, a.host FROM mysql.proxies_priv AS a  WHERE a.proxied_host <> '' AND a.proxied_user <> '';

MaxScale 2.2.14 to MaxScale 2.5.0

This query is used with MariaDB versions 10.2.2 and newer. Older server versions use the 2.2.10 query.

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) 
    WHERE u.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;

MaxScale 2.2.10 to MaxScale 2.2.13

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.

System Errors

Failed to write message: 11, Resource temporarily unavailable

Starting with MaxScale 2.1, MaxScale can log the Failed to write message: 11, Resource temporarily unavailable message under extremely intensive workloads (see MXS-1983). To correct it increase the pipe buffer size from the default 1MB to a higher value. At least 8MB is recommended and should be increased until the message stops appearing.

To set the pipe buffer size, execute the following command.

sudo sysctl -w fs.pipe-max-size=8388608

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.

Systemd

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

SysV

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

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.

Binlogrouter

Commands not Working

Make sure you are connecting on the port where the binlogrouter is listening. A common mistake is to connect to a readwritesplit or readconnroute port and execute the replication configuration commands there.

MaxScale CDC: Avrorouter

For most problems, resetting the conversion state is the solution. If the conversion repeatedly stops at a certain point, please open a bug report.

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.

Access denied to CDC interface

Create the user with maxadmin call command cdc add_user <service name> <user> <password> or maxctrl call command cdc add_user <service name> <user> <password>.

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.