Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-3626

readwritesplit and schemarouter

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 2.5.13
    • 2.5.15
    • schemarouter
    • 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
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            antonp1976 Anton Petin
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.