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
