Hello:
I have recently posted the message attached at the bottom of
this one to the mailing list. Since then, I have continued to
work the sporadic and troublesome errors that are described in
that attached message on otherwise perfectly working and proven
code. I now have some insights that I would like to share with
the group and solicit their thoughts and ideas as to what the
root cause(s) may be.
Update and progress:
In continuing to work towards identifying the cause of this
problem, I have recently modified my Connection URLs to disable
the new (4.1) server based PreparedStatements (i.e. added
&useServerPrepStmts=false to JDCB URL string. This appears to
be a huge key as all of my sporadic errors as described below
have magically gone away with NO other changes to the source
code, DB, or environment. From this, I am assuming that there
are still some remaining bugs in the 4.1.5-gamma DB and/or both
the mysql-connector-java-3.1.4-beta-bin.jar and the mysql-
connector-java-3.0.15-ga-bin.jar Connector/J drivers related to
server PreparedStatements. For the time being, running without
the server PreparedStatements has me working again - however,
I'd obvously like to take advantage of them as soon as possible
for the performance advantage they can offer.
Can anyone confirm this theory and/or shed any light on the
issue or expected fixes?
Thanks again in advance!
Todd
Original posting of problem details follows:
----------------------
Hello:
I am having a problem with an (not)updatable ResultSet that I
cannot
figure out. The problem is not consistent in that the exact SQL
statements and tables involved will work one time and then fail
the
next. There does not appear to be any rhyme or reason as to
when or why
it fails. Once it does fail, simply retrying (perhaps several
times)
get's me back to a working state. I have already looked
extensivley for
any sort of non-closed Connection, ResultSet, etc. and have found
nothing. Additionally, I have isolated this in order to ensure
there
are no Threading considerations.
Here is the table involved:
CREATE TABLE OSECRegistration
(
id VARCHAR(255) NOT NULL,
hostName VARCHAR(255) NOT NULL,
platformType VARCHAR(255) NOT NULL,
registrationTime BIGINT NOT NULL,
createdBy VARCHAR(255) NOT NULL,
dateCreated BIGINT NOT NULL,
modifiedBy VARCHAR(255) NOT NULL,
dateModified BIGINT NOT NULL,
PRIMARY KEY( id ),
UNIQUE ( hostName, platformType ),
INDEX id_index ( id ),
INDEX PlatformType_index ( platformType ),
FOREIGN KEY PlatformType_key ( platformType )
REFERENCES PlatformType ( platformType )
)
TYPE=InnoDB;
The SQL that I am using in a PreparedStatement is as follows:
String stmt = "select id, registrationTime, modifiedBy,
dateModified
from OSECRegistration where ( hostName = ? ) AND platformType
= ? for
update";
I prepare the statement as follows:
ps = con.prepareStatement( stmt,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE );
I then resolve the '?' values and:
ResultSet rs = ps.executeQuery();
and walk through the ResultSet modifying the required fields with
statements
like:
rs.updateLong( i, ((Long) newValue).longValue() );
As far as I can tell, all of the above is correct. Yet, I still
inconsistantly get errors such as the following:
[junit] com.mysql.jdbc.NotUpdatable: Result Set not
updatable.This
result set must come from a statement that was created with a
result set
type of ResultSet.CONCUR_UPDATABLE, the query must select only
one
table, and must select all primary keys from that table. See the
JDBC
2.1 API Specification, section 5.6 for more details.
[junit] at
com.mysql.jdbc.UpdatableResultSet.generateStatements
(UpdatableResultSet.java:1770)
[junit] at
com.mysql.jdbc.UpdatableResultSet.syncUpdate
(UpdatableResultSet.java:1959)
[junit] at
com.mysql.jdbc.UpdatableResultSet.updateLong
(UpdatableResultSet.java:1343)
[junit] at
com.ensuren.ose.common.registration.OSECRegistrationJDBCDao.updat
eResultSet(OSECRegistrationJDBCDao.java:2298)
[junit] at
com.ensuren.ose.common.registration.OSECRegistrationJDBCDao.readF
orUpdate(OSECRegistrationJDBCDao.java:724)
[junit] at
com.ensuren.ose.server.registration.RegistrationBean.register
(RegistrationBean.java:140)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0
(Native
Method)
[junit] at
sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:39)
[junit] at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
[junit] at java.lang.reflect.Method.invoke
(Method.java:324)
[junit] at
org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invo
ke(StatelessSessionContainer.java:683)
[junit] at
org.jboss.resource.connectionmanager.CachedConnectionInterceptor.
invoke(CachedConnectionInterceptor.java:185)
[junit] at
org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke
(StatelessSessionInstanceInterceptor.java:72)
[junit] at
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext
(AbstractTxInterceptor.java:84)
[junit] at
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions
(TxInterceptorCMT.java:315)
[junit] at
org.jboss.ejb.plugins.TxInterceptorCMT.invoke
(TxInterceptorCMT.java:148)
[junit] at
org.jboss.ejb.plugins.SecurityInterceptor.invoke
(SecurityInterceptor.java:120)
[junit] at
org.jboss.ejb.plugins.LogInterceptor.invoke
(LogInterceptor.java:191)
[junit] at
org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke
(ProxyFactoryFinderInterceptor.java:122)
[junit] at
org.jboss.ejb.StatelessSessionContainer.internalInvoke
(StatelessSessionContainer.java:331)
[junit] at org.jboss.ejb.Container.invoke
(Container.java:723)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0
(Native
Method)
[junit] at
sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:39)
[junit] at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
[junit] at java.lang.reflect.Method.invoke
(Method.java:324)
[junit] at
org.jboss.mx.server.ReflectedDispatcher.dispatch
(ReflectedDispatcher.java:60)
[junit] at
org.jboss.mx.server.Invocation.dispatch(Invocation.java:61)
[junit] at
org.jboss.mx.server.Invocation.dispatch(Invocation.java:53)
[junit] at
org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
[junit] at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:185)
[junit] at
org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:473)
[junit] at
org.jboss.invocation.http.server.HttpInvoker.invoke
(HttpInvoker.java:163)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0
(Native
Method)
[junit] at
sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:39)
[junit] at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
[junit] at java.lang.reflect.Method.invoke
(Method.java:324)
[junit] at
org.jboss.mx.server.ReflectedDispatcher.dispatch
(ReflectedDispatcher.java:60)
[junit] at
org.jboss.mx.server.Invocation.dispatch(Invocation.java:61)
[junit] at
org.jboss.mx.server.Invocation.dispatch(Invocation.java:53)
[junit] at
org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
[junit] at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:185)
[junit] at
org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:473)
[junit] at
org.jboss.invocation.http.servlet.InvokerServlet.processRequest
(InvokerServlet.java:147)
[junit] at
org.jboss.invocation.http.servlet.InvokerServlet.doPost
(InvokerServlet.java:209)
[junit] at
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
[junit] at
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
[junit] at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
(ApplicationFilterChain.java:237)
[junit] at
org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:157)
[junit] at
org.apache.catalina.core.StandardWrapperValve.invoke
(StandardWrapperValve.java:214)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:104)
[junit] at
org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520)
[junit] at
org.apache.catalina.core.StandardContextValve.invokeInternal
(StandardContextValve.java:198)
[junit] at
org.apache.catalina.core.StandardContextValve.invoke
(StandardContextValve.java:152)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:104)
[junit] at
org.jboss.web.tomcat.security.SecurityAssociationValve.invoke
(SecurityAssociationValve.java:72)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:102)
[junit] at
org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke
(JBossSecurityMgrRealm.java:275)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:102)
[junit] at
org.apache.catalina.authenticator.AuthenticatorBase.invoke
(AuthenticatorBase.java:462)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:102)
[junit] at
org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520)
[junit] at
org.apache.catalina.core.StandardHostValve.invoke
(StandardHostValve.java:137)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:104)
[junit] at
org.apache.catalina.valves.ErrorReportValve.invoke
(ErrorReportValve.java:117)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:102)
[junit] at
org.apache.catalina.valves.AccessLogValve.invoke
(AccessLogValve.java:535)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:102)
[junit] at
org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520)
[junit] at
org.apache.catalina.core.StandardEngineValve.invoke
(StandardEngineValve.java:109)
[junit] at
org.apache.catalina.core.StandardValveContext.invokeNext
(StandardValveContext.java:104)
[junit] at
org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520)
[junit] at
org.apache.catalina.core.ContainerBase.invoke
(ContainerBase.java:929)
[junit] at
org.apache.coyote.tomcat5.CoyoteAdapter.service
(CoyoteAdapter.java:160)
[junit] at
org.apache.coyote.http11.Http11Processor.process
(Http11Processor.java:799)
[junit] at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.p
rocessConnection(Http11Protocol.java:705)
My environment is as follows:
Mandrake 9.2
MySQL 4.1.4-gamma-standard
Using mysql-connector-java-3.1.4-beta-bin.jar
(I have also tried the 3.0.14 production driver with the same
results)
and JBoss 3.2.5
Finally, I am acquiring the Connection as follows (i.e. I am not
using
the JBoss jndi DataSource):
Class.forName( "com.mysql.jdbc.Driver" );
Connection con = DriverManager.getConnection(
props.getProperty(
"jdbc:mysql://localhost:3306/mydb" ),
props.getProperty( "myuser" ),
props.getProperty
( "mypassword" ) );
con.setAutoCommit( false );
Again, all of the above works perfectly much of the time.
However,
sporadically it fails with the NotUpdatable ResultSet error.
Once it
has failed, it takes multiple repreated attempts until it
finally goes
back to working again.
I would greatly appreciate any and all assistance with
determining what
I am doing wrong. This problem has been frustrating me for
several
weeks now and I need some suggestions please.
Thanks in advance,
Todd
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
