☰

Openfire：org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - 关键字“LEFT”附近的语法不正确

tag-icon • tag-icon sql-server openfire
Openfire：org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - 关键字“LEFT”附近的语法不正确

知道是什么原因导致 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 开发人员能够在下一个错误修复版本中修复该错误那就太好了。

相关内容