[Building Sakai] C3P0 configuration for BaseDataSource

[Lydia Li]

Hi Scott,
Not sure if you have figured this out. I spent a lot of time on this 
when I first started working on sakai 2.6. It turned out that I was 
missing a sakai-configuration.xml under my tomcat/sakai. Without this 
file Sakai keeps using the default sakai.properties which still has the 
dbcp config. See one of my email thread last year.

http://collab.sakaiproject.org/pipermail/sakai-dev/2009-May/001093.html

Hope this helps,
Lydia


Fischbein, Scott A. wrote:
>
> 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>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"