Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
2.5.13
-
None
Description
Good afternoon, I'm trying to set up maxscale according to the following scheme
Two services readwritesplit (Read-Write-Service-rw1, Read-Write-Service-rw2)
In front of them service - schemarouter
On Read-Write-Service-rw1
There is a replicated DB shard1 in it there are many tables including the "history" table
On Read-Write-Service-rw2
There is a replicated shard2 DB in it one "history" table
I attach the config below, when connecting or trying to execute the show databases command, it writes to the logs
|
|
2021-06-20 13:37:29 error: (1) [schemarouter] (Service-shard) Fatal error when processing SHOW DATABASES response, closing session.
|
2021-06-20 13:37:37 error: (2) [schemarouter] (Service-shard) Fatal error when processing SHOW DATABASES response, closing session.
|
2021-06-20 13:37:39 error: (3) [schemarouter] (Service-shard) Fatal error when processing SHOW DATABASES response, closing session.
|
2021-06-20 13:38:00 error: (4) [schemarouter] (Service-shard) Fatal error when processing SHOW DATABASES response, closing session.
|
2021-06-20 13:39:01 error: (5) [schemarouter] (Service-shard) Fatal error when processing SHOW DATABASES response, closing session.
|
|
|
MariaDB [(none)]> show databases;
|
ERROR 2013 (HY000): Lost connection to MySQL server during query
|
MariaDB [(none)]> show databases;
|
ERROR 2006 (HY000): MySQL server has gone away
|
No connection. Trying to reconnect...
|
Connection id: 4
|
Current database: *** NONE ***
|
|
|
ERROR 5000 (DUPDB): Error: duplicate tables found on two different shards.
|
|
If I go separately under the user data as specified in the service sections or under the client data, then all commands work fine, what could be the problem?
Grants on all servers are:
GRANT select, usage, super, READ_ONLY ADMIN, replication client, reload, process, show databases, event, REPLICATION SLAVE ADMIN on *.* to 'maxscale_main'@'%';
|
|
|
grant all on *.* to maxscale_main_client@'%' identified by 'PASSWORD';
|
|
Also i've tried to add separatly
grant all on shard1.* to maxscale_main_client@'%' identified by 'PASSWORD' for RW1
and
grant all on shard2.* to maxscale_main_client@'%' identified by 'PASSWORD' for RW2
MaxScale version 2.5.13, MariaDB 10.5.10
Thank you.
[maxscale]
|
threads=4
|
admin_secure_gui=false
|
admin_host=0.0.0.0
|
passive=true
|
auth_connect_timeout=60s
|
auth_read_timeout=60s
|
auth_write_timeout=60s
|
query_retries=10
|
query_retry_timeout=30s
|
load_persisted_configs=false
|
syslog=0
|
log_notice=1
|
log_warning=1
|
log_info=0
|
log_debug=1
|
|
|
|
|
|
|
[server-rw1-db1]
|
type=server
|
address=10.10.10.1
|
port=3306
|
protocol=MariaDBBackend
|
|
|
[server-rw1-db2]
|
type=server
|
address=10.10.10.2
|
port=3306
|
protocol=MariaDBBackend
|
|
|
|
|
[server-rw2-db1]
|
type=server
|
address=10.10.20.1
|
port=3306
|
protocol=MariaDBBackend
|
|
|
[server-rw2-db2]
|
type=server
|
address=10.10.20.2
|
port=3306
|
protocol=MariaDBBackend
|
|
|
|
|
|
|
|
|
[MariaDB-Monitor-rw1]
|
enforce_read_only_slaves=true
|
type=monitor
|
module=mariadbmon
|
servers=server-rw1-db1, server-rw1-db2
|
user=maxscale_main
|
password=PASSWORD
|
monitor_interval=1000
|
backend_connect_attempts=5
|
backend_connect_timeout=10s
|
backend_read_timeout=60s
|
backend_write_timeout=10s
|
|
|
[MariaDB-Monitor-rw2]
|
enforce_read_only_slaves=true
|
type=monitor
|
module=mariadbmon
|
servers=server-rw2-db1, server-rw2-db2
|
user=maxscale_main
|
password=PASSWORD
|
monitor_interval=1000
|
backend_connect_attempts=5
|
backend_connect_timeout=10s
|
backend_read_timeout=60s
|
backend_write_timeout=10s
|
|
|
[Read-Write-Service-rw1]
|
connection_timeout=600s
|
type=service
|
router=readwritesplit
|
servers=server-rw1-db1, server-rw1-db2
|
user=maxscale_main
|
password=PASSWORD
|
max_slave_replication_lag = 5s
|
causal_reads=global
|
causal_reads_timeout=30s
|
delayed_retry = true
|
delayed_retry_timeout = 180s
|
master_accept_reads=false
|
max_slave_connections=1
|
max_sescmd_history = 150000
|
|
|
[Read-Write-Service-rw2]
|
connection_timeout=600s
|
type=service
|
router=readwritesplit
|
servers=server-rw2-db1, server-rw2-db2
|
user=maxscale_main
|
password=PASSWORD
|
max_slave_replication_lag = 5s
|
causal_reads=global
|
causal_reads_timeout=30s
|
delayed_retry = true
|
delayed_retry_timeout = 180s
|
master_accept_reads=false
|
max_slave_connections=1
|
max_sescmd_history = 150000
|
|
|
[Service-shard]
|
auth_all_servers=true
|
log_auth_warnings=true
|
type=service
|
router=schemarouter
|
targets=Read-Write-Service-rw1, Read-Write-Service-rw2
|
user=maxscale_main
|
password=PASSWORD
|
max_sescmd_history = 150000
|
|
|
|
|
[OShard-Listener-shard]
|
type=listener
|
service=Service-shard
|
protocol=MariaDBClient
|
port=4006
|