1

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.

invzbl3
  • 5,872
  • 9
  • 36
  • 76
  • 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 Jan 24 '21 at 08: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 Jan 24 '21 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 Jan 24 '21 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 Jan 24 '21 at 16:16
  • Or phrased differently, if you can load `com.mysql.cj.jdbc.Driver` and not have the problem, but loading `com.mysql.jdbc.Driver` does exhibit the problem, then `com.mysql.jdbc.Driver` is not loaded from the 8.0.11 driver (even though that class exists in that driver), but from a different version of the driver. Because the 8.0.11 version of `com.mysql.jdbc.Driver` is basically just `public class Driver extends com.mysql.cj.jdbc.Driver {}` (and it generates a warning in a static initializer). – Mark Rotteveel Jan 24 '21 at 16:20
  • I've added my full POM file. Can it be the cause of issue if changing driver versions doesn't affect the result? I suppose it's plugin problem. – invzbl3 Jan 24 '21 at 16:27
  • 1
    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 Jan 24 '21 at 16:34

1 Answers1

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.

invzbl3
  • 5,872
  • 9
  • 36
  • 76
  • 2
    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). – Mark Rotteveel Jan 24 '21 at 08:45
  • 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. – invzbl3 Jan 24 '21 at 20:39