Repeated SQL queries from Openfire maxing out SQL Account limits

Openfire Openfire Support
1

New OpenFire installation and implementation. LDAPS in use. I was able to make it through the the setup wizard without major issues, but once its been configured there are massive amounts of logs and SQL queries being generated and its maxing out the account limits on the sql admin account being used to ferry the communication between SQL and openfire.

I cant seem to find any information online which points me in any particular direction.

These are the logs I am getting from the admin portal.

    at java.lang.Thread.run(Thread.java:1583) [?:?]
2024.02.14 10:28:08.617 ERROR [TaskEngine-pool-9]: org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider - User 'spark.admin' has exceeded the 'max_questions' resource (current value: 100)
java.sql.SQLSyntaxErrorException: User 'spark.admin' has exceeded the 'max_questions' resource (current value: 100)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:2014) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at org.apache.commons.dbcp2.DelegatingConnection.setAutoCommit(DelegatingConnection.java:801) ~[commons-dbcp2-2.9.0.jar:2.9.0]
    at org.apache.commons.dbcp2.DelegatingConnection.setAutoCommit(DelegatingConnection.java:801) ~[commons-dbcp2-2.9.0.jar:2.9.0]
    at org.jivesoftware.database.DbConnectionManager.getTransactionConnection(DbConnectionManager.java:219) ~[xmppserver-4.8.0.jar:4.8.0]
    at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.purgeItems(DefaultPubSubPersistenceProvider.java:1846) ~[xmppserver-4.8.0.jar:4.8.0]
    at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider$1.run(DefaultPubSubPersistenceProvider.java:256) ~[xmppserver-4.8.0.jar:4.8.0]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572) ~[?:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
    at java.lang.Thread.run(Thread.java:1583) [?:?]
Caused by: com.mysql.cj.exceptions.CJException: User 'spark.admin' has exceeded the 'max_questions' resource (current value: 100)
    at jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62) ~[?:?]
    at java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502) ~[?:?]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:486) ~[?:?]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:104) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:149) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:127) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:848) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:771) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:701) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:1050) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:997) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.NativeSession.execSQL(NativeSession.java:658) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    at com.mysql.cj.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:2005) ~[mysql-connector-j-8.2.0.jar:8.2.0]
    ... 10 more
2024.02.14 10:28:08.618 ERROR [TaskEngine-pool-9]: org.jivesoftware.database.DbConnectionManager - Cannot invoke "java.sql.Connection.rollback()" because "con" is null
java.lang.NullPointerException: Cannot invoke "java.sql.Connection.rollback()" because "con" is null
    at org.jivesoftware.database.DbConnectionManager.closeTransactionConnection(DbConnectionManager.java:251) ~[xmppserver-4.8.0.jar:4.8.0]
    at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.purgeItems(DefaultPubSubPersistenceProvider.java:1875) ~[xmppserver-4.8.0.jar:4.8.0]
    at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider$1.run(DefaultPubSubPersistenceProvider.java:256) ~[xmppserver-4.8.0.jar:4.8.0]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572) ~[?:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]
    at java.lang.Thread.run(Thread.java:1583) [?:?]
2024.02.14 10:28:08.619 ERROR [TaskEngine-pool-9]: org.jivesoftware.database.DbConnectionManager - Cannot invoke "java.sql.Connection.setAutoCommit(boolean)" because "con" is null
java.lang.NullPointerException: Cannot invoke "java.sql.Connection.setAutoCommit(boolean)" because "con" is null
    at org.jivesoftware.database.DbConnectionManager.closeTransactionConnection(DbConnectionManager.java:262) ~[xmppserver-4.8.0.jar:4.8.0]
    at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.purgeItems(DefaultPubSubPersistenceProvider.java:1875) ~[xmppserver-4.8.0.jar:4.8.0]
    at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider$1.run(DefaultPubSubPersistenceProvider.java:256) ~[xmppserver-4.8.0.jar:4.8.0]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572) ~[?:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:317) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) ~[?:?]

If I increase the Max number of queries on the “spark.admin” account, it just fills up again in a few minutes.

I would appreciate any help anyone could provide here.

Thanks!

2

The ‘max_questions’ resource appears to be a MySQL setting to apply resource usage limits to database accounts.

Openfire can make intensive use of the database. I would recommend against using account restrictions on the database at all. In MySQL, this appears to be configurable by configuring this setting to 0, if I read their documentation correctly.

3

So that appears to resolve the issue with maxing out the connection, however I am still wondering what is causing this to repeatedly trigger:

ERROR [TaskEngine-pool-9]: org.jivesoftware.database.DbConnectionManager - Cannot invoke “java.sql.Connection.rollback()” because “con” is null

It seems like it just triggers on repeat over and over. Is there something that tends to cause this?

4

I had assumed that these errors were caused by the same problem: because a new connection cannot be created to the database, the closure of such connections cause exceptions to be thrown.

We can guard against those messages being logged (I’ve created a ticket for this: OF-2800) but I believe that this is mostly a cosmetic change.

If you can somehow generate the Cannot invoke “java.sql.Connection.rollback()” because “con” is null exceptions without them being combined with other exceptions, then my above assumption is wrong, and more work is needed. Can you please verify if by addressing the original problem, the others go away too?

5

Good point. For some reason when I ran through the setup wizard and chose to set up as LDAPS, it still configured the profile as default, so I am going to have to re-run the setup again.

I will confirm for sure that they don’t happen again and let you know. Thank you so much for your quick responses!

1 Like