Steve
----- Original Message ----- From: "Werner Guttmann" <[EMAIL PROTECTED]>
To: <[email protected]> Sent: Wednesday, May 14, 2008 5:36 AMSubject: 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:Yes but I do not think it is really a bug in castor. The connection poolreset 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 toclasses 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 amusing 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 PMSubject: Re: [castor-user] read-only connection for generating keysfor 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 Jiraissue) 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 objectusing 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
