[MXS-1579] Maxscale 2.1.11 and Cache Filter not working well Created: 2017-12-14  Updated: 2017-12-18  Resolved: 2017-12-18

Status: Closed
Project: MariaDB MaxScale
Component/s: cache
Affects Version/s: 2.1.11
Fix Version/s: N/A

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

[root@maxscale maxscale.cnf.d]# uname -r
3.10.0-327.4.5.el7.x86_64
[root@maxscale maxscale.cnf.d]# uname -a
Linux maxscale 3.10.0-327.4.5.el7.x86_64 #1 SMP Mon Jan 25 22:07:14 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux


 Description   

Folks,

I started playing with the Cache Filter on Maxscale 2.1.11, as per the documentation, and get it going after chatting with engineers. I found that the configuration for the Cache Filter depends on the creation of a service and an additional listener, as in my configuration's case, I have one listener for the ReadWriteSplit service and now, another listener for the Cache Filter.

My complete Maxscale configuration is like below:

[root@maxscale maxscale.cnf.d]# cat *
 
[cluster-monitor]
 
type=monitor
 
module=galeramon
 
user=maxusr
 
password=3F122C31CD5556BFC93A8A4170A525BB
 
monitor_interval=1000
 
backend_connect_timeout=3
 
backend_write_timeout=2
 
backend_read_timeout=1
 
servers=prod_mariadb01,prod_mariadb02,prod_mariadb03
 
 
 
[MyCache]
 
type=filter
 
module=cache
 
storage=storage_inmemory
 
soft_ttl=30
 
hard_ttl=45
 
cached_data=thread_specific
 
selects=assume_cacheable
 
max_size=50Mi
 
#rules=
 
 
 
[MyService]
 
type=service
 
router=readwritesplit
 
user=maxusr
 
password=3F122C31CD5556BFC93A8A4170A525BB
 
filters=MyCache
 
 
 
[MyCache-listener]
 
type=listener
 
protocol=MySQLClient
 
service=MyService
 
address=0.0.0.0
 
port=4007
 
authenticator=MySQLAuth
 
 
 
[prod_mariadb01]
 
type=server
 
protocol=MySQLBackend
 
address=192.168.50.11
 
port=3306
 
authenticator=MySQLBackendAuth
 
 
 
[prod_mariadb02]
 
type=server
 
protocol=MySQLBackend
 
address=192.168.50.12
 
port=3306
 
authenticator=MySQLBackendAuth
 
 
 
[prod_mariadb03]
 
type=server
 
protocol=MySQLBackend
 
address=192.168.50.13
 
port=3306
 
authenticator=MySQLBackendAuth
 
 
 
[rwsplit-listener]
 
type=listener
 
protocol=MySQLClient
 
service=rwsplit-service
 
address=0.0.0.0
 
port=4006
 
authenticator=MySQLAuth
 
 
 
[rwsplit-service]
 
servers=prod_mariadb01,prod_mariadb02,prod_mariadb03

After starting maxscale pointing to a basic configuration, log reports the below:

MariaDB MaxScale  /var/log/maxscale/maxscale.log  Thu Dec 14 13:53:30 2017
 
----------------------------------------------------------------------------
 
2017-12-14 13:53:30   notice : Working directory: /var/log/maxscale
 
2017-12-14 13:53:30   notice : MariaDB MaxScale 2.1.11 started
 
2017-12-14 13:53:30   notice : MaxScale is running in process 4481
 
2017-12-14 13:53:30   notice : Configuration file: /root/basic.cnf
 
2017-12-14 13:53:30   notice : Log directory: /var/log/maxscale
 
2017-12-14 13:53:30   notice : Data directory: /var/lib/maxscale
 
2017-12-14 13:53:30   notice : Module directory: /usr/lib64/maxscale
 
2017-12-14 13:53:30   notice : Service cache: /var/cache/maxscale
 
2017-12-14 13:53:30   notice : Loading /root/basic.cnf.
 
2017-12-14 13:53:30   notice : The logging of informational messages has been enabled.
 
2017-12-14 13:53:30   notice : /root/basic.cnf.d does not exist, not reading.
 
2017-12-14 13:53:30   notice : Loading generated configuration files from '/var/lib/maxscale/maxscale.cnf.d'
 
2017-12-14 13:53:30   notice : Loading /var/lib/maxscale/maxscale.cnf.d/prod_mariadb01.cnf.
 
2017-12-14 13:53:30   notice : Loading /var/lib/maxscale/maxscale.cnf.d/prod_mariadb02.cnf.
 
2017-12-14 13:53:30   notice : Loading /var/lib/maxscale/maxscale.cnf.d/prod_mariadb03.cnf.
 
2017-12-14 13:53:30   notice : Loading /var/lib/maxscale/maxscale.cnf.d/rwsplit-service.cnf.
 
2017-12-14 13:53:30   notice : Loading /var/lib/maxscale/maxscale.cnf.d/rwsplit-listener.cnf.
 
2017-12-14 13:53:30   notice : Loading /var/lib/maxscale/maxscale.cnf.d/cluster-monitor.cnf.
 
2017-12-14 13:53:30   notice : Loading /var/lib/maxscale/maxscale.cnf.d/filter_cache.cnf.
 
2017-12-14 13:53:30   notice : [readwritesplit] Initializing statement-based read/write split router module.
 
2017-12-14 13:53:30   notice : Loaded module readwritesplit: V1.1.0 from /usr/lib64/maxscale/libreadwritesplit.so
 
2017-12-14 13:53:30   notice : [cache] Initialized cache module V1.0.0.
 
2017-12-14 13:53:30   notice : Loaded module cache: V1.0.0 from /usr/lib64/maxscale/libcache.so
 
2017-12-14 13:53:30   notice : [galeramon] Initialise the MySQL Galera Monitor module.
 
2017-12-14 13:53:30   notice : Loaded module galeramon: V2.0.0 from /usr/lib64/maxscale/libgaleramon.so
 
2017-12-14 13:53:30   notice : [cli] Initialise CLI router module
 
2017-12-14 13:53:30   notice : Loaded module cli: V1.0.0 from /usr/lib64/maxscale/libcli.so
 
2017-12-14 13:53:30   notice : Loaded module MySQLBackendAuth: V1.0.0 from /usr/lib64/maxscale/libMySQLBackendAuth.so
 
2017-12-14 13:53:30   notice : Loaded module MySQLAuth: V1.1.0 from /usr/lib64/maxscale/libMySQLAuth.so
 
2017-12-14 13:53:30   notice : [cache] Creating thread specific cache.
 
2017-12-14 13:53:30   notice : [storage_inmemory] Storage module created.
 
2017-12-14 13:53:30   notice : [cache] Created single threaded LRU storage.
 
2017-12-14 13:53:30   notice : [cache] Created single threaded cache.
 
2017-12-14 13:53:30   notice : [cache] Created cache per thread.
 
2017-12-14 13:53:30   info   : [maxscaled] Initialise MaxScaled Protocol module.
 
2017-12-14 13:53:30   notice : Loaded module maxscaled: V2.0.0 from /usr/lib64/maxscale/libmaxscaled.so
 
2017-12-14 13:53:30   notice : Loaded module MaxAdminAuth: V2.1.0 from /usr/lib64/maxscale/libMaxAdminAuth.so
 
2017-12-14 13:53:30   notice : No query classifier specified, using default 'qc_sqlite'.
 
2017-12-14 13:53:30   notice : Loaded module qc_sqlite: V1.0.0 from /usr/lib64/maxscale/libqc_sqlite.so
 
2017-12-14 13:53:30   info   : qc_sqlite loaded.
 
2017-12-14 13:53:30   info   : [qc_sqlite] In-memory sqlite database successfully opened for thread 140174052039296.
 
2017-12-14 13:53:30   notice : Using encrypted passwords. Encryption key: '/var/lib/maxscale/.secrets'.
 
2017-12-14 13:53:30   info   : Notification service feedback is not enabled.
 
2017-12-14 13:53:30   notice : Loaded module MySQLClient: V1.1.0 from /usr/lib64/maxscale/libMySQLClient.so
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('', 'localhost', NULL, 0, NULL)
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('', 'box01', NULL, 0, NULL)
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('maxusr', '192.168.50.%', NULL, 1, '23AE809DDACAF96AF0FD78ED04B6A265E05AA257')
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('maxmon', '192.168.50.%', NULL, 1, '23AE809DDACAF96AF0FD78ED04B6A265E05AA257')
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('wb', '192.168.50.%', NULL, 1, '23AE809DDACAF96AF0FD78ED04B6A265E05AA257')
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('appurs', '192.168.50.%', NULL, 1, '23AE809DDACAF96AF0FD78ED04B6A265E05AA257')
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('wb', '192.168.50.100', NULL, 1, '23AE809DDACAF96AF0FD78ED04B6A265E05AA257')
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('wb', '%', NULL, 1, '23AE809DDACAF96AF0FD78ED04B6A265E05AA257')
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('wb', 'maxscale', NULL, 1, '23AE809DDACAF96AF0FD78ED04B6A265E05AA257')
 
2017-12-14 13:53:30   info   : [MySQLAuth] Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('appuser', '%', NULL, 1, '23AE809DDACAF96AF0FD78ED04B6A265E05AA257')
 
2017-12-14 13:53:30   notice : [MySQLAuth] [rwsplit-service] Loaded 10 MySQL users for listener rwsplit-listener.
 
2017-12-14 13:53:30   notice : Listening for connections at [0.0.0.0]:4006 with protocol MySQL
 
2017-12-14 13:53:30   info   : Started session [1] for rwsplit-service service
 
2017-12-14 13:53:30   notice : Listening for connections at [/tmp/maxadmin.sock]:0 with protocol MaxScale Admin
 
2017-12-14 13:53:30   info   : Started session [2] for CLI service
 
2017-12-14 13:53:30   notice : Listening for connections at [0.0.0.0]:4007 with protocol MySQL
 
2017-12-14 13:53:30   info   : Started session [3] for MyService service
 
2017-12-14 13:53:30   notice : MaxScale started with 1 server threads.
 
2017-12-14 13:53:30   notice : Started MaxScale log flusher.
 
2017-12-14 13:53:30   notice : Server changed state: prod_mariadb01[192.168.50.11:3306]: new_master. [Running] -> [Master, Synced, Running]
 
2017-12-14 13:53:30   notice : Server changed state: prod_mariadb02[192.168.50.12:3306]: new_slave. [Running] -> [Slave, Synced, Running]
 
2017-12-14 13:53:30   notice : Server changed state: prod_mariadb03[192.168.50.13:3306]: new_slave. [Running] -> [Slave, Synced, Running]

At this point, the client connections to the backend using the port 4006 continues to work well. But, as for now, I would like to start using the Cache Filter, connections on port 4007 with the same user is not working.

I show that below:

#: Cache Filter port
 
[root@maxscale maxscale.cnf.d]# mysql -u appuser -p123 -h 192.168.50.100 -P 4007 -e "select 1"
 
ERROR 1045 (28000): Access denied for user 'appuser'@'192.168.50.100' (using password: YES)
 
 
 
#: ReadWriteSplit port
 
[root@maxscale maxscale.cnf.d]# mysql -u appuser -p123 -h 192.168.50.100 -P 4006 -e "select 1"
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+
 
[root@maxscale maxscale.cnf.d]#

You can notice that the user was loaded when Maxscale started up and why the connection is OK for one port and not for the other as the user information on the underlying backend is the same? As well, you can notice that the database account for the services are all the same.

 Comments   
Comment by Johan Wikman [ 2017-12-15 ]

wagnerbianchi There seems to be a couple of problems in the configuration file. There are no servers defined for [MyService]. And from [rwsplit-service] most settings are missing, although I suspect that might be due to the configuration file having been created from dynamically created configuration files.

Please check the behaviour with servers added to [MyService].

Comment by Wagner Bianchi (Inactive) [ 2017-12-15 ]

Johan,

First of all, thanks for your attention. Yes, adding servers to the MyService definition gave me the right access to the servers, while executing the cache layer on Maxscale. The current definition is like:

[root@maxscale maxscale.cnf.d]# cat filter_cache.cnf
 
[MyCache]
 
type=filter
 
module=cache
 
storage=storage_inmemory
 
soft_ttl=30
 
hard_ttl=45
 
cached_data=thread_specific
 
selects=assume_cacheable
 
max_size=50Mi
 
#rules=
 
 
 
[MyService]
 
type=service
 
router=readwritesplit
 
user=maxusr
 
password=F6A2D70932C9ED77CD1F13D92AC8E835
 
filters=MyCache
 
servers=prod_mariadb01,prod_mariadb02,prod_mariadb03
 
 
 
[MyCache-listener]
 
type=listener
 
protocol=MySQLClient
 
service=MyService
 
address=0.0.0.0
 
port=4007
 
authenticator=MySQLAuth
 
authenticator_options=inject_service_user=false

We can close this issue, thanks once again.

Comment by Johan Wikman [ 2017-12-18 ]

wagnerbianchi Ok, great that the solution was this simple

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