Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-741

when only one slave and one master, connector/j only connect to master in some case

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 2.2.1
    • N/A
    • aurora, Failover
    • None
    • AWS Aurora with 1 master node and 1 slave node

    Description

      In below scenarios, connector/j only connect to master at last.

      Scenario 1:
      1, Start aurora with 1 master node and 1 slave node.

      2, Start spring boot program using below URL to connect aurora.
      jdbc:mysql:aurora://xxx-cluster.cluster-yyy-zzz.rds.amazonaws.com/dbname
      Now we can set readOnly to true to access slave node, and set readOnly to false to access master node.

      3. Remove slave node from aurora cluster
      In this time, no matter what readOnly to set, the connector/j will access master node. It's OK.
      Sometimes, SQLException may happen when slave node is not removed completely.

      4. Add slave node into aurora cluster again.
      In this time, although setting readOnly to true, the connector/j still connect to master node.
      It's not OK. We want to connect slave node for read only connection.
      Now we can access slave node db from command line's mysql command.

      Scenario 2:
      1, Start aurora with only 1 master node.

      2, Start spring boot program using below URL to connect aurora.
      jdbc:mysql:aurora://xxx-cluster.cluster-yyy-zzz.rds.amazonaws.com/dbname
      Now no matter what readOnly to set, the connector/j will access master node. It's OK.

      3. Add slave node into aurora cluster again.
      In this time, although setting readOnly to true, the connector/j still connect to master node.
      It's not OK. We want to connect slave node for read only connection.
      We can access slave node db from command line's mysql command.

      Cause:
      In org.mariadb.jdbc.internal.protocol.AuroraProtocol#loop method, when only one slave and connect fail or no slave, it maybe not execute org.mariadb.jdbc.internal.failover.impl.AuroraListener#retrieveAllEndpointsAndSet to retrieve endpoints.

      Attachments

        Activity

          donghui Hui Dong added a comment -

          We found we set pool=true in jdbcurl and we also use tomcat jdbc pool with spring boot. So I think this make our program create two connection pool. And this make our program not reconnect database normally when new node added into cluster.

          donghui Hui Dong added a comment - We found we set pool=true in jdbcurl and we also use tomcat jdbc pool with spring boot. So I think this make our program create two connection pool. And this make our program not reconnect database normally when new node added into cluster.
          plebedev Peter Lebedev added a comment -

          We also have a similar problem with 2.5.4 driver, and we have not had it with 1.7.2 driver.

          Here is our use case:
          1. We have a task that runs every minute inside tomcat, that can connect to either an Aurora database or a traditional mysql, that is determined when tomcat starts.
          2. This task is supposed to read from either an Aurora reader, or a mysql slave running in read-only mode
          3. This task creates a read-only connection.
          4. For business reasons this task does "SHOW SLAVE STATUS" to determine how far behind the slave is.
          When it is executed against Aurora reader, it returns nothing, and when it's executed agains a traditional slave, it returns the status, so we can determine how far behind the slave is.
          5. If "SHOW SLAVE STATUS" is executed against the Aurora writer node, it returns values like
          Slave_IO_Running='No', Slave_SQL_Running='No', Seconds_Behind_Master='null'
          and the task fails, and then re-tried in a minute.

          Per documentation, when aurora mode is used, the driver supposed to pick a reader node if a connection is explicitly set to read only:

          aurora	This mode supports connection failover in an Amazon Aurora cluster. This mode does support load-balancing reads on slave instances if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave instance to execute read queries for a connection.
          This mode has been available since MariaDB Connector/J 1.2.0
          

          We have been running this task for about two years using 1.7.2 driver w/o any issues. This week we decided to test 2.5.4 and when we just changed the driver, and restarted tomcat, we got a burst of errors because of (5), then after 10s of failed executions, it started to execute normally with occasional failures because of (5).

          When we switched back to 1.7.2 driver, all these issues went away. Looks like that there was a regression since that version, so the driver is not always picking a slave for read-only connections.

          plebedev Peter Lebedev added a comment - We also have a similar problem with 2.5.4 driver, and we have not had it with 1.7.2 driver. Here is our use case: 1. We have a task that runs every minute inside tomcat, that can connect to either an Aurora database or a traditional mysql, that is determined when tomcat starts. 2. This task is supposed to read from either an Aurora reader, or a mysql slave running in read-only mode 3. This task creates a read-only connection. 4. For business reasons this task does "SHOW SLAVE STATUS" to determine how far behind the slave is. When it is executed against Aurora reader, it returns nothing, and when it's executed agains a traditional slave, it returns the status, so we can determine how far behind the slave is. 5. If "SHOW SLAVE STATUS" is executed against the Aurora writer node, it returns values like Slave_IO_Running='No', Slave_SQL_Running='No', Seconds_Behind_Master='null' and the task fails, and then re-tried in a minute. Per documentation, when aurora mode is used, the driver supposed to pick a reader node if a connection is explicitly set to read only: aurora This mode supports connection failover in an Amazon Aurora cluster. This mode does support load-balancing reads on slave instances if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave instance to execute read queries for a connection. This mode has been available since MariaDB Connector/J 1.2.0 We have been running this task for about two years using 1.7.2 driver w/o any issues. This week we decided to test 2.5.4 and when we just changed the driver, and restarted tomcat, we got a burst of errors because of (5), then after 10s of failed executions, it started to execute normally with occasional failures because of (5). When we switched back to 1.7.2 driver, all these issues went away. Looks like that there was a regression since that version, so the driver is not always picking a slave for read-only connections.
          diego dupin Diego Dupin added a comment -

          closing since 2.2 version is not supported anymore, and no changes in this ticket since a few years

          diego dupin Diego Dupin added a comment - closing since 2.2 version is not supported anymore, and no changes in this ticket since a few years

          People

            diego dupin Diego Dupin
            donghui Hui Dong
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.