0

I'm currently having trouble sending a multiquery to a mysql database through java. The code is like this:

String query = "Query 1; Query 2; Query 3;... Query 59" 
stmt = connection.createStatement();
System.out.println(query);
stmt.execute(query);

And then I get the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Got packets out of order
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:925)
at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:4017)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3129)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2335)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2722)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2738)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:899)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:737)
at fileUpload.ICDBoperations.uploadELG(ICDBoperations.java:61)
at com.intuitivecare.icserver.upload.doPost(upload.java:108)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
at java.lang.Thread.run(Thread.java:745)

The code works fine for just 1 query (any of the queries).

I already have set allowMultiQueries=true, also the problem is not a packet too large problem as I truncated at only the 3rd query and got the same error.

Any ideas? Thanks!

Luis Henrique
  • 1
  • 1
  • 2
  • To me this sounds like a chance that you are sharing a single connection on multiple threads concurrently. Is that connection created (or leased it from a connection pool) for that request specifically, or is it a variable that is shared by multiple threads? – Mark Rotteveel Jan 27 '17 at 13:39
  • it is obtained by each servlet instance from a datasource object which was created on the servlet init method – Luis Henrique Feb 02 '17 at 04:09
  • That is not a good way to do it: there is a single servlet instance shared by multiple requests concurrently. See also http://stackoverflow.com/q/3106452/466862 – Mark Rotteveel Feb 02 '17 at 07:17
  • I'm sorry, maybe I didn't describe it fully. I initialize a datasource and I have a get connection method. I only call the getConnection method from within the doPost method and I close it within the same method. I believe that's the best way to do it, right? – Luis Henrique Feb 11 '17 at 18:06

2 Answers2

4

I had this issue in spring boot application and found out that "mysql-connector-java" version 5.1.6 was causing this. I updated it with 5.1.45 and resolved the issue.

Purushothaman
  • 417
  • 4
  • 6
0

I still don't have the answer but I found a workaround. Instead of trying to execute the multiple queries at once and establishing the transaction within java, I added the BEGIN TRANSACTION; query at the beginning and the COMMIT; at the end. Then I just executed the queries with the following code:

stmt = connection.createStatement();
    for(int i=0;i<numQueries;i++){
        stmt.execute(queries[i]);
    }

Where queries[] is a string array containing the queries I want to execute in the appropriate order. This may not be optimal, but, in my case it did the trick. When I execute the queries in netbeans IDE it takes me about 22s, whereas executing the above code takes about 25s. Hope people find this helpful!

Luis Henrique
  • 1
  • 1
  • 2