Yes, driver-specific in that a few RDBMS do *not* support read-only connections. But that should not be the case in your environment.
Let know whether using this property makes a difference for you .... otherwise we'll have to add some code to how connections are being obtained. Werner Stephen Ince wrote: > No. I did not. I see that it is driver specific. Hmmm. The error only > appeared when I used connection pooling. > > Steve > > > ----- Original Message ----- From: "Werner Guttmann" > <[EMAIL PROTECTED]> > To: <[email protected]> > Sent: Wednesday, May 14, 2008 5:36 AM > Subject: Re: [castor-user] read-only connection for generating keys for > postgres. > > >> Stephen, >> >> have you had a look at >> >> http://commons.apache.org/dbcp/configuration.html >> >> and the 'defaultReadOnly' parameter in particular ? >> >> Werner >> >> Stephen Ince wrote: >>> Tomcat 4.1.36 comes with apache db common connection pooling. The reason >>> that hsqldb,db2, oracle and mysql don't not see it, is that the >>> respective jdbc drivers must be handling the error case and doing the >>> switch from readOnly == true --> readOnly == false. >>> >>> commons-dbcp-1.2.1.jar >>> commons-pool-1.3.jar >>> >>> jdbc pool configuration >>> ------------------------------------------------------------------------------ >>> >>> >>> <Resource name="jdbc/ods" type="javax.sql.DataSource"/> >>> <ResourceParams name="jdbc/ods"> >>> <parameter> >>> <name>factory</name> >>> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value> >>> </parameter> >>> <parameter> >>> <name>maxActive</name> >>> <value>8</value> >>> </parameter> >>> <parameter> >>> <name>maxWait</name> >>> <value>120000</value> >>> </parameter> >>> <parameter> >>> <name>username</name> >>> <value>postgres</value> >>> </parameter> >>> <parameter> >>> <name>password</name> >>> <value>postgres</value> >>> </parameter> >>> <parameter> >>> <name>driverClassName</name> >>> <value>org.postgresql.Driver</value> >>> </parameter> >>> <parameter> >>> <name>url</name> >>> <value>jdbc:postgresql://localhost:5432/ods</value> >>> </parameter> >>> <parameter> >>> <name>validationQuery</name> >>> <value>select count(*) from users</value> >>> </parameter> >>> <parameter> >>> <name>removeAbandoned</name> >>> <value>true</value> >>> </parameter> >>> <parameter> >>> <name>removeAbandonedTimeout</name> >>> <value>120</value> >>> </parameter> >>> <parameter> >>> <name>testWhileIdle</name> >>> <value>true</value> >>> </parameter> >>> <parameter> >>> <name>testOnReturn</name> >>> <value>true</value> >>> </parameter> >>> <parameter> >>> <name>numTestsPerEvictionRun</name> >>> <value>8</value> >>> </parameter> >>> <parameter> >>> <name>timeBetweenEvictionRunsMillis</name> >>> <value>500000</value> >>> </parameter> >>> <parameter> >>> <name>minEvictableIdleTimeMillis</name> >>> <value>500000</value> >>> </parameter> >>> </ResourceParams> >>> Steve >>> ----- Original Message ----- From: "Werner Guttmann" >>> <[EMAIL PROTECTED]> >>> To: <[email protected]> >>> Sent: Wednesday, May 14, 2008 3:45 AM >>> Subject: Re: [castor-user] read-only connection for generating keys for >>> postgres. >>> >>> >>>> No problem. But let's think aloud a bit more: what connection pool are >>>> you using, if I may ask ? >>>> >>>> Werner >>>> >>>> Stephen Ince wrote: >>>>>>> reset jdbc connection in >>>>>>> org.castor.persist.LocalTransactionContext.createConnection() >>>>>> Hmm, trying to get my head around this .. ;-). I guess you are saying >>>>>> that - once a connection is being re-used - it will have a wrong >>>>>> state >>>>>> associated upon retrieval from the connection pool, correct ? >>>>>> Ideally, >>>>>> it would be nice if such code could be added to >>>>> Yes but I do not think it is really a bug in castor. The connection >>>>> pool >>>>> classes should reset the connections. >>>>> I did this in Castor because it was easier. >>>>> >>>>> ----- Original Message ----- From: "Werner Guttmann" >>>>> <[EMAIL PROTECTED]> >>>>> To: <[email protected]> >>>>> Sent: Tuesday, May 13, 2008 4:07 AM >>>>> Subject: Re: [castor-user] read-only connection for generating keys >>>>> for >>>>> postgres. >>>>> >>>>> >>>>>> Stephen Ince wrote: >>>>>>> FYI. >>>>>>> I was able to get my app working with castor/postgres and derby. >>>>>>> I am >>>>>>> using castor 1.01. It seems to be a lot faster than the current >>>>>>> release. >>>>>>> >>>>>>> FIXES and work arounds. >>>>>>> 1) Changed to using IDENTITY key generator from HIGH/LOW. Over a >>>>>>> period >>>>>>> of time (week) the high/low seems to get out of sync. >>>>>>> >>>>>>> 2) ReadOnly transaction issue. This is not really a castor issue but >>>>>>> apache/db connection pooling issue. It is not resetting the >>>>>>> connections. >>>>>>> Work around. >>>>>>> >>>>>>> reset jdbc connection in >>>>>>> org.castor.persist.LocalTransactionContext.createConnection() >>>>>> Hmm, trying to get my head around this .. ;-). I guess you are saying >>>>>> that - once a connection is being re-used - it will have a wrong >>>>>> state >>>>>> associated upon retrieval from the connection pool, correct ? >>>>>> Ideally, >>>>>> it would be nice if such code could be added to >>>>>> Database.close()/TransactionContext.close(), but that would imply >>>>>> that >>>>>> we woul dhave to force folks to close a Database instance (rather >>>>>> than >>>>>> waiting for it to be 'auto-closed'. >>>>>> >>>>>> >>>>>>> conn.setReadOnly(false) >>>>>>> >>>>>>> 3) Derby identity generator bug. >>>>>>> In >>>>>>> org.exolab.castor.jdo.keygen.IdentityKeyGenerator >>>>>>> >>>>>>> public void supportsSqlType( int sqlType ) >>>>>>> throws MappingException >>>>>>> { >>>>>>> if (sqlType != Types.INTEGER && >>>>>>> sqlType != Types.NUMERIC && >>>>>>> sqlType != Types.DECIMAL && >>>>>>> sqlType != Types.BIGINT) { >>>>>>> throw new MappingException( >>>>>>> Messages.format("mapping.keyGenSQLType", >>>>>>> getClass().getName(), new Integer(sqlType))); >>>>>>> } >>>>>>> >>>>>>> if (sqlType != Types.INTEGER && >>>>>>> fName.equals("hsql")) { >>>>>>> throw new MappingException( >>>>>>> Messages.format("mapping.keyGenSQLType", >>>>>>> getClass().getName(), new Integer(sqlType))); >>>>>>> } >>>>>>> >>>>>>> // ODS: fix >>>>>>> if (sqlType == Types.NUMERIC && >>>>>>> fName.equals("derby")) { >>>>>>> throw new MappingException( >>>>>>> Messages.format("mapping.keyGenSQLType", >>>>>>> getClass().getName(), new Integer(sqlType))); >>>>>>> } >>>>>>> } >>>>>>> >>>>>>> Steve >>>>>>> ----- Original Message ----- From: "Stephen Ince" >>>>>>> <[EMAIL PROTECTED]> >>>>>>> To: <[email protected]> >>>>>>> Sent: Wednesday, January 30, 2008 1:19 PM >>>>>>> Subject: Re: [castor-user] read-only connection for generating keys >>>>>>> for >>>>>>> postgres. >>>>>>> >>>>>>> >>>>>>>> Werner, >>>>>>>> I am having a hard time reproducing "read-only transaction" case >>>>>>>> outside of my webapp. I am also getting the read-only error for >>>>>>>> MAX-KEY key generation. Any suggestions on how I can reproduce >>>>>>>> it. I >>>>>>>> am thinking about spawning a few threads and do not close the jdo >>>>>>>> sessions. Sofar the single threaded case is not reproducible. >>>>>>>> >>>>>>>> Here is a the stack trace for the webapp. >>>>>>>> >>>>>>>> >>>>>>>> a.. This Database operation failed: : SQL exception in the key >>>>>>>> generator org.exolab.castor.jdo.keygen.MaxKeyGenerator: >>>>>>>> org.postgresql.util.PSQLException: ERROR: transaction is >>>>>>>> read-only.: >>>>>>>> ERROR: transaction is read-only >>>>>>>> >>>>>>>> >>>>>>>> Caused by: org.postgresql.util.PSQLException: ERROR: transaction is >>>>>>>> read-only >>>>>>>> at >>>>>>>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx >>>>>>>> >>>>>>>> ecutorImpl.java:1548) >>>>>>>> at >>>>>>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor >>>>>>>> >>>>>>>> Impl.java:1316) >>>>>>>> at >>>>>>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja >>>>>>>> >>>>>>>> va:191) >>>>>>>> at >>>>>>>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat >>>>>>>> >>>>>>>> ement.java:452) >>>>>>>> at >>>>>>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract >>>>>>>> >>>>>>>> Jdbc2Statement.java:351) >>>>>>>> at >>>>>>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc >>>>>>>> >>>>>>>> 2Statement.java:255) >>>>>>>> at >>>>>>>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(Dele >>>>>>>> >>>>>>>> gatingPreparedStatement.java:92) >>>>>>>> at >>>>>>>> org.exolab.castor.jdo.keygen.MaxKeyGenerator.generateKey(MaxKeyGenera >>>>>>>> >>>>>>>> tor.java:162) >>>>>>>> at >>>>>>>> org.exolab.castor.jdo.engine.SQLStatementCreate.generateKey(SQLStatem >>>>>>>> >>>>>>>> entCreate.java:384) >>>>>>>> at >>>>>>>> org.exolab.castor.jdo.engine.SQLStatementCreate.executeStatement(SQLS >>>>>>>> >>>>>>>> tatementCreate.java:189) >>>>>>>> at >>>>>>>> org.exolab.castor.jdo.engine.SQLEngine.create(SQLEngine.java:313) >>>>>>>> at >>>>>>>> org.exolab.castor.persist.ClassMolder.create(ClassMolder.java:694) >>>>>>>> at >>>>>>>> org.exolab.castor.persist.LockEngine.create(LockEngine.java:532) >>>>>>>> at >>>>>>>> org.castor.persist.AbstractTransactionContext.walkObjectsToBeCreated( >>>>>>>> >>>>>>>> AbstractTransactionContext.java:817) >>>>>>>> at >>>>>>>> org.castor.persist.AbstractTransactionContext.create(AbstractTransact >>>>>>>> >>>>>>>> ionContext.java:777) >>>>>>>> at >>>>>>>> org.exolab.castor.jdo.engine.AbstractDatabaseImpl.create(AbstractData >>>>>>>> >>>>>>>> baseImpl.java:302) >>>>>>>> >>>>>>>> >>>>>>>> Steve >>>>>>>> ----- Original Message ----- From: "Werner Guttmann" >>>>>>>> <[EMAIL PROTECTED]> >>>>>>>> To: <[email protected]> >>>>>>>> Sent: Friday, January 25, 2008 6:22 PM >>>>>>>> Subject: Re: [castor-user] read-only connection for generating keys >>>>>>>> for postgres. >>>>>>>> >>>>>>>> >>>>>>>>> I think I'll need a full test case so that I am able to run it >>>>>>>>> against e.g. Derby. Can you please supply us with one ? >>>>>>>>> >>>>>>>>> Werner >>>>>>>>> >>>>>>>>> Stephen Ince wrote: >>>>>>>>>> Werner, >>>>>>>>>> The read-only connection error showed up again on postgres. So >>>>>>>>>> "<param name="same-connection" value="false"/>" didn't resolve >>>>>>>>>> the >>>>>>>>>> problem. This problem only occurs with postgresql and derby. >>>>>>>>>> Hsqldb, db2, oracle and sqlserver all work fine. I think it has >>>>>>>>>> something to do with getting a connection from a pool that was >>>>>>>>>> previously set to read-only. >>>>>>>>>> >>>>>>>>>> Steve >>>>>>>>>> >>>>>>>>>> A fatal error occurred while creating/updating >>>>>>>>>> com.opendemand.security.UserProfi >>>>>>>>>> le using SQL: UPDATE "users" SET >>>>>>>>>> "user_name"=?,"title"=?,"fname"=?,"minitial"=?, >>>>>>>>>> "lname"=?,"passwd"=?,"role"=?,"status"=?,"license"=?,"total_test_ran"=?,"test_ti >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> me"=?,"last_login_time"=?,"test_time_elapsed"=?,"group_id"=?,"company"=?,"accept >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> _license"=?,"email"=?,"url"=?,"referred_by"=?,"telephone"=?,"fax"=?,"address"=?, >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> "city"=?,"state"=?,"zipcode"=?,"country"=? WHERE "user_id"=? >>>>>>>>>> org.postgresql.util.PSQLException: ERROR: transaction is >>>>>>>>>> read-only >>>>>>>>>> at >>>>>>>>>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> ecutorImpl.java:1548) >>>>>>>>>> at >>>>>>>>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Impl.java:1316) >>>>>>>>>> at >>>>>>>>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Steve >>>>>>>>>> ----- Original Message ----- From: "Werner Guttmann" >>>>>>>>>> <[EMAIL PROTECTED]> >>>>>>>>>> To: <[email protected]> >>>>>>>>>> Sent: Friday, January 25, 2008 4:30 PM >>>>>>>>>> Subject: Re: [castor-user] read-only connection for generating >>>>>>>>>> keys >>>>>>>>>> for postgres. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>>> Interesting, indeed. How did you come about this solution for >>>>>>>>>>> postgreSQL, if I may ask ? >>>>>>>>>>> >>>>>>>>>>> Werner >>>>>>>>>>> >>>>>>>>>>> Stephen Ince wrote: >>>>>>>>>>>> Werner, >>>>>>>>>>>> I think I solved the problem for postgres but not derby. >>>>>>>>>>>> <mapping> >>>>>>>>>>>> <key-generator name="HIGH-LOW" alias="keygen"> >>>>>>>>>>>> : >>>>>>>>>>>> <param name="same-connection" value="false"/> >>>>>>>>>>>> </key-generator> >>>>>>>>>>>> </mapping> >>>>>>>>>>>> This seemed to worked for postgres. With derby I get a similiar >>>>>>>>>>>> error. >>>>>>>>>>>> Nested error: java.sql.SQLException: An SQL data change is not >>>>>>>>>>>> permitted for a read-only connection, user or database.: An SQL >>>>>>>>>>>> data change is not permitted for a read-only connection, >>>>>>>>>>>> user or >>>>>>>>>>>> database. >>>>>>>>>>>> Steve >>>>>>>>>>>> ----- Original Message ----- >>>>>>>>>>>> From: "Werner Guttmann" <[EMAIL PROTECTED] >>>>>>>>>>>> <mailto:[EMAIL PROTECTED]>> >>>>>>>>>>>> To: <[email protected] >>>>>>>>>>>> <mailto:[email protected]>> >>>>>>>>>>>> Sent: Friday, January 25, 2008 3:48 PM >>>>>>>>>>>> Subject: Re: [castor-user] read-only connection for generating >>>>>>>>>>>> keys for postgres. >>>>>>>>>>>> >>>>>>>>>>>> > Stephen, >>>>>>>>>>>> > >>>>>>>>>>>> > can you supply me with a test case (packaged as part of a >>>>>>>>>>>> Jira >>>>>>>>>>>> issue) so >>>>>>>>>>>> > that I could easily replay this problem ? >>>>>>>>>>>> > >>>>>>>>>>>> > Regards >>>>>>>>>>>> > Werner >>>>>>>>>>>> > >>>>>>>>>>>> > Stephen Ince wrote: >>>>>>>>>>>> >> I am getting the following error when I try to save an >>>>>>>>>>>> object >>>>>>>>>>>> using a >>>>>>>>>>>> >> key sequence. >>>>>>>>>>>> >> This works fine for oracle, sqlserver, hsqldb and db2. >>>>>>>>>>>> >> >>>>>>>>>>>> >> Here is my database.xml. Is there something I can do it to >>>>>>>>>>>> force castor >>>>>>>>>>>> >> to use a different connection to get the keys or change >>>>>>>>>>>> connection from >>>>>>>>>>>> >> read-only to non read-only? >>>>>>>>>>>> >> >>>>>>>>>>>> >> org.exolab.castor.jdo.keygen.MaxKeyGenerator: >>>>>>>>>>>> >> org.postgresql.util.PSQLException: ERROR: transaction is >>>>>>>>>>>> read-only.: >>>>>>>>>>>> >> ERROR: transaction is read-only >>>>>>>>>>>> >> >>>>>>>>>>>> >> <!DOCTYPE mapping PUBLIC "-//EXOLAB/Castor Mapping DTD >>>>>>>>>>>> Version >>>>>>>>>>>> 1.0//EN" >>>>>>>>>>>> >> "http://castor.exolab.org/mapping.dtd";> >>>>>>>>>>>> >> <mapping> >>>>>>>>>>>> >> <key-generator name="HIGH-LOW" alias="keygen"> >>>>>>>>>>>> >> <param name="table" value="seq"/> >>>>>>>>>>>> >> <param name="key-column" value="seq_table_name"/> >>>>>>>>>>>> >> <param name="value-column" value="max_pk_value"/> >>>>>>>>>>>> >> <param name="grab-size" value="50"/> >>>>>>>>>>>> >> <param name="same-connection" value="true"/> >>>>>>>>>>>> >> </key-generator> >>>>>>>>>>>> >> </mapping> >>>>>>>>>>>> >> >>>>>>>>>>>> >> Steve >>>>>>>>>>>> >> >>>>>>>>>>>> >> >>>>>>>>>>>> --------------------------------------------------------------------- >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >> To unsubscribe from this list please visit: >>>>>>>>>>>> >> >>>>>>>>>>>> >> http://xircles.codehaus.org/manage_email >>>>>>>>>>>> >> >>>>>>>>>>>> >> >>>>>>>>>>>> > >>>>>>>>>>>> > >>>>>>>>>>>> > >>>>>>>>>>>> --------------------------------------------------------------------- >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> > To unsubscribe from this list please visit: >>>>>>>>>>>> > >>>>>>>>>>>> > http://xircles.codehaus.org/manage_email >>>>>>>>>>>> > >>>>>>>>>>>> > >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> --------------------------------------------------------------------- >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> To unsubscribe from this list please visit: >>>>>>>>>>> >>>>>>>>>>> http://xircles.codehaus.org/manage_email >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> --------------------------------------------------------------------- >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> To unsubscribe from this list please visit: >>>>>>>>>> >>>>>>>>>> http://xircles.codehaus.org/manage_email >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> --------------------------------------------------------------------- >>>>>>>>> >>>>>>>>> >>>>>>>>> To unsubscribe from this list please visit: >>>>>>>>> >>>>>>>>> http://xircles.codehaus.org/manage_email >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> --------------------------------------------------------------------- >>>>>>>> >>>>>>>> To unsubscribe from this list please visit: >>>>>>>> >>>>>>>> http://xircles.codehaus.org/manage_email >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> --------------------------------------------------------------------- >>>>>>> >>>>>>> To unsubscribe from this list, please visit: >>>>>>> >>>>>>> http://xircles.codehaus.org/manage_email >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> --------------------------------------------------------------------- >>>>>> To unsubscribe from this list, please visit: >>>>>> >>>>>> http://xircles.codehaus.org/manage_email >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> --------------------------------------------------------------------- >>>>> To unsubscribe from this list, please visit: >>>>> >>>>> http://xircles.codehaus.org/manage_email >>>>> >>>>> >>>>> >>>> >>>> >>>> --------------------------------------------------------------------- >>>> To unsubscribe from this list, please visit: >>>> >>>> http://xircles.codehaus.org/manage_email >>>> >>>> >>>> >>> >>> >>> --------------------------------------------------------------------- >>> To unsubscribe from this list, please visit: >>> >>> http://xircles.codehaus.org/manage_email >>> >>> >>> >> >> >> --------------------------------------------------------------------- >> To unsubscribe from this list, please visit: >> >> http://xircles.codehaus.org/manage_email >> >> >> > > > --------------------------------------------------------------------- > To unsubscribe from this list, please visit: > > http://xircles.codehaus.org/manage_email > > > --------------------------------------------------------------------- To unsubscribe from this list, please visit: http://xircles.codehaus.org/manage_email
