[gobblin] branch master updated: [GOBBLIN-1503] Modify mysql validation query to fail if db is in read-only mode

[ap...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

aplex pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/gobblin.git


The following commit(s) were added to refs/heads/master by this push:
     new de94515  [GOBBLIN-1503] Modify mysql validation query to fail if db is in read-only mode
de94515 is described below

commit de94515d04ea615bdff86b306f34f5361e1725e6
Author: Jack Moseley <jm...@linkedin.com>
AuthorDate: Mon Aug 9 13:08:24 2021 -0700

    [GOBBLIN-1503] Modify mysql validation query to fail if db is in read-only mode
    
    If mysql db enters read-only mode due to a failover, the connections need to be reset before writes will work. Currently the validation query is "select 1", so it will not evict connections in read-only mode. This new query is taken from https://stackoverflow.com/questions/39552146/evicting-connections-to-a-read-only-node-in-a-cluster-from-the-connection-pool, and should fail the validation if the db is read-only, so connections will be retried.
---
 .../src/main/java/org/apache/gobblin/metastore/MysqlStateStore.java   | 4 +++-
 1 file changed, 3 insertions(+), 1 deletion(-)

diff --git a/gobblin-metastore/src/main/java/org/apache/gobblin/metastore/MysqlStateStore.java b/gobblin-metastore/src/main/java/org/apache/gobblin/metastore/MysqlStateStore.java
index b96c877..3b54f42 100644
--- a/gobblin-metastore/src/main/java/org/apache/gobblin/metastore/MysqlStateStore.java
+++ b/gobblin-metastore/src/main/java/org/apache/gobblin/metastore/MysqlStateStore.java
@@ -187,7 +187,9 @@ public class MysqlStateStore<T extends State> implements StateStore<T> {
     basicDataSource.setDriverClassName(ConfigUtils.getString(config, ConfigurationKeys.STATE_STORE_DB_JDBC_DRIVER_KEY,
         ConfigurationKeys.DEFAULT_STATE_STORE_DB_JDBC_DRIVER));
     // MySQL server can timeout a connection so need to validate connections before use
-    basicDataSource.setValidationQuery("select 1");
+    // This query will fail if db is in read-only mode, otherwise read-only connections may continue to fail and not get evicted
+    // See https://stackoverflow.com/questions/39552146/evicting-connections-to-a-read-only-node-in-a-cluster-from-the-connection-pool
+    basicDataSource.setValidationQuery("select case when @@read_only = 0 then 1 else (select table_name from information_schema.tables) end as `1`");
     basicDataSource.setTestOnBorrow(true);
     basicDataSource.setDefaultAutoCommit(false);
     basicDataSource.setTimeBetweenEvictionRunsMillis(60000);