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.updateResultSet(OSECRegistrationJDBCDao.java:2298)
[junit] at
com.ensuren.ose.common.registration.OSECRegistrationJDBCDao.readForUpdate(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.invoke(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.processConnection(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]
