![Openfire:org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - 关键字“LEFT”附近的语法不正确](https://linux22.com/image/737529/Openfire%EF%BC%9Aorg.jivesoftware.openfire.pubsub.PubSubPersistenceManager%20-%20%E5%85%B3%E9%94%AE%E5%AD%97%E2%80%9CLEFT%E2%80%9D%E9%99%84%E8%BF%91%E7%9A%84%E8%AF%AD%E6%B3%95%E4%B8%8D%E6%AD%A3%E7%A1%AE.png)
知道是什么原因导致 all.log 和 error.log 中不断出现此错误吗?Windows 上的 Openfire 4.3.2 连接到 SQL Server 2012 (SP4) - 虽然这应该不重要。这可能是 ofProperty 表中 SQL 不正确的结果吗?在哪里查看?我们有一个正在运行的 web/Candy.js 聊天应用程序……
2019.05.08 20:46:12 ERROR [TaskEngine-pool-16]: org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - Incorrect syntax near the keyword 'LEFT'.
java.sql.BatchUpdateException: Incorrect syntax near the keyword 'LEFT'.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069) ~[jtds-1.3.1.jar:1.3.1]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:223) ~[commons-dbcp2-2.5.0.jar:2.5.0]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1893) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:57) [xmppserver-4.3.2.jar:4.3.2]
at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:283) [xmppserver-4.3.2.jar:4.3.2]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_202]
at java.util.concurrent.FutureTask.run(Unknown Source) [?:1.8.0_202]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:1.8.0_202]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:1.8.0_202]
at java.lang.Thread.run(Unknown Source) [?:1.8.0_202]
答案1
正如 SQL Server Profiler 所示,Openfire 尝试执行以下 SQL:
DELETE FROM ofPubsubItem
LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID= @P0 AND nodeID= @P1 ORDER BY creationDate DESC LIMIT @P2 ) AS noDelete
ON ofPubsubItem.id = noDelete.id
WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = @P3 AND nodeID = @P4 ....'
这是不正确的(或者至少是非标准的从连接语句中删除)。根据https://stackoverflow.com/questions/4097260/sql-delete-based-on-condition-in-join, https://www.sqlservercentral.com/forums/topic/delete-statements-when-using-a-join-best-practice和其他资源,没有用于从连接中删除的 ANSI SQL 标准。使用子查询代替连接总是可行的,但如果您想使用连接,通常需要包含两次 FROM 关键字,例如:
DELETE FROM ofPubsubItem
FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID= @P0 AND nodeID= @P1 ORDER BY creationDate DESC LIMIT @P2 ) AS noDelete
ON ofPubsubItem.id = noDelete.id
WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = @P3 AND nodeID = @P4 ....'
如果 Openfire 开发人员能够在下一个错误修复版本中修复该错误那就太好了。