MySQL Bugs: #71454: A PreparedStatement.executeUpdate results in a MySQLNonTransientConnectionExcept

Bug #71454	A PreparedStatement.executeUpdate results in a MySQLNonTransientConnectionExcept
Submitted:	23 Jan 2014 3:31	Modified:	24 Mar 2014 18:55
Reporter:	David Heath	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.6.14	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Product Name: MySQL
Product Version: 5.6.14
Major Version: 5
Minor Version: 6
JDBC Driver Major Version: 5
JDBC Driver Minor Version: 1
JDBC Driver Name: MySQL Connector Java
JDBC Driver Version: mysql-connector-java-5.1.26 ( Revision: ${bzr.revision-id} )

A client (Large independent Oil Company) is experiencing the following Exception:

Caused by: 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:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
        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:1015)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2818)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2157)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2460)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2377)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2361)
        at com.x4m.x4mdb.tables.FeatureLockCRUD.acquireLocks(FeatureLockCRUD.java:491)

When executing our code.

Our code is updating a BLOB in a 2 column (int, BLOB) single row table.

How to repeat:
I have tried many ways to reproduce on my machine with no success.

Thank you for the report.

Please provide minimal Java test case, demonstrating the issue.

I am not sure if a minimal Java test case will help - but I will try.

The code which is failing has been successfully running on 100's of different machines for more than 2 1/2 years.

On this one users machine it seems to consistently fail with the supplied exception when I perform an update on a Table using a preparedStatement.

The object being updated is a BLOB.

The pseudo code is as follows:

String sql= "UPDATE x4m_lock_table SET STORAGE_ = ? WHERE ID = 1";

PreparedStatement pstmt = con.prepareStatement(sql);

byte[] bytes = ...;   // Convert small Java Object into a byte array

ByteArrayInputStream inStream = new ByteArrayInputStream(bytes);

pstmt.setBinaryStream(1, inStream, bytes.length);

pstmt.executeUpdate();         // <----  FAILS HERE

I am assuming the actual error is 1156 - ER_NET_PACKETS_OUT_OF_ORDER

Which I have been researching but have found very little information on.

What generally is the cause of this error?

Thank you for the feedback.

But minimal test case is necessary. Error message "Got packets out of order" is very common and usually happens when client processes data, received from the server, in wrong order. For example, when you try fetch results before receiving any or when you try to fetch second row of multiple-record results without processing earlier once. In most cases this is not result of a bug in MySQL code.

Is the test code I have given sufficient?

Given your explanation why would a "Got packets out of order" happen when executing an update with a prepared Statement?

David,
Are you using any connection properties? Are you performing other operations on the connection before issuing the update statement?

The Connection URL is:  

jdbc:mysql://OKCTFMPRD001:3306/?noAccessToProcedureBodies=true

No additional properties are used.

We do reuse connections - but in a very controlled way.

As mentioned, this problem has not been seen before although the same code has been run, probably 10's of 1,000 of times.

Your best bet for further diagnosis on this issue is to get a network capture when the problem is observed, using traceProtocol=true.  That will generate a huge amount of output, of course, so being able to isolate the problem into a repeatable test case is critical.

Unless there's indication that the root cause of the problem is MySQL software, this seems to be more appropriately handled via support, where a more comprehensive analysis of the deployment environment and use case can be done.

David,
Is it possible to enable the "protocolTrace" option on the workstation exhibiting the problem? It will produce a large amount of output, but will include the communication details between the client and server allowing us to further isolate this issue.

From: http://wiki.cedar-solutions.com/MySQLProtocolTrace

It looks like ProtocalTrace can only be turned on from the JDBC connection URL.

Is there another way to enable tracing - as the above will require modifications to an application which is already on our clients site?

David,
Changing the option on the client is the only way to get a protocol trace from the driver. If you access to the server and can run wireshark/tshark on the connection from this client, the dump from these would also aid in debugging the problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".