Keep alive database connection to prevent exceeding wait timeout

Fire dummy select statement to prevent hitting wait timeout in database
server.

The problem is not that easy to see and can only occur for very big
gerrit installation sites with ca. 300,000 accounts. Schema 146 is only
scanning the accounts from the database at first step and then migrating
those accounts from ReviewDb to NoteDb without any database activity.
Depending on the size of gerrit installation site, this migration can
take from minutes to many hours.

Even though the default wait timeout in MySQL server is 8 hours, many
sites substantially descreased that value so that instead of document
that problem and enforcing gerrit administrators to increase wait
timeout value, fire dummy SQL select statement per batch chunk of 1000
acounts. That would mean, that for 300,000 accounts the 300 SQL
statements would be fired.

Bug: Issue 12637
Change-Id: I4138ad1f85e92aef6d06aa87a0a0d2bc1a5341cb

1 files changed, 22 insertions, 3 deletions

diff --git a/gerrit-server/src/main/java/com/google/gerrit/server/schema/Schema_146.java b/gerrit-server/src/main/java/com/google/gerrit/server/schema/Schema_146.java
index 64e9123c95..d05c5f560a 100644
--- a/gerrit-server/src/main/java/com/google/gerrit/server/schema/Schema_146.java
+++ b/gerrit-server/src/main/java/com/google/gerrit/server/schema/Schema_146.java
@@ -23,6 +23,7 @@ import com.google.gerrit.reviewdb.server.ReviewDb;
 import com.google.gerrit.server.GerritPersonIdent;
 import com.google.gerrit.server.config.AllUsersName;
 import com.google.gerrit.server.git.GitRepositoryManager;
+import com.google.gwtorm.jdbc.JdbcSchema;
 import com.google.gwtorm.server.OrmException;
 import com.google.inject.Inject;
 import com.google.inject.Provider;
@@ -104,7 +105,7 @@ public class Schema_146 extends SchemaVersion {
         Sets.newHashSet(Iterables.partition(accounts, 500));
     ExecutorService pool = createExecutor(ui);
     try {
-      batches.stream().forEach(batch -> pool.submit(() -> processBatch(batch, ui)));
+      batches.stream().forEach(batch -> pool.submit(() -> processBatch(db, batch, ui)));
       pool.shutdown();
       pool.awaitTermination(Long.MAX_VALUE, TimeUnit.DAYS);
     } catch (InterruptedException e) {
@@ -125,7 +126,7 @@ public class Schema_146 extends SchemaVersion {
     return Executors.newFixedThreadPool(threads);
   }
 
-  private void processBatch(List<Entry<Account.Id, Timestamp>> batch, UpdateUI ui) {
+  private void processBatch(ReviewDb db, List<Entry<Account.Id, Timestamp>> batch, UpdateUI ui) {
     try (Repository repo = repoManager.openRepository(allUsersName);
         RevWalk rw = new RevWalk(repo);
         ObjectInserter oi = repo.newObjectInserter()) {
@@ -143,10 +144,28 @@ public class Schema_146 extends SchemaVersion {
         showProgress(ui, count);
         if (count % 1000 == 0) {
           gc(repo, true, ui);
+          keepAliveDatabaseConnection(db);
         }
       }
     } catch (IOException e) {
       throw new UncheckedIOException(e);
+    } catch (SQLException e) {
+      throw new RuntimeException(e);
+    }
+  }
+
+  private static String dummySelectStatement() {
+    // TODO(davido): Gwtorm doesn't expose dummySelectStatement() method for all supported SQL
+    // dialects.
+    return "SELECT version_nbr FROM schema_version";
+  }
+
+  private static void keepAliveDatabaseConnection(ReviewDb db) throws SQLException {
+    try (Statement stmt = ((JdbcSchema) db).getConnection().createStatement();
+        ResultSet rs = stmt.executeQuery(dummySelectStatement())) {
+      // No Op.
+      // The select is fired to prevent the SQL connection from becoming stale
+      // and being closed on the server side during long running batch operation.
     }
   }
 
@@ -278,7 +297,7 @@ public class Schema_146 extends SchemaVersion {
     return oi.insert(cb);
   }
 
-  private boolean isInitialEmptyCommit(ObjectId emptyTree, RevCommit c) {
+  private static boolean isInitialEmptyCommit(ObjectId emptyTree, RevCommit c) {
     return c.getParentCount() == 0
         && c.getTree().equals(emptyTree)
         && c.getShortMessage().equals(CREATE_ACCOUNT_MSG);