mysql connector/j 5.1.16 + sphinxql 0.9.9 = MySQLNonTransientConnectionException

most recent connector/j – 5.1.16 does not seem to work with sphinx search 0.9.9. i did not find [yet] actual cause of the problem but going back to 5.1.15 solved the problem for now.

sometimes it’s good to refrain from reckless upgrades… when testing my setup i found that

symptoms:

import java.io.*;
import java.sql.*;
import java.util.*;
public class A{
        public static void main(String[] args) throws Exception{
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                Connection conn = DriverManager.getConnection("jdbc:mysql://host:9306/data");
        }
}

when run with connector/j 5.1.15 or older works fine, but with 5.1.16 give me:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        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:1013)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2412)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:207)
        at A.main(A.java:9)
Caused by: java.lang.NullPointerException
        at com.mysql.jdbc.ConnectionImpl.isAutoCommitNonDefaultOnServer(ConnectionImpl.java:3619)
        at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3494)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2385)
        ... 13 more

most probably this is caused by not-so-good implementation of mysql protocol by sphinx in 0.9.9, but.. it seems i’ll be stuck with that version of search engine [un]patiently waiting for stable 2.x release. i googled but did not find anything reasonable. this seems to be close, but adding relaxAutoCommit=false in the connection string did not help. will referencing to probably only reference in the world to my blog post cause some critical exception?

as a side note – i’ve been using sphinx 0.9x-0.99 for over two years in the production environment. it helps me to search over 6M of records – not a lot in the internet scale but quite nice a number. despite minor hiccups mentioned here – it works very well.