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

Make sure that MaxScale has a user configured and that it has the following grants.

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.roles_mapping TO 'maxscale'@'maxscalehost';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'maxscalehost';

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

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, u.password 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, u.password AS password, 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;

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.

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