How can I fix the error: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)? [duplicate]

Question

I'm using Maven project with JSP + Servlets.

In Main class I'm connecting via:

Connection connection = ConnectionFactory.getInstance().getConnection();

And logic for ConnectionFactory class is:

    public class ConnectionFactory {
    
        static final Logger logger = Logger.getLogger(ConnectionFactory.class);
    
        private static final String PATH_TO_PROPERTIES = RootPathUtil.getRootApplicationClassPath("database.properties");
        private static final Properties properties;
        
        static {
            InputStream inputStream;
            Properties prop = new Properties();
            try {
                inputStream = new FileInputStream(PATH_TO_PROPERTIES);
                prop.load(inputStream);
                logger.info("Connection Factory Property loaded.\n\t\tProperty file location: " + PATH_TO_PROPERTIES);
            } catch (FileNotFoundException e) {
                logger.error("Property file \"database.properties\" didn't found. Database main properties didn't initialize.");
                e.printStackTrace();
            } catch (IOException e) {
                logger.error("IO exception during property file \"database.properties\" loading. Database main properties didn't initialize.");
                e.printStackTrace();
            }
            properties = prop;
        }
    
        private static ConnectionFactory instance = new ConnectionFactory();
        private BasicDataSource ds;
    
        private ConnectionFactory() {
            if (properties.isEmpty()){
                logger.error("Property instance is empty. Database main properties didn't initialize. ConnectionFactory creation failed.");
                throw new PropertyLoadException("ConnectionFactory creation failed. Property didn't load.");
            }
            ds = new BasicDataSource();
            ds.setDriverClassName(properties.getProperty("dbDriver"));
            ds.setUsername(properties.getProperty("userName"));
            ds.setPassword(properties.getProperty("password"));
            ds.setUrl(properties.getProperty("connectionUrl"));
    
            ds.setMinIdle(Integer.parseInt(properties.getProperty("connection_pull.min_idle")));
            ds.setMaxIdle(Integer.parseInt(properties.getProperty("connection_pull.max_idle")));
            ds.setMaxOpenPreparedStatements(Integer.parseInt(properties.getProperty("connection_pull.max_open_prepared_statements")));
        }
    
        public static ConnectionFactory getInstance() {
            if (instance == null) {
                instance = new ConnectionFactory();
                return instance;
            } else {
                return instance;
            }
        }
    
        public Connection getConnection() throws SQLException {
            return this.ds.getConnection();
        }
    }

When I'm running Tomcat Server, I'm always getting the issue like:

java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2294)
    at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2039)
    at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
    at com.test.project.carrental.data.service.ConnectionFactory.getConnection(ConnectionFactory.java:69)
    at com.test.project.carrental.data.service.DataBaseUtil.getConnection(DataBaseUtil.java:21)
    at com.test.project.carrental.controller.filter.JDBCFilter.doFilter(JDBCFilter.java:63)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at com.test.project.carrental.controller.filter.LogSessionIdFilter.doFilter(LogSessionIdFilter.java:39)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2330)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
    at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
    at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2304)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2290)
    ... 27 more
Caused by: java.lang.NullPointerException
    at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:2997)
    at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1865)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1228)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    ... 41 more

To solve this problem, I tried different versions for mysql-connector-java dependency like:

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>

Also, as mentioned here and here, I tried to use:

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>

Alternative fix using 5.1.41 or 5.1.46 wasn't helpful for me as well.

At the same time, I tried to use various connection properties for:

dbDriver=com.mysql.jdbc.Driver
connectionUrl=jdbc:mysql://localhost:3306/db?verifyServerCertificate=false&useSSL=true
userName=...
password=...
connection_pull.min_idle=5
connection_pull.max_idle=20
connection_pull.max_open_prepared_statements=180

I've tested different variants for coonectionUrl like:

#connectionUrl=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
#connectionUrl=jdbc:mysql://localhost:3306/db?autoReconnect=true
#connectionUrl=jdbc:mysql://localhost:3306/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
#connectionUrl=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
#connectionUrl=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
#connectionUrl=jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&serverTimezone=UTC&useSSL=false

some connections were based on this & this & this answer.

UPD:

I've added my POM file, because, as mentioned in comments below, MySQL Connector/J 8.0.11 contains both variants of connection strings.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>CarRental</groupId>
    <artifactId>CarRental</artifactId>
    <version>1.0-SNAPSHOT</version>

    <name>Car Rental Service</name>
    <packaging>war</packaging>

    <properties>
        <java.version>1.8</java.version>
        <junit.version>4.12</junit.version>
        <mokito.version>1.10.19</mokito.version>
        <slf4j.version>1.7.21</slf4j.version>
    </properties>

    <dependencies>
        <!-- SERVLETS AND VIEWS-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.2</version>
        </dependency>

        <!-- DATDBASE DRIVER -->
        <!--<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.1.1</version>
        </dependency>

        <!-- TESTING -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.mockito</groupId>
            <artifactId>mockito-all</artifactId>
            <version>${mokito.version}</version>
            <scope>test</scope>
        </dependency>

        <!-- LOGGING -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>${slf4j.version}</version>
        </dependency>

    </dependencies>

    <build>
        <finalName>CarRentalService</finalName>

        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <encoding>UTF-8</encoding>
                    <source>${java.version}</source>
                    <target>${java.version}</target>
                </configuration>
            </plugin>

            <plugin>
                <artifactId>maven-resources-plugin</artifactId>
                <version>3.0.1</version>
                <configuration>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>

            <plugin>
                <artifactId>maven-war-plugin</artifactId>
                <version>3.0.0</version>
                <configuration>
                    <failOnMissingWebXml>false</failOnMissingWebXml>
                </configuration>
            </plugin>

            <plugin>
                <groupId>org.jacoco</groupId>
                <artifactId>jacoco-maven-plugin</artifactId>
                <version>0.7.7.201606060606</version>
                <configuration>
                    <destFile>${basedir}/target/coverage-reports/jacoco-unit.exec</destFile>
                    <dataFile>${basedir}/target/coverage-reports/jacoco-unit.exec</dataFile>
                </configuration>
                <executions>
                    <execution>
                        <id>jacoco-initialize</id>
                        <goals>
                            <goal>prepare-agent</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>jacoco-site</id>
                        <phase>package</phase>
                        <goals>
                            <goal>report</goal>
                        </goals>
                    </execution>
                  </executions>
            </plugin>

            <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-surefire-plugin</artifactId>
            <version>2.19.1</version>
            <configuration>
                <systemPropertyVariables>
                    <jacoco-agent.destfile>${basedir}/target/coverage-reports/jacoco-unit.exec</jacoco-agent.destfile>
                </systemPropertyVariables>
            </configuration>
            </plugin>
        </plugins>
    </build>
</project>

I've tried to run without Intelij IDEA war file - the same issue.

Given the stacktrace, you are not using MySQL Connector/J 8.0.11, which would mean that you have multiple versions of MySQL Connector/J on the classpath (maybe an old one in Tomcat's lib folder, and one in your WAR, or maybe two in your WAR). — Mark Rotteveel
– Mark Rotteveel, Commented Jan 24, 2021 at 8:43
I've tried, as you mentioned, to change to older verson MySQL Connector/J 5.1.46 (also 8.0.11 which contains both variants of connections strings) as dependency in POM & write in database.properties dbDriver=com.mysql.jdbc.Driver & recreate war using Edit configuration in Intelij IDEA by choosing Tomcat Server (Local), but it still doesn't work. What I'm doing wrong here? I don't think it's duplicated question, so why do I get downvotes? — invzbl3
– invzbl3, Commented Jan 24, 2021 at 16:12
You just said that 8.0.11 contains two different dbDriver variants, so I've reproduced the same issue using 8.0.11. — invzbl3
– invzbl3, Commented Jan 24, 2021 at 16:16
The stacktrace is not produced by a 8.0.x version of MySQL Connector/J (which uses the com.mysql.cj.jdbc package), nor by 5.1.46 (which wouldn't exhibit this error), which means that you must also have an older version of MySQL Connector/J on your classpath somewhere, and that older version gets loaded in preference of the 8.0.11 version (or the 5.1.46 version). You need to check your classpath carefully for other versions (e.g. in the lib folder of Tomcat itself). It is a duplicate as the error is caused by using a MySQL Connector/J 5.1.45 or earlier version. — Mark Rotteveel
– Mark Rotteveel, Commented Jan 24, 2021 at 16:16
Check the lib folder of your Tomcat installation to see if it contains older versions of the MySQL driver, and unzip your WAR and check the WEB-INF/lib folder to see if it maybe contains multiple versions of the driver. — Mark Rotteveel
– Mark Rotteveel, Commented Jan 24, 2021 at 16:34

invzbl3 · Accepted Answer · 2021-01-25 01:37:49Z

3

I spent several days to solve this problem. I have tested many approaches that have been mentioned in different answers, but none of them worked.

The cause of my specific problem was in dbDriver=com.mysql.jdbc.Driver. To fix it, I've changed to dbDriver=com.mysql.cj.jdbc.Driver.

More information can be read, for example, here.

Notice: I'm using MySQL Server 8.0.20 & OS Windows 10.

UPD: As it turned out, I didn't check target folder where was located WEB-INF/lib with jars.

As soon as I cleaned up unnecessary versions there, the program started with dbDriver=com.mysql.jdbc.Driver as well. Tip for unzip is also helpful as solution.

edited Jan 25, 2021 at 1:37

answered Jan 23, 2021 at 23:04

invzbl3

6,63813 gold badges46 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Rotteveel Over a year ago

Given the 8.0.x driver contains both com.mysql.jdbc.Driver and com.mysql.cj.jdbc.Driver that normally wouldn't make a difference. This means that you have two versions of the driver on the classpath. In your case, when you load com.mysql.jdbc.Driver, it will load the old driver because it is earlier on the classpath (e.g. in the lib folder of Tomcat itself), and when you load com.mysql.cj.jdbc.Driver it will load the newer one (in your WAR).

invzbl3 Over a year ago

Thanks, I've finally solved the issue by checking target folder where was located WEB-INF/lib and deleting unnecessary versions. It makes more sense now.

Collectives™ on Stack Overflow

How can I fix the error: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)? [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related