Cannot create PoolableConnectionFactory (Unknown database 'XYZ_DEV')

java mysql tomcat amazon-web-services amazon-ec2

32,432

Solution 1

Found the solution. The reason I was getting the error is due to Linux version of MySQL being case sensitive to the database name. When I moved my entire stack from Windows to Linux I faced this problem.

Windows MySQL database names are not case-sensitive and Linux MySQL database names are case-sensitive.

Solution 2

Below rule is redundant. So remove it.

MYSQL    TCP          3306         127.0.0.1/32

Your MySQL is listening on only localhost:

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

This is not a problem as long as you are connecting to MySQL locally. But any remote connection to MySQl will fails. If this is the case, then change the MySQL binding to 0.0.0.0 in my.cnf.

Before running you code, could you try connecting to the MySQL locally using the MySQL cli as below:

mysql -u <username> -h 127.0.0.1 -p

If this connection works fine, then check whether you are able to connect fine with your jdbc driver. Also, double check your jdbc settings.

32,432

Author by

Pradeep Rajashekar

Updated on March 19, 2020

Comments

Pradeep Rajashekar about 4 years

I have been trying to host a web application on AWS EC2 Ubuntu t1.micro instance.

MySQL version: 5.5.35

Tomcat version: 7.0.52

JDK version: 7.0_51

The context for the webapp is:

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/XYZ" docBase="XYZ"
        debug="5" reloadable="true" crossContext="true">

<Resource name="jdbc/XYZ" auth="Container" type="javax.sql.DataSource" 
    factory="org.apache.commons.dbcp.BasicDataSourceFactory" 
    driverClassName="com.mysql.jdbc.Driver" 
    url="jdbc:mysql://127.0.0.1:3306/XYZ?characterEncoding=UTF-8"
    username="root" maxActive="100" maxIdle="50" removeAbandoned="true"
    useUnicode="true" autoReconnect="true"
    removeAbandonedTimeout="60" logAbandoned="true"
    password="root"/>
</Context>

The error that gets thrown when i start tomcat is as follows

AbandonedObjectPool is used (org.apache.commons.dbcp.AbandonedObjectPool@3f445d30)


LogAbandoned: true
   RemoveAbandoned: true
   RemoveAbandonedTimeout: 60
 WARN [localhost-startStop-1] (JDBCExceptionReporter.java:71) - SQL Error: 0, SQLState: null
ERROR [localhost-startStop-1] (JDBCExceptionReporter.java:72) - Cannot create PoolableConnectionFactory (Unknown database 'XYZ')
 WARN [localhost-startStop-1] (SettingsFactory.java:103) - Could not obtain connection metadata
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown database 'XYZ')
    at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:855)
    at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
    at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
    at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:72)
    at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:1881)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1174)
    at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:805)
    at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:745)
    at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:134)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1198)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1167)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:427)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:285)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:352)
    at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:245)
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:188)
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4973)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5467)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:632)
    at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1073)
    at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1857)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'XYZ'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:910)
    at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3923)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1273)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
    at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
    at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
    at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
    at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
    ... 33 more

My AWS security groups are set as follows

Type     Protocol     PortRange    Source
MYSQL    TCP          3306         127.0.0.1/32
SSH      TCP          22           0.0.0.0/0
HTTP     TCP          22           0.0.0.0/0
HTTPS    TCP          22           0.0.0.0/0
All traffic      TCP          22           0.0.0.0/0

I checked the host that MySQL is running on

$ netstat -tln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN

If I try connecting to MySQL using

mysql -u root -h 127.0.0.1 -D database_name -p

the connection works just fine.

Everything seems to be ok. Still I cant seem to find the reason the error is being thrown. Would really appreciate some expert advice.

Thanks in advance.

Pradeep Rajashekar about 10 years

I have tried connecting to mysql using host ip 127.0.0.1. It works fine. Since I have added ALL TRAFFIC in my AWS security group I know adding MYSQL is redundant but that was just in case. How do I check the connection using jdbc driver? And where do I check jdbc settings. Thanks in advance.
slayedbylucifer about 10 years

I am not a java guy. Check these links which will help you testing JDBC: dev.mysql.com/doc/connector-j/en/…, mkyong.com/jdbc/how-to-connect-to-mysql-with-jdbc-driver-jav‌a, wiki.archlinux.org/index.php/JDBC_and_MySQL