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(-) 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 + * Statement object. If this Statement object did + * not generate any keys, an empty ResultSet + * object is returned. + * + *

+ * Note: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 ResultSet object containing the auto-generated key(s) + * generated by the execution of this Statement object + * @exception SQLException if a database access error occurs or + * this method is called on a closed Statement + * @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