java - SQLException: Protocol violation. Oracle JDBC Driver issue

Question

I'm getting the following excpetion:

java.sql.SQLException: Protocol violation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:190)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:286)
at oracle.jdbc.driver.T4C80all.receive(T4C80all.java:766)
at oracle.jdbc.driver.T4CPreparedStatement.do0all8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1225)
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:373)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:284)

The Oracle system is running 10.2.0.3.0 on Solaris 5.10. The jdbc driver is running on JDK 1.6.0_21 (if it's import the java is running on a Solaris 5.10 machine as well). I've tried several different oracle thin drivers including the latest and the one that appears to exactly match the oracle version.

The query I'm running is fairly simple: "select * from some_table order by key1, key2, key3" Then iterating through the result set and writing to a file. The table has around 12 million rows, so I expect the process is running long, but it seems to die within 5-15 mins into it. Each time I run it, it blows up on a different row, so I don't think the problem is with the data.

I found the oracle alert log but I couldn't tell that anything in there was related to my process. Still, I'm no oracle expert and perhaps there's an oracle setting I need to look at. Strangely enough, I'm running about five of these type of queries (a couple are a bit more complicated) on different connections and only two simplest ones ever get this problem.

Any help or ideas on what to look at to narrow down the problem would be appreciated.

Answer 1

For future googlers who are have to this page, here is the problem we had . The protocol violation exception was being logged on application logs and Oracle trace.

Oracle trace

This is error from oracle trace files

--- PROTOCOL VIOLATION DETECTED ---

----- Dump Cursor sql_id=1j5kjnkncpp xsc=0x2a053a2a0 cur=0x2a052f1cf0 ---
----- Current SQL Statement for this session (sql_id=1jjns4k6npp) -----
        select xyz

From Application Logs

Caused by: org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [72000]; error code [20000];

Symptom

This exception was happening occasionally. The stack trace had different sql in it which was very confusing. Running the sql with sql plus worked fine.

Root Cause

The exception was thrown when oracle driver was trying to export a CLOB data. This was happening with only few records, not all of them. The data as such was a file. Visually we could not make out what was wrong with that data.

Why we were seeing errors in oracle logs ?

So if this was a driver defect, why did we see the error in oracle trace ? Logically the driver errors should be only confined to application logs. The reasons was that when protocol violation happened, the connection got corrupted. This connection was returned to the connection pool. Any user or job when will use that connection would not work and would experience error. That is why it will happen at random places, with random users

Solution

A short term fix was to change this property in connection pool. We are using DBCP connection pool.

Changed from ds.setTestOnBorrow(false); to ds.setTestOnBorrow(true);

Now when the pool returns a corrupted connection to the pool, before app borrows this connection , it would test for validity. If connection is unusable, pool would discard and then app gets a new/valid connection.

If you enable connection pool logs, you should see the exception which normally is swallowed.

Driver Upgrade

Upgrade to OJDBC 12.1.0.2 from OJDBC 12.1.0.1 solved the problem, even for the problematic rows.

Some other links for reference

https://confluence.atlassian.com/display/CONFKB/java.sql.SQLException%3A+Protocol+violation+caught+while+accessing+a+page+and+Oracle+DB+is+used