MySQL Syntax error

Openfire Openfire Support
1

I have openfire 4.7.3 running in a Ubuntu 20.04 with 24GB RAM and MySQL 8.0.30

in logs i can see a lot of errors:

2022.08.03 07:16:38 e[1;31mERRORe[m [TaskEngine-pool-9]: org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='aboboda01@10.3.0.2' AND ' at line 1
java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='aboboda01@10.3.0.2' AND ' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_312]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_312]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_312]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_312]
	at com.mysql.cj.util.Util.handleNewInstance(Util.java:192) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.util.Util.getInstance(Util.java:167) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.util.Util.getInstance(Util.java:174) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executePreparedBatchAsMultiStatement(ClientPreparedStatement.java:584) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:431) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:795) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:241) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:241) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.purgeItems(DefaultPubSubPersistenceProvider.java:1897) [xmppserver-4.7.3.jar:4.7.3]
	at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.access$000(DefaultPubSubPersistenceProvider.java:50) [xmppserver-4.7.3.jar:4.7.3]
	at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider$1.run(DefaultPubSubPersistenceProvider.java:256) [xmppserver-4.7.3.jar:4.7.3]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_312]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_312]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_312]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_312]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_312]
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='aboboda01@10.3.0.2' AND ' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371) ~[mysql-connector-java-8.0.28.jar:8.0.28]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executePreparedBatchAsMultiStatement(ClientPreparedStatement.java:527) ~[mysql-connector-java-8.0.28.jar:8.0.28]
2

I cannot reproduce this issue. The query works fine for me, using mysql 8.0.30.0-ubuntu-.20.04.2:

mysql> DELETE ofPubsubItem FROM ofPubsubItem LEFT JOIN 
    -> (SELECT id FROM ofPubsubItem WHERE serviceID='foo' AND nodeID='bar' 
    -> ORDER BY creationDate DESC LIMIT 3) AS noDelete 
    -> ON ofPubsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND 
    -> ofPubsubItem.serviceID = 'foo' AND nodeID = 'bar';
Query OK, 0 rows affected (0,00 sec)
3

When i run the query in ubuntu comand line or in mysql workbench, run with no errors but openfire constantly log that error.

awkward