[Building Sakai] C3P0 configuration for BaseDataSource
Fischbein, Scott A.
sfischbein at ucdavis.edu
Fri Jul 9 13:56:05 PDT 2010
Hi all,
I've got a question about configuring the Sakai BaseDataSource to use C3P0 in 2.7
We've had this running in 2.5, but now that were upgrading, and dealing with the new kernel distribution, etc... I've found the db-components.xml file where the BaseDataSource is defined (the one which used to be in db-pack/components.xml) and when I make the equivalent change to that file (the one which we've been running in 2.5) I get a startup error complaining about being unable to set the property "url" on javax.sql.DataSrouce - in the past, I've seen this when I have a C3P0 DataSource defined in the db components.xml, but have DBCP parameters defined in sakai.properties (ie: "url=" for DBCP instead of "jdbcUrl=" for C3P0). I've confirmed that this is not the case (ie: my sakai.properties settings are appropriate for C3P0) but I'm still seeing this error. Any thoughts/suggestions?
I'm pasting my db-components.xml and the relevant sakai.properties entries below.
Thanks,
Scott
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<!-- database beans -->
<bean id="org.sakaiproject.db.impl.BasicSqlServiceSqlDefault" class="org.sakaiproject.db.impl.BasicSqlServiceSqlDefault" singleton="true"/>
<bean id="org.sakaiproject.db.impl.BasicSqlServiceSqlDb2" class="org.sakaiproject.db.impl.BasicSqlServiceSqlDb2" singleton="true"/>
<bean id="org.sakaiproject.db.impl.BasicSqlServiceSqlHSql" class="org.sakaiproject.db.impl.BasicSqlServiceSqlHSql" singleton="true"/>
<bean id="org.sakaiproject.db.impl.BasicSqlServiceSqlMsSql" class="org.sakaiproject.db.impl.BasicSqlServiceSqlMsSql" singleton="true"/>
<bean id="org.sakaiproject.db.impl.BasicSqlServiceSqlMySql" class="org.sakaiproject.db.impl.BasicSqlServiceSqlMySql" singleton="true"/>
<bean id="org.sakaiproject.db.impl.BasicSqlServiceSqlOracle" class="org.sakaiproject.db.impl.BasicSqlServiceSqlOracle" singleton="true"/>
<bean id="org.sakaiproject.db.api.SqlService"
class="org.sakaiproject.db.impl.BasicSqlService"
init-method="init" destroy-method="destroy" singleton="true"
depends-on="db.cache.SAKAI_SITE_PROPERTY,db.cache.SAKAI_SITE_PAGE_PROPERTY,db.cache.SAKAI_REALM_PROPERTY,db.cache.SAKAI_ALIAS_PROPERTY,db.cache.SAKAI_SITE_GROUP_PROPERTY,db.cache.SAKAI_USER_PROPERTY">
<lookup-method name="usageSessionService" bean="org.sakaiproject.event.api.UsageSessionService" />
<lookup-method name="threadLocalManager" bean="org.sakaiproject.thread_local.api.ThreadLocalManager" />
<property name="defaultDataSource">
<ref bean="javax.sql.DataSource" />
</property>
<property name="longDataSource">
<ref bean="javax.sql.LongDataSource" />
</property>
<property name="autoDdl">
<value>${auto.ddl}</value>
</property>
<property name="commitAfterRead">
<value>false</value>
</property>
<!-- HSQLDB configuration -->
<property name="vendor">
<value>hsqldb</value>
</property>
<property name="databaseBeans">
<map>
<entry key="default"><ref bean="org.sakaiproject.db.impl.BasicSqlServiceSqlDefault"/></entry>
<entry key="db2" ><ref bean="org.sakaiproject.db.impl.BasicSqlServiceSqlDb2" /></entry>
<entry key="hsqldb" ><ref bean="org.sakaiproject.db.impl.BasicSqlServiceSqlHSql" /></entry>
<entry key="mssql" ><ref bean="org.sakaiproject.db.impl.BasicSqlServiceSqlMsSql" /></entry>
<entry key="mysql" ><ref bean="org.sakaiproject.db.impl.BasicSqlServiceSqlMySql" /></entry>
<entry key="oracle" ><ref bean="org.sakaiproject.db.impl.BasicSqlServiceSqlOracle" /></entry>
</map>
</property>
</bean>
<!-- Base DataSource for all pooled database connections - for use with C3P0 - SAF -->
<bean id="javax.sql.BaseDataSource" abstract="true"
class="com.mchange.v2.c3p0.ComboPooledDataSource"
>
<!-- The fully qualified Java class name of the JDBC driver to be used. -->
<property name="driverClass">
<value>org.hsqldb.jdbcDriver</value>
</property>
<!-- The connection URL to be passed to our JDBC driver to establish a connection. -->
<property name="jdbcUrl">
<value>jdbc:hsqldb:mem:sakai</value>
</property>
<!-- The connection username to be passed to our JDBC driver to establish a connection. -->
<property name="user">
<value>sa</value>
</property>
<!-- The connection password to be passed to our JDBC driver to establish a connection. -->
<property name="password">
<value></value>
</property>
<!-- The initial number of connections that are created when the pool is started. -->
<property name="initialPoolSize">
<value>10</value>
</property>
<!-- The maximum number of active connections that can be allocated from this pool at the same time, or zero for no limit. -->
<property name="maxPoolSize">
<value>50</value>
</property>
<!-- The maximum number of active connections that can remain idle in the pool, without extra ones being released, or zero for no limit. -->
<property name="minPoolSize">
<value>10</value>
</property>
<!-- The maximum number of seconds that the pool will wait (when there
are no available connections) for a connection to be returned before
throwing an exception, or -1 to wait indefinitely. -->
<property name="maxIdleTime">
<value>20000</value>
</property>
<!-- If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.-->
<property name="idleConnectionTestPeriod">
<value>180</value>
</property>
<!-- If true, an operation will be performed asynchronously at every connection checkin to verify that the connection is valid. Use in combination with idleConnectionTestPeriod for quite reliable, always asynchronous Connection testing. Also, setting an
automaticTestTable or preferredTestQuery will usually speed up all connection tests. -->
<property name="testConnectionOnCheckin">
<value>true</value>
</property>
</bean>
<!-- Shared DataSource for all pooled database connections -->
<bean id="javax.sql.DataSource" parent="javax.sql.BaseDataSource">
</bean>
<!-- Proxy which will keep only get the connection if one is actually needed,
this is especially helpful when using hibernate or wrapping transactions around requests
because it keeps the connection from being opened and held if it is not needed -->
<bean id="javax.sql.LazyDataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
<property name="targetDataSource"><ref local="javax.sql.DataSource" /></property>
</bean>
<!-- The "slow" connection pool for streaming downloads from db -->
<bean id="javax.sql.LongDataSource"
parent="javax.sql.BaseDataSource">
</bean>
<!-- Base Hibernate SessionFactory -->
<bean
id="org.sakaiproject.springframework.orm.hibernate.SessionFactoryBase"
abstract="true"
class="org.sakaiproject.springframework.orm.hibernate.impl.ClassicLocalSessionFactoryBeanHibernate31">
<property name="dataSource">
<ref local="javax.sql.LazyDataSource" />
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.query.substitutions">
true 1, false 0, yes 'Y', no 'N'
</prop>
<prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.provider_class">
org.sakaiproject.springframework.orm.hibernate.SakaiCacheProvider
</prop>
</props>
</property>
<!--
This s
org.hibernate.cache.EhCacheProvider
-->
<!-- update the database schema when starting -->
<property name="schemaUpdate">
<value>${auto.ddl}</value>
</property>
</bean>
<bean id="org.sakaiproject.springframework.orm.hibernate.VendorHbmTransformer" class="org.sakaiproject.springframework.orm.hibernate.impl.VendorHbmTransformerImpl">
<property name="vendorTransforms">
<map>
<entry>
<key><value>db2</value></key>
<value>org/sakaiproject/springframework/orm/hibernate/dialect/db2/db2.xsl</value>
</entry>
</map>
</property>
</bean>
<!-- The "Global" Hibernate Session Factory -->
<bean
id="org.sakaiproject.springframework.orm.hibernate.GlobalSessionFactory"
parent="org.sakaiproject.springframework.orm.hibernate.SessionFactoryBase"
class="org.sakaiproject.springframework.orm.hibernate.AddableSessionFactoryBean">
</bean>
<!-- The "Global" Transaction Manager -->
<bean
id="org.sakaiproject.springframework.orm.hibernate.GlobalTransactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory">
<ref bean="org.sakaiproject.springframework.orm.hibernate.GlobalSessionFactory" />
</property>
<property name="dataSource" ref="javax.sql.LazyDataSource" />
</bean>
<bean id="org.sakaiproject.springframework.orm.hibernate.L2Cache"
class="org.springframework.cache.ehcache.EhCacheFactoryBean">
<property name="cacheManager"><ref bean="org.sakaiproject.memory.api.MemoryService.cacheManager"/></property>
<property name="cacheName"><value>org.sakaiproject.springframework.orm.hibernate.L2Cache</value></property>
<property name="maxElementsInMemory" ><value>100000</value></property>
<property name="diskPersistent" ><value>false</value></property>
<property name="eternal" ><value>false</value></property>
<property name="timeToLive" ><value>300</value></property>
<property name="timeToIdle" ><value>600</value></property>
</bean>
<!-- beans for use in caching DB flat (uses defaults) -->
<bean id="db.cache.SAKAI_ALIAS_PROPERTY"
class="org.springframework.cache.ehcache.EhCacheFactoryBean">
<property name="cacheManager" ref="org.sakaiproject.memory.api.MemoryService.cacheManager" />
<property name="cacheName" value="org.sakaiproject.db.BaseDbFlatStorage.SAKAI_ALIAS_PROPERTY" />
</bean>
<bean id="db.cache.SAKAI_SITE_PROPERTY"
class="org.springframework.cache.ehcache.EhCacheFactoryBean">
<property name="cacheManager" ref="org.sakaiproject.memory.api.MemoryService.cacheManager" />
<property name="cacheName" value="org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PROPERTY" />
</bean>
<bean id="db.cache.SAKAI_SITE_PAGE_PROPERTY"
class="org.springframework.cache.ehcache.EhCacheFactoryBean">
<property name="cacheManager" ref="org.sakaiproject.memory.api.MemoryService.cacheManager" />
<property name="cacheName" value="org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_PAGE_PROPERTY" />
</bean>
<bean id="db.cache.SAKAI_REALM_PROPERTY"
class="org.springframework.cache.ehcache.EhCacheFactoryBean">
<property name="cacheManager" ref="org.sakaiproject.memory.api.MemoryService.cacheManager" />
<property name="cacheName" value="org.sakaiproject.db.BaseDbFlatStorage.SAKAI_REALM_PROPERTY" />
</bean>
<bean id="db.cache.SAKAI_SITE_GROUP_PROPERTY"
class="org.springframework.cache.ehcache.EhCacheFactoryBean">
<property name="cacheManager" ref="org.sakaiproject.memory.api.MemoryService.cacheManager" />
<property name="cacheName" value="org.sakaiproject.db.BaseDbFlatStorage.SAKAI_SITE_GROUP_PROPERTY" />
</bean>
<bean id="db.cache.SAKAI_USER_PROPERTY"
class="org.springframework.cache.ehcache.EhCacheFactoryBean">
<property name="cacheManager" ref="org.sakaiproject.memory.api.MemoryService.cacheManager" />
<property name="cacheName" value="org.sakaiproject.db.BaseDbFlatStorage.SAKAI_USER_PROPERTY" />
</bean>
<!-- enables JMX on hibernate -->
<bean id="org.sakaiproject.springframework.orm.hibernate.SessionFactoryBase.jmxAgent"
class="org.sakaiproject.springframework.orm.hibernate.HibernateJMXAgent"
init-method="init" >
<property name="sessionFactory">
<ref bean="org.sakaiproject.springframework.orm.hibernate.GlobalSessionFactory" />
</property>
</bean>
</beans>
Sakai.properties:
## MYSQL Sakai BaseDateSource settings for C3P0
vendor at org.sakaiproject.db.api.SqlService=mysql
driverClass at javax.sql.BaseDataSource=com.mysql.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MySQLDialect
jdbcUrl at javax.sql.BaseDataSource=XXXX
preferredTestQuery at javax.sql.BaseDataSource=select 1 from DUAL
initialPoolSize at javax.sql.BaseDataSource=24
minPoolSize at javax.sql.BaseDataSource=24
maxPoolSize at javax.sql.BaseDataSource=80<mailto:maxPoolSize at javax.sql.BaseDataSource=80>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20100709/ac8a3969/attachment.html
More information about the sakai-dev
mailing list