Re: AutoReconnect problem

[Pål Arne Hoff]

Mark Matthews wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Pål Arne Hoff wrote:
> 
>> I have a autoReconnect problem with Connector/J 3.0.4 (The problem 
>> seems to be in 3.0.2 and 3.0.3 too, but I seem to remember not having 
>> the problem with 3.0.1).
>>
>> I'm running MySQL Max 3.23.54a in Linux, using JBoss 2.4.10 and 
>> JRockit 7.0 JVM (I also tested with Sun's JVM 1.4.1_01 and JRockit 8.0b).
>>
>> I connect to MySQL through JBoss using this URL: 
>> jdbc:mysql://localhost/bms_company?autoReconnect=true. Then every 
>> morning when the connection hasn't been used over night, I get a 
>> NullPointerException when I try to connect through our application. 
>> The same thing happens if I restart MySQL without restarting JBoss. If 
>> I change to mm.mysql-2.0.14, everything works just fine.
>>
>> Is this a bug in the new driver?
> 
> 
> Someone else with a similar problem has brought something to my 
> attention that I should have warned you about. Are you setting 
> autoCommit(true) at the end of every transaction? The later versions of 
> the driver do not allow autoReconnect when autoCommit is false, because 
> it could lead to a non-transactional state. It is only safe to reconnect 
> when autoCommit=true. If you setAutoCommit(true) after you 
> commit/rollback a transaction, and then set it to 'false' when you 
> begin, you should get the behavior you desire.
> 

I'm just reading from the database to display data using jsp and servlet 
for the time being, so I haven't implemented transactions. Maybe I 
should do that anyway.


> If you're using a connection pool, it should be doing this for you, as 
> the JDBC spec states that autoCommit='true' is the default state for all 
> new connections, and connection pools, even though re-using existing 
> connections, should set the default state of the connection so that it 
> appears to be a 'new' one.
> 

I am using JBoss' connection pool, or at least I thought so (I'm no big 
expert on JBoss). The configuration used in JBoss is provided 
underneath. I think it might be a problem that I use XADataSourceLoader 
when I don't use transactions, but I'm not sure what else to use...

   <mbean code="org.jboss.jdbc.XADataSourceLoader" 
name="DefaultDomain:service=XADataSource,name=bmsds">
     <attribute 
name="DataSourceClass">org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl</attribute>
     <attribute name="PoolName">bmsds</attribute>
     <attribute 
name="URL">jdbc:mysql://localhost/bms_company?autoReconnect=true</attribute>
     <attribute name="JDBCUser">xxxxxxx</attribute>
     <attribute name="Password">xxxxxxxx</attribute>
     <attribute name="MaxSize">30</attribute>
     <attribute name="MinSize">0</attribute>
   </mbean>


If you still want the stacktrace, here it is:

java.sql.SQLException: Communication link failure: java.io.IOException
java.sql.SQLException: Communication link failure: java.io.IOException
com.mysql.jdbc.MysqlIO.sendCommand(ILjava.lang.String;Lcom.mysql.jdbc.Buffer;)Lcom.mysql.jdbc.Buffer;(MysqlIO.java:931)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(Lcom.mysql.jdbc.Buffer;ILcom.mysql.jdbc.Connection;IZLjava.lang.String;)Lcom.mysql.jdbc.ResultSet;(MysqlIO.java:1066)
com.mysql.jdbc.Connection.execSQL(Ljava.lang.String;ILcom.mysql.jdbc.Buffer;IZZLjava.lang.String;)Lcom.mysql.jdbc.ResultSet;(Connection.java:1728)
com.mysql.jdbc.PreparedStatement.executeQuery()Ljava.sql.ResultSet;(PreparedStatement.java:1437)
org.jboss.pool.jdbc.PreparedStatementInPool.executeQuery()Ljava.sql.ResultSet;(PreparedStatementInPool.java:71)


Cheers,
Pål Arne Hoff