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

JIRA | Marcin Cieślak | 2 years ago
  1. 0

    [OF-900] PubSubManager: DELETE FROM ofPubsubItem LEFT JOIN breaks MySQL - Jive Software Open Source

    igniterealtime.org | 12 months ago
    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
  2. 0

    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 .... }}

    Jive Software Open Source | 2 years ago | Marcin Cieślak
    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
  3. 0

    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 .... }}

    JIRA | 2 years ago | Marcin Cieślak
    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
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    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 .... }}

    Jive Software Open Source | 2 years ago | Marcin Cieślak
    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
  6. 0

    to generate a table with list using hbm file

    Stack Overflow | 1 year ago | Yogesh Seralia
    java.sql.BatchUpdateException: Table 'gym.repeat_days_list' doesn't exist

  1. Kialandei 1 times, last 2 weeks ago
  2. MoYapro 1 times, last 2 weeks ago
  3. tvrmsmith 6 times, last 3 weeks ago
  4. linxiaolong 2 times, last 1 month ago
  5. DoktorDoener666 9 times, last 1 month ago
27 more registered users
68 unregistered visitors
Not finding the right solution?
Take a tour to get the most out of Samebug.

Tired of useless tips?

Automated exception search integrated into your IDE

Root Cause Analysis

  1. 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()
  2. Java RT
    Constructor.newInstance
    1. sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    2. sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    3. sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    4. java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    4 frames
  3. MySQL jdbc
    PreparedStatement.executeBatchSerially
    1. com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    2. com.mysql.jdbc.Util.getInstance(Util.java:384)
    3. com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    4. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
    5. com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
    6. com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    7. com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    8. com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
    9. com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    10. com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
    11. com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1933)
    11 frames