[MXS-3002] Upgrading mariadb/galera cluster 10.4.12->10.4.13 causing any user authentication to fail Created: 2020-05-21  Updated: 2020-12-30  Resolved: 2020-07-03

Status: Closed
Project: MariaDB MaxScale
Component/s: Authenticator
Affects Version/s: 2.4.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Andras Kovacs Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: galera
Environment:

Debian Buster (db), Debian Stretch (maxscale)

Issue Links:
Problem/Incident
is caused by MXS-3064 User loading query can run into chars... Closed

 Description   

I am using a mariadb/galera cluster. PHP app is connected to db using maxscale from localhost. When upgrading mariadb from 10.4.12 to 10.4.13 user authentication started to fail.

When starting maxscale, this is logged:

2020-05-21 09:47:07   notice : [MariaDBAuth] [Read-Write-Service] Loaded 0 MySQL users for listener 'Read-Write-Listener' from server 'vsqdev-db1' with checksum 0xfd7d71d9.
 
2020-05-21 09:47:07   notice : [MariaDBAuth] [Read-Write-Service] No users were loaded but 'inject_service_user' is enabled. Enabling service credentials for authentication until database users have been successfully loaded.

All authentication request raises these logs:

2020-05-21 09:59:18   warning: (3) [MariaDBAuth] Read-Write-Service: login attempt for user 'devvsq'@[127.0.0.1]:64892 to database 'mydb', authentication failed. User not found.
 
2020-05-21 09:59:30   warning: (4) [Read-Write-Service] Refresh rate limit (once every 30 seconds) exceeded for load of users' table.
 
2020-05-21 09:59:30   warning: (4) [MariaDBAuth] Read-Write-Service: login attempt for user 'devvsq'@[127.0.0.1]:64900 to database 'mydb', authentication failed. User not found.

On DB side, these are logged:

2020-05-20 12:13:29 82 [Warning] Aborted connection 82 to db: 'mydb' user: 'devvsq' host: 'myhost.com' (Got an error reading communication packets)
 
2020-05-20 13:24:48 132 [Warning] Aborted connection 132 to db: 'mydb' user: 'devvsq' host: 'myhost.com' (Got timeout reading communication packets)

It is important to note, that it had been a working system and mariadb upgrade caused it to fail. I tried many auth options in maxscale but have no idea what is a workaround. DB users are both available tied to "localhost" and "%" but this does not help.

Interestingly, the only auth working is with maxscale ("vsqmaxscale") user, nothing else.

my maxscale config:

[maxscale]
 
threads=auto
 
 
 
[vsqdev-db1]
 
type=server
 
address=172.18.3.40
 
port=3306
 
protocol=MariaDBBackend
 
 
 
[vsqdev-db2]
 
type=server
 
address=172.18.3.41
 
port=3306
 
protocol=MariaDBBackend
 
 
 
[Read-Write-Service]
 
type=service
 
router=readwritesplit
 
servers=vsqdev-db1,vsqdev-db2
 
user=vsqmaxscale
 
password=<snip>
 
 
 
[Read-Write-Listener]
 
type=listener
 
service=Read-Write-Service
 
protocol=MariaDBClient
 
address=127.0.0.1
 
port=3307
 
 
 
[Galera-Monitor]
 
type=monitor
 
module=galeramon
 
servers=vsqdev-db1,vsqdev-db2
 
user=vsqmaxscale
 
password=<snip>
 
monitor_interval=10000
 
use_priority=true
 
 
 
[MaxAdmin-Service]
 
type=service
 
router=cli
 
 
 
[MaxAdmin-Listener]
 
type=listener
 
service=MaxAdmin-Service
 
protocol=maxscaled
 
socket=default



 Comments   
Comment by markus makela [ 2020-05-22 ]

Does the SQL query listed here return an error of some sorts? From the looks of it, I'd say that it's quite likely that there is something wrong on the database side that causes the query to malfunction.

Have you tried this on any other systems with the same set of versions?

Comment by Andras Kovacs [ 2020-05-22 ]

Markus, thanks for the feedback.

Directly, on the database node using mysql client the query works:

+-----------------+-----------+----------------+-------------+-------------------------------------------+
 
| user            | host      | db             | select_priv | password                                  |
 
+-----------------+-----------+----------------+-------------+-------------------------------------------+
 
| root            | localhost | NULL           | Y           | <snip>                                    |
 
| mysql           | localhost | NULL           | Y           |                                           |
 
| devvsq          | %         | mydb| N           |                                           |
 
| sstuser         | localhost | NULL           | N           |                                           |
 
| root            | %         | NULL           | Y           |                                           |
 
| mysqld_exporter | localhost | NULL           | Y           |                                           |
 
| vsqmaxscale     | %         | mysql          | N           |                                           |
 
| devvsq_wowza    | %         | NULL           | N           |                                           |
 
| mariadb.sys     | localhost | NULL           | N           |                                           |
 
| devvsq          | localhost | mydb| N           |                                           |
 
| devvsq          | %         | NULL           | N           |                                           |
 
| devvsq_wowza    | %         | mydb| N           |                                           |
 
| mariadb.sys     | localhost | mysql          | N           |                                           |
 
| devvsq          | localhost | NULL           | N           |                                           |
 
+-----------------+-----------+----------------+-------------+-------------------------------------------+

However, when connecting using maxscale user, through maxscale from the other node it fails!

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 9
 
Server version: 10.4.13-MariaDB-1:10.4.13+maria~buster-log mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> 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;
 
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '<>'

This should be causing the problem. Although, I do not get why illegal mix of collations occur here...

Comment by markus makela [ 2020-05-22 ]

Have you configured any custom server or client character sets in the MariaDB configuration files? It's possible that one of them is causing this conflict.

Comment by Andras Kovacs [ 2020-05-22 ]

Sure, I have these in my.cnf

[mysqld]
 
user = mysql
 
datadir = /var/lib/mysql
 
character-set-server = utf8mb4
 
collation-server = utf8mb4_unicode_ci
 
character-set-client-handshake = FALSE
 
init_connect = 'SET character_set_system = utf8mb4'
 
init_connect = 'SET character_set_connection = utf8mb4'
 
init_connect = 'SET character_set_database = utf8mb4'
 
init_connect = 'SET character_set_results = utf8mb4'
 
init_connect = 'SET collation_database = utf8mb4_unicode_ci'
 
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'
 
init_connect = 'SET NAMES utf8mb4'

Let me know please what could be causing it... thanks a lot.

Comment by markus makela [ 2020-05-22 ]

I bet if you comment out the init_connect lines it will work. Those would also be done for the connection done by MaxScale which is likely to cause the problem.

Comment by Andras Kovacs [ 2020-05-22 ]

Markus, thanks. Yes, it fixed the problem. I figured out that this line caused the problem

init_connect = 'SET NAMES utf8mb4'

Comment by markus makela [ 2020-05-25 ]

I think this will be fixed automatically with the upcoming 2.5 release of MaxScale and the rewrite of the authentication code. For 2.4 and earlier releases, we can document that this should be avoided in case of problems like these.

We shold also execute a SET NAMES latin1 to make sure we're using the charset we want.

Comment by Andras Kovacs [ 2020-05-25 ]

thanks a lot

Comment by markus makela [ 2020-07-03 ]

I'll close this as Incomplete as it's both a bug in the MaxScale code and a database configuration related issue. I've opened MXS-3064 for the problem that was found in the process of investigating this issue.

Generated at Thu Feb 08 04:18:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.