java.sql.BatchUpdateException

There are no available Samebug tips for this exception. Do you have an idea how to solve this issue? A short tip would help users who saw this issue last week.

  • Using fresh install of OpenFire 3.9.3 (but the database has been taken from another machine, running pretty recent OF version) DB server is MariaDB 10 {panel:title=Exception} 2015.03.28 20:51:59 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1981) at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1393) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100) at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57) at $java.sql.PreparedStatement$$EnhancerByProxool$$3661bae7.executeBatch(<generated>) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1886) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:62) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:279) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1933) ... 16 more {panel} Manual test: {panel:title=MariaDB command line} MariaDB [xmpp]> DELETE FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceI -> D='saper@saper.info' AND nodeID='urn:xmpp:inbox' ORDER BY creationDate DESC LIMIT 1000000) AS noDelete ON ofPu -> bsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = 'saper@saper.info' AND nodeID -> = 'urn:xmpp:inbox' -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceI D='saper@saper.info' AND n' at line 1 {panel} Looks like 7029145857a07bce16b8f1b18ce625d6a1378157 broke this query for MySQL (it needs {{DELETE tablename FROM tablename LEFT JOIN .... }}
    via by Marcin Cieślak,
  • Using fresh install of OpenFire 3.9.3 (but the database has been taken from another machine, running pretty recent OF version) DB server is MariaDB 10 {panel:title=Exception} 2015.03.28 20:51:59 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1981) at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1393) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100) at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57) at $java.sql.PreparedStatement$$EnhancerByProxool$$3661bae7.executeBatch(<generated>) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1886) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:62) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:279) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1933) ... 16 more {panel} Manual test: {panel:title=MariaDB command line} MariaDB [xmpp]> DELETE FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceI -> D='saper@saper.info' AND nodeID='urn:xmpp:inbox' ORDER BY creationDate DESC LIMIT 1000000) AS noDelete ON ofPu -> bsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = 'saper@saper.info' AND nodeID -> = 'urn:xmpp:inbox' -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceI D='saper@saper.info' AND n' at line 1 {panel} Looks like 7029145857a07bce16b8f1b18ce625d6a1378157 broke this query for MySQL (it needs {{DELETE tablename FROM tablename LEFT JOIN .... }}
    via by Marcin Cieślak,
  • Using fresh install of OpenFire 3.9.3 (but the database has been taken from another machine, running pretty recent OF version) DB server is MariaDB 10 {panel:title=Exception} 2015.03.28 20:51:59 org.jivesoftware.openfire.pubsub.PubSubPersistenceManager - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1981) at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1393) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100) at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57) at $java.sql.PreparedStatement$$EnhancerByProxool$$3661bae7.executeBatch(<generated>) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1886) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:62) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:279) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1933) ... 16 more {panel} Manual test: {panel:title=MariaDB command line} MariaDB [xmpp]> DELETE FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceI -> D='saper@saper.info' AND nodeID='urn:xmpp:inbox' ORDER BY creationDate DESC LIMIT 1000000) AS noDelete ON ofPu -> bsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = 'saper@saper.info' AND nodeID -> = 'urn:xmpp:inbox' -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceI D='saper@saper.info' AND n' at line 1 {panel} Looks like 7029145857a07bce16b8f1b18ce625d6a1378157 broke this query for MySQL (it needs {{DELETE tablename FROM tablename LEFT JOIN .... }}
    via by Marcin Cieślak,
  • No title
    via by Unknown author,
  • Duplicate Upgrade Query in 3.1
    via GitHub by jtesser
    ,
  • fix /nlgroupspassword
    via GitHub by rourke750
    ,
  • 运行时出现异常
    via GitHub by gewanbo
    ,
    • java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1981) at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1393) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100) at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57) at $java.sql.PreparedStatement$$EnhancerByProxool$$3661bae7.executeBatch(<generated>) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.purgeItems(PubSubPersistenceManager.java:1886) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager.access$000(PubSubPersistenceManager.java:62) at org.jivesoftware.openfire.pubsub.PubSubPersistenceManager$2.run(PubSubPersistenceManager.java:279) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='saper@saper.info' AND no' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1933)

    Users with the same issue

    Handemelindo
    2 times, last one,
    Unknown visitor1 times, last one,
    Unknown visitor1 times, last one,
    linxiaolong
    2 times, last one,
    somasundaram
    1 times, last one,
    115 more bugmates