Listener failure on startup - unable to fetch users

281 views

Skip to first unread message

do...@firefamily.net

unread,

Jul 17, 2015, 3:58:21 PM7/17/15

to maxs...@googlegroups.com

I am attempting to build out a MaxScale instance in a test instance but hitting production database servers. However the Listeners keep failing on startup so I am unable to use it. As far as I can tell from the documentation I have it setup correctly. Could someone please point me in the right direction so I can sort this out?

The servers are a Master-Master-Slave configuration (One master-slave in the primary data center, another master-slave in the failover data center. I am ignoring the slave in the failover data center in this configuration).

error1.log:

MariaDB Corporation MaxScale /var/log/maxscale/error1.log Fri Jul 17 15:45:59 2015
-----------------------------------------------------------------------
--- Logging is enabled.
2015-07-17 15:45:59 warning : Monitor Connect Timeout 10 is greater than monitor interval ~10 seconds, lowering to 9 seconds
2015-07-17 15:46:00 Error : Loading users for service [Read Connection Router] encountered error: [Lost connection to MySQL server during query].
2015-07-17 15:46:00 Error : Unable to load users from 0.0.0.0:4008 for service Read Connection Router.
2015-07-17 15:46:00 Error : Failed to start service 'Read Connection Router'.
2015-07-17 15:46:01 Error : Loading users for service [RW Split Router] encountered error: [Lost connection to MySQL server during query].
2015-07-17 15:46:01 Error : Unable to load users from 0.0.0.0:3306 for service RW Split Router.
2015-07-17 15:46:01 Error : Failed to start service 'RW Split Router'.

messages1.log:

MariaDB Corporation MaxScale /var/log/maxscale/messages1.log Fri Jul 17 15:45:59 2015
-----------------------------------------------------------------------
--- Logging is enabled.
2015-07-17 15:45:59 Configuration file: /etc/maxscale.cnf
2015-07-17 15:45:59 Log directory: /var/log/maxscale//
2015-07-17 15:45:59 Data directory: /var/lib/maxscale/data/data26807
2015-07-17 15:45:59 Module directory: /usr/lib64/maxscale
2015-07-17 15:45:59 Service cache: /var/cache/maxscale/
2015-07-17 15:45:59 --- Logging to file is enabled --
2015-07-17 15:45:59 Initialise CLI router module V1.0.0.
2015-07-17 15:45:59 Loaded module cli: V1.0.0 from /usr/lib64/maxscale/libcli.so
2015-07-17 15:45:59 Initialise debug CLI router module V1.1.1.
2015-07-17 15:45:59 Loaded module debugcli: V1.1.1 from /usr/lib64/maxscale/libdebugcli.so
2015-07-17 15:45:59 Initializing statemend-based read/write split router module.
2015-07-17 15:45:59 Loaded module readwritesplit: V1.0.2 from /usr/lib64/maxscale/libreadwritesplit.so
2015-07-17 15:45:59 Initialise readconnroute router module V1.1.0.
2015-07-17 15:45:59 Loaded module readconnroute: V1.1.0 from /usr/lib64/maxscale/libreadconnroute.so
2015-07-17 15:45:59 Initialise the Multi-Master Monitor module V1.1.1.
2015-07-17 15:45:59 Loaded module mmmon: V1.1.1 from /usr/lib64/maxscale/libmmmon.so
2015-07-17 15:45:59 MariaDB Corporation MaxScale 1.2.0 (C) MariaDB Corporation Ab 2013-2014
2015-07-17 15:45:59 MaxScale is running in process 26807
2015-07-17 15:45:59 Encrypted password file /var/lib/maxscale//.secrets can't be accessed (No such file or directory). Password encryption is not used.
2015-07-17 15:46:01 Loaded module telnetd: V1.0.1 from /usr/lib64/maxscale/libtelnetd.so
2015-07-17 15:46:01 Listening telnet connections at 0.0.0.0:4442
2015-07-17 15:46:01 Loaded module maxscaled: V1.0.0 from /usr/lib64/maxscale/libmaxscaled.so
2015-07-17 15:46:01 Listening maxscale connections at 0.0.0.0:6603
2015-07-17 15:46:01 Started MaxScale log flusher.
2015-07-17 15:46:01 MaxScale started with 4 server threads.

maxadmin checks:

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
per7-db01 | 10.140.160.32 | 3306 | 0 | Master, Running
per7-db02 | 10.140.160.37 | 3306 | 0 | Slave, Running
per5-db01 | 10.140.20.32 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> list services
Services.
--------------------------+----------------------+--------+---------------
Service Name | Router Module | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Read Connection Router | readconnroute | 0 | 0
RW Split Router | readwritesplit | 0 | 0
Debug Interface | debugcli | 1 | 1
CLI | cli | 2 | 2
--------------------------+----------------------+--------+---------------

MaxScale> list modules
Modules.
----------------+-------------+---------+-------+-------------------------
Module Name | Module Type | Version | API | Status
----------------+-------------+---------+-------+-------------------------
maxscaled | Protocol | V1.0.0 | 1.0.0 | GA
telnetd | Protocol | V1.0.1 | 1.0.0 | GA
mmmon | Monitor | V1.1.1 | 3.0.0 | Beta
readconnroute | Router | V1.1.0 | 1.0.0 | GA
readwritesplit | Router | V1.0.2 | 1.0.0 | GA
debugcli | Router | V1.1.1 | 1.0.0 | GA
cli | Router | V1.0.0 | 1.0.0 | GA
----------------+-------------+---------+-------+-------------------------

MaxScale> show servers
Server 0x1f75480 (per7-db01)
Server: 10.140.160.32
Status: Master, Running
Protocol: MySQLBackend
Port: 3306
Server Version: 5.5.43-37.2-log
Node Id: 5
Master Id: 51
Repl Depth: 0
Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0
Server 0x1f753d0 (per7-db02)
Server: 10.140.160.37
Status: Slave, Running
Protocol: MySQLBackend
Port: 3306
Server Version: 5.5.40-36.1-log
Node Id: 6
Master Id: 5
Repl Depth: 1
Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0
Server 0x1f67340 (per5-db01)
Server: 10.140.20.32
Status: Slave, Running
Protocol: MySQLBackend
Port: 3306
Server Version: 5.5.43-37.2-log
Node Id: 51
Master Id: 5
Repl Depth: 1
Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0

/etc/maxscale.cnf:

[maxscale]
threads=4
log_messages=1
log_trace=1
log_debug=1

[Multi Master Monitor]
type=monitor
module=mmmon
servers=per7-db01,per7-db02,per5-db01
user=maxscale
passwd=********
monitor_interval=10000
backend_connect_timeout=10
backend_read_timeout=8
backend_write_timeout=8
detect_replication_lag=1
detect_stale_master=0

[qla]
type=filter
module=qlafilter
options=/tmp/QueryLog

[fetch]
type=filter
module=regexfilter
match=fetch
replace=select

[Read Connection Router]
type=service
router=readconnroute
servers=per7-db01,per7-db02,per5-db01
user=maxscale
passwd=********
router_options=slave

[RW Split Router]
type=service
router=readwritesplit
servers=per7-db01,per7-db02,per5-db01
user=maxscale
passwd=********
#use_sql_variables_in=
#max_slave_connections=100%
max_slave_replication_lag=90
#router_options=slave_selection_criteria=
#filters=fetch|qla
#filters=qla
enable_root_user=on

[Debug Interface]
type=service
router=debugcli

[CLI]
type=service
router=cli

[Read Connection Listener]
type=listener
service=Read Connection Router
protocol=MySQLClient
port=4008

[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
#port=4006
port=3306

[Debug Listener]
type=listener
service=Debug Interface
protocol=telnetd
#address=127.0.0.1
port=4442

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
#address=localhost
port=6603

[per7-db01]
type=server
address=10.140.160.32
port=3306
protocol=MySQLBackend

[per7-db02]
type=server
address=10.140.160.37
port=3306
protocol=MySQLBackend

[per5-db01]
type=server
address=10.140.20.32
port=3306
protocol=MySQLBackend

I reviewed the code and found what I thought was the command that appears to drive the error I'm getting, and I got this result when logged in manually with the maxscale user that was created:

mysql> SELECT COUNT(1) AS nusers_db FROM (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 AND user.user <> '' ORDER BY host DESC) AS tbl_count;
+-----------+
| nusers_db |
+-----------+
| 9795 |
+-----------+
1 row in set (6.57 sec)

For reference, this was in the code on github:

#define LOAD_MYSQL_USERS_WITH_DB_QUERY "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 AND user.user <> ''" MYSQL_USERS_WITH_DB_ORDER

#define MYSQL_USERS_WITH_DB_COUNT "SELECT COUNT(1) AS nusers_db FROM (" LOAD_MYSQL_USERS_WITH_DB_QUERY ") AS tbl_count"