title: Resource acquisition exceptions (Hibernate, c3p0, MySQL) when moving from Ubuntu 10.04 to 12.04
body: <p>There are various similar questions on here, but none with answers that fix my problem.</p>
  
  <p>I'm running a Java EE app (<a href="http://www.yawlfoundation.org" rel="nofollow">YAWL</a> built from source), which uses Hibernate and c3p0 to connect to a MySQL DB. The app runs on a Tomcat user instance (created via <code>tomcat6-instance-create</code>). <strong>It works perfectly on an Ubuntu 10.04 environment</strong>, but gets <code>com.mchange.v2.resourcepool.CannotAcquireResourceException</code> errors when 'upgrading' to Ubuntu 12.04.</p>
  
  <p>The changes in the environment that seem relevant are:</p>
  
  <ul>
  <li>MySQL 5.1.66 --> 5.5.28</li>
  <li>Tomcat 6.0.24 --> 6.0.35</li>
  <li><code>tomcat6-instance-create</code> now adds a symlink <code>lib</code> folder (so that Eclipse can create instances properly; see <a href="https://bugs.launchpad.net/ubuntu/+source/tomcat6/+bug/297675" rel="nofollow">Debian bug #297675</a>)</li>
  </ul>
  
  <p>Java has not changed: it's OpenJDK 6b24 on both releases.</p>
  
  <p>Because of the last change, I had to play with <code>catalina.properties</code> to have my own shared libraries folder separate from the <code>lib</code> symlink, but I recreated this exactly on 10.04 and it still all worked OK there.</p>
  
  <p>Extracts from INFO level Hibernate log are below. (The INFO level means you can see all the properties being used on the (long!) third line.)</p>
  
  <pre><code>[INFO] 2012-12-19 14:53:15,302 org.hibernate.annotations.common.Version - HCANN000001: Hibernate Commons Annotations {4.0.1.Final}
  [INFO] 2012-12-19 14:53:15,308 org.hibernate.Version - HHH000412: Hibernate Core {4.1.2.Final}
  [INFO] 2012-12-19 14:53:15,311 org.hibernate.cfg.Environment - HHH000205: Loaded properties from resource hibernate.properties: {hibernate.connection.password=****, hibernate.c3p0.acquire_increment=1, hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N', hibernate.cache.region_prefix=hibernate.test, hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory, hibernate.c3p0.idle_test_period=3000, hibernate.cache.use_second_level_cache=true, hibernate.show_sql=false, hibernate.c3p0.max_statements=100, hibernate.jdbc.batch_size=0, hibernate.bytecode.use_reflection_optimizer=false, hibernate.c3p0.timeout=5000, hibernate.generate_statistics=true, hibernate.cache.use_query_cache=true, hibernate.max_fetch_depth=1, hibernate.jdbc.use_streams_for_binary=true, hibernate.c3p0.min_size=2, hibernate.connection.username=root, hibernate.connection.driver_class=com.mysql.jdbc.Driver, hibernate.current_session_context_class=thread, hibernate.c3p0.max_size=20, hibernate.connection.provider_class=org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider, hibernate.cache.use_structured_entries=true, hibernate.dialect=org.hibernate.dialect.MySQLDialect, hibernate.connection.url=jdbc:mysql:///yawl_r1872}
  [INFO] 2012-12-19 14:53:15,311 org.hibernate.cfg.Environment - HHH000407: Using java.io streams to persist binary types
  [INFO] 2012-12-19 14:53:15,311 org.hibernate.cfg.Environment - HHH000021: Bytecode provider name : javassist
  [INFO] 2012-12-19 14:53:15,331 org.hibernate.cfg.Configuration - HHH000221: Reading mappings from resource: org/yawlfoundation/yawl/resourcing/resource/Participant.hbm.xml
  
  [...]
  
  [INFO] 2012-12-19 14:53:15,970 org.hibernate.service.jdbc.connections.internal.ConnectionProviderInitiator - HHH000130: Instantiating explicit connection provider: org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider
  [INFO] 2012-12-19 14:53:15,982 org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider - HHH010002: C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql:///yawl_r1872
  [INFO] 2012-12-19 14:53:15,982 org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider - HHH000046: Connection properties: {user=root, password=****}
  [INFO] 2012-12-19 14:53:15,983 org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider - HHH000006: Autocommit mode: false
  [WARN] 2012-12-19 14:53:45,331 org.hibernate.engine.jdbc.internal.JdbcServicesImpl - HHH000342: Could not obtain connection to query metadata : Connections could not be acquired from the underlying database!
  
  [...]
  
  [INFO] 2012-12-19 14:53:45,788 org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000102: Fetching database metadata
  [INFO] 2012-12-19 14:53:45,788 org.hibernate.service.jdbc.connections.internal.ConnectionProviderInitiator - HHH000130: Instantiating explicit connection provider: org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider
  [INFO] 2012-12-19 14:53:45,789 org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider - HHH010002: C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql:///yawl_r1872
  [INFO] 2012-12-19 14:53:45,789 org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider - HHH000046: Connection properties: {user=root, password=****}
  [INFO] 2012-12-19 14:53:45,789 org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider - HHH000006: Autocommit mode: false
  [ERROR] 2012-12-19 14:54:15,053 org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000319: Could not get database metadata
  java.sql.SQLException: Connections could not be acquired from the underlying database!
      at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
      at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
      at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
      at org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:84)
      at org.hibernate.tool.hbm2ddl.ManagedProviderConnectionHelper.prepare(ManagedProviderConnectionHelper.java:55)
      at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:194)
      at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:178)
      at org.yawlfoundation.yawl.resourcing.datastore.HibernateEngine.initialise(HibernateEngine.java:121)
      at org.yawlfoundation.yawl.resourcing.datastore.HibernateEngine.&lt;init&gt;(HibernateEngine.java:88)
      at org.yawlfoundation.yawl.resourcing.datastore.HibernateEngine.getInstance(HibernateEngine.java:96)
      at org.yawlfoundation.yawl.resourcing.datastore.persistence.Persister.&lt;init&gt;(Persister.java:54)
      at org.yawlfoundation.yawl.resourcing.datastore.persistence.Persister.getInstance(Persister.java:59)
      at org.yawlfoundation.yawl.resourcing.ResourceManager.setPersisting(ResourceManager.java:1794)
      at org.yawlfoundation.yawl.resourcing.rsInterface.ResourceGateway.init(ResourceGateway.java:83)
      at javax.servlet.GenericServlet.init(GenericServlet.java:212)
      at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1206)
      at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:1026)
      at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4421)
      at org.apache.catalina.core.StandardContext.start(StandardContext.java:4734)
      at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:799)
      at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:779)
      at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:601)
      at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:943)
      at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:778)
      at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:504)
      at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1317)
      at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:324)
      at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:142)
      at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1065)
      at org.apache.catalina.core.StandardHost.start(StandardHost.java:840)
      at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1057)
      at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:463)
      at org.apache.catalina.core.StandardService.start(StandardService.java:525)
      at org.apache.catalina.core.StandardServer.start(StandardServer.java:754)
      at org.apache.catalina.startup.Catalina.start(Catalina.java:595)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:616)
      at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
      at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
  Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
      at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
      at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
      at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
      at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
      ... 40 more
  </code></pre>
  
  <p>The Hibernate and c3p0 properties are as below:</p>
  
  <pre><code>hibernate.query.substitutions true 1, false 0, yes 'Y', no 'N'
  hibernate.dialect org.hibernate.dialect.MySQLDialect
  hibernate.connection.driver_class com.mysql.jdbc.Driver
  hibernate.connection.url jdbc:mysql:///yawl_r1872
  hibernate.connection.username root
  hibernate.connection.password yawl
  hibernate.c3p0.max_size 20
  hibernate.c3p0.min_size 2
  hibernate.c3p0.timeout 5000
  hibernate.c3p0.max_statements 100
  hibernate.c3p0.idle_test_period 3000
  hibernate.c3p0.acquire_increment 1
  hibernate.connection.provider_class org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider
  hibernate.show_sql false
  hibernate.current_session_context_class thread
  hibernate.generate_statistics true
  hibernate.jdbc.batch_size 0
  hibernate.jdbc.use_streams_for_binary true
  hibernate.max_fetch_depth 1
  hibernate.cache.region_prefix hibernate.test
  hibernate.cache.use_second_level_cache true
  hibernate.cache.use_query_cache true
  hibernate.cache.use_structured_entries true
  hibernate.cache.region.factory_class org.hibernate.cache.ehcache.EhCacheRegionFactory
  </code></pre>
  
  <p>Since these properties are listed in the INFO comments, I assume that they are definitely being read and used properly. I can connect fine to the database using <code>mysql yawl_r1872 -u root -p yawl</code>, so the credentials are fine (and, remember, it works fine on Ubuntu 10.04).</p>
  
  <p>Things I've tried that haven't worked:</p>
  
  <ul>
  <li>using a non-shared-libs configuration (with libs per webapp)</li>
  <li>using Hibernate dialects <code>MySQL5Dialect</code> and <code>MySQL5InnoDBDialect</code></li>
  <li>using connection URLs with the host and port explicitly given (e.g., <code>//localhost:3306/yawl_r1872</code>) and with DB names without underscores in</li>
  <li>uncommenting <code>max_connections = 100</code> in <code>/etc/mysql/my.cnf</code>. (I think it defaults to 100 anyway.)</li>
  <li>upgrading the MySQL Connector/J library (from 5.1.13 to 5.1.22)</li>
  <li>using a Tomcat 7.0.26 instance (via Ubuntu <code>tomcat7-user</code> package; this doesn't have the symbolic <code>lib</code> link fix in, interestingly)</li>
  <li>using a MySQL v5.1.66 server via <a href="https://launchpad.net/mysql-sandbox" rel="nofollow">MySQL Sandbox</a> (with the normal MySQL 5.5 instance down). I think I set this up right: added <code>bind-address = 127.0.0.1</code> to its <code>.cnf</code> file, checked that it was listening correctly on 127.0.0.1:5166, and used connection URL <code>jdbc:mysql://127.0.0.1:5166/yawl_r1872</code> with username <code>root</code> and password <code>msandbox</code></li>
  <li>checking that I could connect to MySQL forcing use of TCP via <code>mysql yawl_r1872 --protocol=TCP -h 127.0.0.1 -u root -p</code>. (Apparently, UNIX sockets are used by default, so previously I wasn't actually confirming that a JDBC-style TCP connection could be made.)</li>
  <li>checking the JDBC connection from some minimal Java JDBC code (worked fine)</li>
  </ul>
  
  <p>For completeness, below is my <code>catalina.properties</code> setup, using a <code>shared</code> folder inside the instance (<code>$CATALINA.BASE</code>):</p>
  
  <pre><code>package.access=sun.,org.apache.catalina.,org.apache.coyote.,org.apache.tomcat.,org.apache.jasper.,sun.beans. 
  package.definition=sun.,java.,org.apache.catalina.,org.apache.coyote.,org.apache.tomcat.,org.apache.jasper.
  common.loader=${catalina.base}/shared,${catalina.base}/shared/*.jar,${catalina.base}/lib,${catalina.base}/lib/*.jar,${catalina.home}/lib,${catalina.home}/lib/*.jar
  server.loader=
  shared.loader=
  tomcat.util.buf.StringCache.byte.enabled=true
  </code></pre>
  
  <p>Any help much appreciated!</p>