关于MySQL Proxy读写分离的问题

0 0

关于MySQL Proxy读写分离的问题25

最近做MYSQL主从复制、读写分离的事情，环境搭建完成，从MYSQL客户端测试主从复制，读写分离没有问题,但是一旦将其置入JBOSS就报下面的错误，再测试，如果直接连接数据库就没有问题，而且如果使用代理，竟然报的是org.hibernate.exception.GenericJDBCException,不知是何解，那位老兄出手相助下子.
环境如下：
Mysql 5.0.37 主从版本相同
MysqlProxy 0.8
Master 192.168.1.253
slave 192.168.1.128
代理地址：jdbc:mysql://192.168.1.253:4040/dbname
直接连接：jdbc:mysql://192.168.1.253:3306/dbname

mysqlproxy 启动脚本如下：
mysql-proxy --proxy-read-only-backend-addresses=192.168.1.128:3306 --proxy-backend-addresses=192.168.1.253:3306 --proxy-lua-script=E:/mysql/proxy/rw-splitting.lua&

还有个怪异的现象，就是一段时间后mysqlproxy连接会莫名奇妙的断开，而且启动的脚本监听不关闭，必须关闭，重新启动脚本，才能连接上，不知是何缘故

JBOSS控制台打印错误如下：
13:02:56,671 WARN [JDBCExceptionReporter] SQL Error: 1243, SQLState: HY000
13:02:56,671 ERROR [JDBCExceptionReporter] Unknown prepared statement handler (1) given to mysql_stmt_execute
13:02:56,687 ERROR [STDERR] org.hibernate.exception.GenericJDBCException: could not execute query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.loader.Loader.doList(Loader.java:2223)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
        at org.hibernate.loader.Loader.list(Loader.java:2099)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
        at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
        at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
        at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
        at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
        at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811)
        at com.saihu.auth.dao.AuthUserDAO.getUserByName(Unknown Source)
        at com.saihu.auth.service.UserService.getUserByName(Unknown Source)
        at com.saihu.auth.server.stub.ice.AuthConnectorI.getUserByName(Unknown Source)
        at com.saihu.auth.client.stub.ic.interfaces._AuthConnectorDisp.___getUserByName(_AuthConnectorDisp.java:169)
        at com.saihu.auth.client.stub.ic.interfaces._AuthConnectorDisp.__dispatch(_AuthConnectorDisp.java:224)
        at IceInternal.Incoming.invoke(Incoming.java:147)
        at Ice.ConnectionI.invokeAll(ConnectionI.java:2078)
        at Ice.ConnectionI.message(ConnectionI.java:1239)
        at IceInternal.ThreadPool.run(ThreadPool.java:788)
        at IceInternal.ThreadPool.access$100(ThreadPool.java:12)
        at IceInternal.ThreadPool$EventHandlerThread.run(ThreadPool.java:1248)
Caused by: java.sql.SQLException: Unknown prepared statement handler (1) given to mysql_stmt_execute
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2901)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1584)
        at com.mysql.jdbc.Connection.serverExecute(Connection.java:4742)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1251)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:993)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1657)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
        at org.hibernate.loader.Loader.doQuery(Loader.java:674)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        at org.hibernate.loader.Loader.doList(Loader.java:2220)
        ... 19 more
13:02:56,687 ERROR [STDERR] warning: dispatch exception:
identity: AuthProxy
facet:
operation: getUserByName
java.lang.NullPointerException
        at com.saihu.auth.server.stub.ice.AuthConnectorI.getUserByName(Unknown Source)
        at com.saihu.auth.client.stub.ic.interfaces._AuthConnectorDisp.___getUserByName(_AuthConnectorDisp.java:169)
        at com.saihu.auth.client.stub.ic.interfaces._AuthConnectorDisp.__dispatch(_AuthConnectorDisp.java:224)
        at IceInternal.Incoming.invoke(Incoming.java:147)
        at Ice.ConnectionI.invokeAll(ConnectionI.java:2078)
        at Ice.ConnectionI.message(ConnectionI.java:1239)
        at IceInternal.ThreadPool.run(ThreadPool.java:788)
        at IceInternal.ThreadPool.access$100(ThreadPool.java:12)
        at IceInternal.ThreadPool$EventHandlerThread.run(ThreadPool.java:1248)
13:02:56,703 ERROR [STDERR] Ice.UnknownException
    unknown = "java.lang.NullPointerException
                at com.saihu.auth.server.stub.ice.AuthConnectorI.getUserByName(Unknown Source)
                at com.saihu.auth.client.stub.ic.interfaces._AuthConnectorDisp.___getUserByName(_AuthConnectorDisp.java:169)
                at com.saihu.auth.client.stub.ic.interfaces._AuthConnectorDisp.__dispatch(_AuthConnectorDisp.java:224)
                at IceInternal.Incoming.invoke(Incoming.java:147)
                at Ice.ConnectionI.invokeAll(ConnectionI.java:2078)
                at Ice.ConnectionI.message(ConnectionI.java:1239)
                at IceInternal.ThreadPool.run(ThreadPool.java:788)
                at IceInternal.ThreadPool.access$100(ThreadPool.java:12)
                at IceInternal.ThreadPool$EventHandlerThread.run(ThreadPool.java:1248)
               "