[MXS-1990] Why the maxscale_schema together with the heartbeat table is not created with a creation command? Created: 2018-07-28  Updated: 2018-07-31  Resolved: 2018-07-31

Status: Closed
Project: MariaDB MaxScale
Component/s: mariadbmon
Affects Version/s: 2.2.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Wagner Bianchi (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

Folks,

I prepared the below commands after starting a Maxscale 2.2.9 with a necessary configuration file. I have seen an issue after creating the monitor with detect_replication_lag=1 and adding the first server under the service (defined on the basic configuration file) and the monitor with the previously mentioned variable to detect slaves lagging.

#: the basic configuration file at /etc/maxscale.cnf
 
[maxscale]
 
threads=auto
 
users_refresh_time=120
 
log_augmentation=1
 
ms_timestamp=1
 
syslog=1
 
 
 
[rwsplit-service]
 
type=service
 
router=readwritesplit
 
user=maxusr
 
password=2C894F278CEC5B974966EC969E006E37
 
 
 
[CLI]
 
type=service
 
router=cli
 
 
 
[CLI-Unix-Listener]
 
type=listener
 
service=CLI
 
protocol=maxscaled
 
socket=default
 
 
 
[CLI-Inet-Listener]
 
type=listener
 
service=CLI
 
protocol=maxscaled
 
address=localhost
 
port=6603

And then, I start the maxscale.service, see log events below:

MariaDB MaxScale  /var/log/maxscale/maxscale.log  Sat Jul 28 21:05:33 2018
 
----------------------------------------------------------------------------
 
2018-07-28 21:05:33   notice : (change_cwd): Working directory: /var/log/maxscale
 
2018-07-28 21:05:33   notice : (init_sqlite3): The collection of SQLite memory allocation statistics turned off.
 
2018-07-28 21:05:33   notice : (init_sqlite3): Threading mode of SQLite set to Multi-thread.
 
2018-07-28 21:05:33   notice : (main): MariaDB MaxScale 2.2.9 started
 
2018-07-28 21:05:33   notice : (main): MaxScale is running in process 10455
 
2018-07-28 21:05:33   notice : (main): Configuration file: /etc/maxscale.cnf
 
2018-07-28 21:05:33   notice : (main): Log directory: /var/log/maxscale
 
2018-07-28 21:05:33   notice : (main): Data directory: /var/lib/maxscale
 
2018-07-28 21:05:33   notice : (main): Module directory: /usr/lib64/maxscale
 
2018-07-28 21:05:33   notice : (main): Service cache: /var/cache/maxscale
 
2018-07-28 21:05:33   notice : (config_load_single_file): Loading /etc/maxscale.cnf.
 
2018-07-28 21:05:33.293   notice : (mxs_log_set_highprecision_enabled): highprecision logging is enabled.
 
2018-07-28 21:05:33.293   notice : (is_directory): /etc/maxscale.cnf.d does not exist, not reading.
 
2018-07-28 21:05:33.293   notice : [cli] (mxs_get_module_object): Initialise CLI router module
 
2018-07-28 21:05:33.293   notice : (load_module): Loaded module cli: V1.0.0 from /usr/lib64/maxscale/libcli.so
 
2018-07-28 21:05:33.294   notice : [readwritesplit] (mxs_get_module_object): Initializing statement-based read/write split router module.
 
2018-07-28 21:05:33.294   notice : (load_module): Loaded module readwritesplit: V1.1.0 from /usr/lib64/maxscale/libreadwritesplit.so
 
2018-07-28 21:05:33.294   notice : (load_module): Loaded module maxscaled: V2.0.0 from /usr/lib64/maxscale/libmaxscaled.so
 
2018-07-28 21:05:33.294   notice : (load_module): Loaded module MaxAdminAuth: V2.1.0 from /usr/lib64/maxscale/libmaxadminauth.so
 
2018-07-28 21:05:33.294   notice : (qc_setup): No query classifier specified, using default 'qc_sqlite'.
 
2018-07-28 21:05:33.294   notice : (load_module): Loaded module qc_sqlite: V1.0.0 from /usr/lib64/maxscale/libqc_sqlite.so
 
2018-07-28 21:05:33.295   notice : (service_launch_all): Starting a total of 2 services...
 
2018-07-28 21:05:33.295   warning: (serviceStartAllPorts): Service 'rwsplit-service' has no listeners defined.
 
2018-07-28 21:05:33.295   notice : (service_launch_all): Service 'rwsplit-service' started (1/2)
 
2018-07-28 21:05:33.295   notice : (dcb_listen): Listening for connections at [/tmp/maxadmin.sock]:0 with protocol MaxScale Admin
 
2018-07-28 21:05:33.295   notice : (dcb_listen): Listening for connections at [localhost]:6603 with protocol MaxScale Admin
 
2018-07-28 21:05:33.296   notice : (service_launch_all): Service 'CLI' started (2/2)
 
2018-07-28 21:05:33.301   notice : (log_flush_cb): Started MaxScale log flusher.
 
2018-07-28 21:05:33.303   notice : (main): Started REST API on [127.0.0.1]:8989
 
2018-07-28 21:05:33.303   notice : (main): MaxScale started with 1 worker threads, each with a stack size of 8388608 bytes.

And then, I ran the below commands:

#: creating the monitor
 
maxadmin create monitor replication-cluster-monitor mariadbmon
 
 
 
#: alter the monitor to add user and passwd for the user accessing the backends
 
maxadmin alter monitor replication-cluster-monitor user=maxmon password=341EFE7FA99C09DB796C0C39E6620FA1 monitor_interval=1000 replication_user=mariadb replication_password=0A8C9D1F240571C0C7657E8AB482E05D failcount=5 auto_failover=true auto_rejoin=true detect_replication_lag=1 
 
 
#: restart the monitor we created
 
maxadmin restart monitor replication-cluster-monitor
 
 
 
#: create the listener on port 3306
 
maxadmin create listener rwsplit-service replication-rwsplit-listener 0.0.0.0 3306
 
 
 
#: creating the servers
 
maxadmin create server n01 10.136.85.157 3306
 
maxadmin create server n02 10.136.87.40  3306
 
maxadmin create server n03 10.136.80.203 3306
 
maxadmin create server n04 10.136.83.198 3306
 
maxadmin create server n05 10.136.86.152 3306
 
 
 
#: adding the servers under the monitor and service
 
maxadmin add server n01 replication-cluster-monitor rwsplit-service
 
...

When adding the server n01, the very first server out of 5, I see the below log events at the maxscale log:

#: the server is now master, as it's the first one and the unique server added until now
 
 2018-07-28 21:06:20.183   notice : (mon_log_state_change): Server changed state: n01[10.136.85.157:3306]: new_master. [Running] -> [Master, Running]
 
 
 
#: and then, the below is just a blizard 
2018-07-28 21:06:20.185   error  : [mariadbmon] (set_master_heartbeat): Error creating maxscale_schema.replication_heartbeat table in Master server: Access denied for user 'maxmon'@'10.136.72.138' to database 'maxscale_schema'
 
2018-07-28 21:06:21.189   error  : [mariadbmon] (set_master_heartbeat): Error creating maxscale_schema.replication_heartbeat table in Master server: Access denied for user 'maxmon'@'10.136.72.138' to database 'maxscale_schema'
 
2018-07-28 21:06:22.194   error  : [mariadbmon] (set_master_heartbeat): Error creating maxscale_schema.replication_heartbeat table in Master server: Access denied for user 'maxmon'@'10.136.72.138' to database 'maxscale_schema'
 
2018-07-28 21:06:23.199   error  : [mariadbmon] (set_master_heartbeat): Error creating maxscale_schema.replication_heartbeat table in Master server: Access denied for user 'maxmon'@'10.136.72.138' to database 'maxscale_schema'
 
2018-07-28 21:06:24.204   error  : [mariadbmon] (set_master_heartbeat): Error creating maxscale_schema.replication_heartbeat table in Master server: Access denied for user 'maxmon'@'10.136.72.138' to database 'maxscale_schema'
 
2018-07-28 21:06:25.209   error  : [mariadbmon] (set_master_heartbeat): Error creating maxscale_schema.replication_heartbeat table in Master server: Access denied for user 'maxmon'@'10.136.72.138' to database 'maxscale_schema'
 
2018-07-28 21:06:26.213   error  : [mariadbmon] (set_master_heartbeat): Error creating maxscale_schema.replication_heartbeat table in Master server: Access denied for user 'maxmon'@'10.136.72.138' to database 'maxscale_schema'

The user 'maxmon'@'10.136.72.138' privileges:

n01.bianchi.com [(none)]> show grants for maxmon@'10.136.72.138'\G
 
*************************** 1. row ***************************
 
Grants for maxmon@10.136.72.138: GRANT SUPER, REPLICATION CLIENT ON *.* TO 'maxmon'@'10.136.72.138' IDENTIFIED BY PASSWORD '*7297C3E22DEB91303FC493303A8158AD4231F486'
 
*************************** 2. row ***************************
 
Grants for maxmon@10.136.72.138: GRANT SELECT ON `maxscale_schema`.* TO 'maxmon'@'10.136.72.138'
 
2 rows in set (0.00 sec)

What I see here: the user for the mariadbmon can access the backend as it's now master and that user was used to polling the backend state, it has the right privileges, if not more than needed, but, something is happening when enabling that slave lag detection.

That's the current environment topology:

n01.bianchi.com [(none)]> show slave hosts;
 
+-----------+-----------------+------+-----------+
 
| Server_id | Host            | Port | Master_id |
 
+-----------+-----------------+------+-----------+
 
|         5 | n05.bianchi.com | 3306 |         1 |
 
|         4 | n04.bianchi.com | 3306 |         1 |
 
|         3 | n03.bianchi.com | 3306 |         1 |
 
|         2 | n02.bianchi.com | 3306 |         1 |
 
+-----------+-----------------+------+-----------+
 
4 rows in set (0.00 sec)

Let me know please if you need any other test or information, cheers!

 Comments   
Comment by markus makela [ 2018-07-30 ]

The user appears to be missing the required grants: https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-monitor/#detect_replication_lag

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