I have a web application running under tomcat talking to MariaDB running on the same machine. The connection is via Connector/J via an URL like this:

jdbc:mysql://127.0.0.1:3306/mydb

  ?jdbcCompliantTruncation=false

  &cachePrepStmts=true

  &prepStmtCacheSize=200

  &prepStmtCacheSqlLimit=4096

  &cacheCallableStmts=true

  &cacheResultSetMetadata=true

  &useUnicode=true

  &cacheServerConfiguration=true

  &logger=Slf4JLogger

  &includeThreadNamesAsStatementComment=true

Note we have a TCP connection via localhost:3306.

Occasionally, when restarting the application (note, we're talking about a COMPLETE RESTART of tomcat), the startup process will hang.

It turns out the reason for the hang is that at startup there is a sequence of standard SQL queries that always run. The one that it blocks on is DROP FUNCTION IF EXISTS `mydb`.REGMATCH, and this query is being blocked due to locks being held by another long-running query that is STILL RUNNING even though it was started by the previous invocation of tomcat, which is long ago dead and gone.

Using a little inference, the problem here must be that the mariadb server does not detect an error on client TCP sockets until it actually attempts to read or write those sockets.

This means that if you connect to the server with a process X, initiate a long-running query Q, and terminate process X, the query Q is NOT killed immediately, but will only die some time in the future whenever the server finally gets around to actually trying to return results and therefore writing to the socket.

Obviously, the query is already doomed as soon as process X disconnects the TCP socket, so there's certainly no point in query Q continuing to exist after that point.

The fix is that there needs to be better asynchronous monitoring on the TCP socket to detect error conditions that occur while the main thread reading and writing to the TCP socket and doing the actual query is off doing other things, so that it can be killed immediately if the socket disconnects.

I would think the socket is already being monitored for incoming data, because isn't it possible to asynchronously kill queries via JDBC (which would imply this)?

Or maybe the problem is this one?