From d4ef2b592d3e1861e71cc6fa2b53696ab4aa48ae Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Mon, 8 May 2023 10:42:26 +0200
Subject: [server] DB: Fix possible handout of dead connections with latest
 mariadb connector

Also more error handling.
---
 .../org/openslx/bwlp/sat/database/Database.java    |  9 ++++-
 .../openslx/bwlp/sat/database/MysqlConnection.java | 16 +++++++-
 .../openslx/bwlp/sat/database/MysqlStatement.java  | 44 ++++++++++++++++++++++
 3 files changed, 67 insertions(+), 2 deletions(-)

(limited to 'dozentenmodulserver')

diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Database.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Database.java
index 17d7a6c7..204cfcb0 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Database.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Database.java
@@ -56,7 +56,14 @@ public class Database {
 			}
 			if (!busyConnections.add(con))
 				throw new RuntimeException("Tried to hand out a busy connection!");
-			return con;
+			try {
+				// By convention in our program we don't want auto commit
+				con.setAutoCommit(false);
+				return con;
+			} catch (SQLException e) {
+				con.release();
+				continue;
+			}
 		}
 		// No pooled connection
 		if (busyConnections.size() > 20) {
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/MysqlConnection.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/MysqlConnection.java
index f1e5e075..4ca91f40 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/MysqlConnection.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/MysqlConnection.java
@@ -52,7 +52,11 @@ public class MysqlConnection implements AutoCloseable {
 	@Override
 	public void close() {
 		if (hasPendingQueries) {
-			LOGGER.warn("Mysql connection had uncommited queries on .close()", new RuntimeException("Stack trace"));
+			LOGGER.warn("Mysql connection had uncommited queries on .close()",
+					new RuntimeException("Stack trace"));
+			for (MysqlStatement s : openStatements) {
+				LOGGER.info(s.getQuery());
+			}
 			hasPendingQueries = false;
 		}
 		try {
@@ -66,6 +70,12 @@ public class MysqlConnection implements AutoCloseable {
 			}
 			openStatements.clear();
 		}
+		try {
+			rawConnection.rollback();
+			rawConnection.setAutoCommit(true);
+		} catch (SQLException e) {
+			LOGGER.warn("Rolling back uncommited queries failed!", e);
+		}
 		Database.returnConnection(this);
 	}
 
@@ -77,4 +87,8 @@ public class MysqlConnection implements AutoCloseable {
 		}
 	}
 
+	void setAutoCommit(boolean b) throws SQLException {
+		rawConnection.setAutoCommit(b);
+	}
+
 }
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/MysqlStatement.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/MysqlStatement.java
index 1d1bbc18..a5d0f49d 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/MysqlStatement.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/MysqlStatement.java
@@ -5,6 +5,7 @@ import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.SQLFeatureNotSupportedException;
 import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.HashMap;
@@ -42,6 +43,11 @@ public class MysqlStatement implements Closeable {
 		this.query = query;
 		this.statement = con.prepareStatement(query.sql);
 	}
+	
+	public String getQuery()
+	{
+	   return query.sql;
+	}
 
 	/**
 	 * Returns the indexes for a parameter.
@@ -190,6 +196,39 @@ public class MysqlStatement implements Closeable {
 	public int executeUpdate() throws SQLException {
 		return statement.executeUpdate();
 	}
+	
+	/**
+	 * Retrieves any auto-generated keys created as a result of executing this
+	 * <code>Statement</code> object. If this <code>Statement</code> object did
+	 * not generate any keys, an empty <code>ResultSet</code>
+	 * object is returned.
+	 *
+	 * <p>
+	 * <B>Note:</B>If the columns which represent the auto-generated keys were not specified,
+	 * the JDBC driver implementation will determine the columns which best represent the
+	 * auto-generated keys.
+	 *
+	 * @return a <code>ResultSet</code> object containing the auto-generated key(s)
+	 *         generated by the execution of this <code>Statement</code> object
+	 * @exception SQLException if a database access error occurs or
+	 *               this method is called on a closed <code>Statement</code>
+	 * @throws SQLFeatureNotSupportedException if the JDBC driver does not support this method
+	 */
+	public ResultSet getGeneratedKeys() throws SQLException {
+		ResultSet rs = statement.getGeneratedKeys();
+		openResultSets.add(rs);
+		return rs;
+	}
+
+	public int lastInsertId() throws SQLException {
+		int result = -1;
+		try (ResultSet rs = statement.getGeneratedKeys()) {
+			if (rs.next()) {
+				result = rs.getInt(1);
+			}
+		}
+		return result;
+	}
 
 	/**
 	 * Closes the statement.
@@ -205,6 +244,11 @@ public class MysqlStatement implements Closeable {
 				//
 			}
 		}
+		try {
+			statement.cancel();
+		} catch (SQLException e) {
+			// Nothing to do
+		}
 		try {
 			statement.close();
 		} catch (SQLException e) {
-- 
cgit v1.2.3-55-g7522