Spring Boot, MySQL, Tomcat can't create intital connections of pool in Eclipse

Question

I have created a webapp using Spring Boot and REST APIs in Eclipse. When I try to run it using Tomcat 8, it gives the error "o.a.tomcat.jdbc.pool.ConnectionPool: Unable to create initial connections of pool."

I tried using the solutions provided but nothing seemed to work, it always gives the same error.

This is my application.properties file -

spring.datasource.url = jdbc:mysql://localhost:3306/dbase?autoReconnect=true&useSSL=false
spring.datasource.username = 1234
spring.datasource.password = 1234
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

spring.jpa.hibernate.ddl-auto = update

and this is the pom.xml file -

<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/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.springboot</groupId>
  <artifactId>springbootrestapi</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>springbootrestapiexample Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>       
  </dependencies>
  <build>
    <finalName>springbootrestapiexample</finalName>
  </build>
</project>

This is the stack trace -

16:33:43.182 [main] DEBUG org.springframework.boot.devtools.settings.DevToolsSettings - Included patterns for restart : []
16:33:43.206 [main] DEBUG org.springframework.boot.devtools.settings.DevToolsSettings - Excluded patterns for restart : [/spring-boot-starter/target/classes/, /spring-boot-autoconfigure/target/classes/, /spring-boot-starter-[\w-]+/, /spring-boot/target/classes/, /spring-boot-actuator/target/classes/, /spring-boot-devtools/target/classes/]
16:33:43.207 [main] DEBUG org.springframework.boot.devtools.restart.ChangeableUrls - Matching URLs for reloading : [file:/C:/Users/Amber%20Bhanarkar/eclipse-workspace/springbootrestapi/target/classes/]

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.5.4.RELEASE)

2019-05-29 16:33:43.767  INFO 13396 --- [  restartedMain] c.s.s.StudentApplication                 : Starting StudentApplication on LAPTOP-BV6107FE with PID 13396 (started by Amber Bhanarkar in C:\Users\Amber Bhanarkar\eclipse-workspace\springbootrestapi)
2019-05-29 16:33:43.768  INFO 13396 --- [  restartedMain] c.s.s.StudentApplication                 : No active profile set, falling back to default profiles: default
2019-05-29 16:33:43.892  INFO 13396 --- [  restartedMain] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@754391d8: startup date [Wed May 29 16:33:43 IST 2019]; root of context hierarchy
2019-05-29 16:33:49.463  INFO 13396 --- [  restartedMain] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2019-05-29 16:33:49.552  INFO 13396 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2019-05-29 16:33:49.554  INFO 13396 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.15
2019-05-29 16:33:50.076  INFO 13396 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2019-05-29 16:33:50.076  INFO 13396 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 6188 ms
2019-05-29 16:33:50.354  INFO 13396 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean  : Mapping servlet: 'dispatcherServlet' to [/]
2019-05-29 16:33:50.359  INFO 13396 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
2019-05-29 16:33:50.360  INFO 13396 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2019-05-29 16:33:50.360  INFO 13396 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2019-05-29 16:33:50.360  INFO 13396 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
2019-05-29 16:33:55.529 ERROR 13396 --- [  restartedMain] o.a.tomcat.jdbc.pool.ConnectionPool      : Unable to create initial connections of pool.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

Here is StudentApplication.java -

package com.springboot.springbootrestapi;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing
public class StudentApplication {

    public static void main(String[] args) {
        SpringApplication.run(StudentApplication.class, args);
    }

}

Can you edit your question and add full stack trace of your error? — Tech at The Sparks Foundation, May 29 '19 at 10:59
@TechatTheSparksFoundation how do I check that? I have the MySQL command line client, should that be running in background? — PeakyBlinder, May 29 '19 at 11:22
goto `start -> run` , type `services.msc` press enter . look for a service named `mysqld` and its status should be running. — Tech at The Sparks Foundation, May 29 '19 at 11:25
@TechatTheSparksFoundation instead of mysqld there is MySQL80 and it is in running state and the startup type is automatic. — PeakyBlinder, May 29 '19 at 11:28
For `MySQL8.0` you are suggested to use `org.hibernate.dialect.MySQL8Dialect` as dialect in `application.properties` , try it and see if it resolves your problem — Tech at The Sparks Foundation, May 29 '19 at 11:31
@TechatTheSparksFoundationNo it didn't resolve the error. There are some errors is it - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. — PeakyBlinder, May 29 '19 at 11:35
you have to clean and build your application after changing `application.properties` — Tech at The Sparks Foundation, May 29 '19 at 11:37
Caused by: java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'. / 2019-05-29 17:03:36.162 WARN 16528 --- [ restartedMain] o.s.b.a.orm.jpa.DatabaseLookup : Unable to determine jdbc url from datasource — PeakyBlinder, May 29 '19 at 11:37
can you add your `springapplication.java` to your question. click [here](https://stackoverflow.com/posts/56358588/edit) to edit — Tech at The Sparks Foundation, May 29 '19 at 11:41
also, you are missing `spring.datasource.driver-class-name=com.mysql.jdbc.Driver` in your application.properties — Tech at The Sparks Foundation, May 29 '19 at 11:44
@TechatTheSparksFoundation yes I did not mention it here, but later I added that line in application.properties. I am doing the build including that. — PeakyBlinder, May 29 '19 at 11:46
The credentials of username , password, port (3306) and db (dbase) you are trying to connect are correct right? — Filip123go, May 29 '19 at 11:53
Can you access your db via a db management program (e.x. dbeaver or datagrip) — Filip123go, May 29 '19 at 12:10

Tech at The Sparks Foundation · Accepted Answer · 2019-05-29T13:17:54.150

2

You have to change quite few things

1.Goto your mysql installed directory in C drive and find my.ini file.

Change default_authentication_plugin to this

default_authentication_plugin=mysql_native_password

and then goto your MySQL command line client, After logging in, run this query:

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

then restart the mysql service and run your program.

replace "yourusername" and "yourpassword" with your actual username and password of MySQL.

Goto POM and replace:

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

with latest MySQL8 connector

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>

i hope it helps :)

edited May 29 '19 at 13:17

answered May 29 '19 at 11:52

Tech at The Sparks Foundation

1,123
2
7
15

No it didn't work. Also my mysql connector is "mysql-connector-java-5.1.42.jar:5.1.42". – PeakyBlinder May 29 '19 at 12:37
Update it to (mysql-connector-java-8.0.16) because u r using mysql8 – Tech at The Sparks Foundation May 29 '19 at 12:41
Are you restarting the sql service or not ? – Tech at The Sparks Foundation May 29 '19 at 12:42
Where should I keep the file then (the MySQL connector jar file) in the MySQL bin or tomcat bin? – PeakyBlinder May 29 '19 at 12:45
Yes I am restarting the service everytime – PeakyBlinder May 29 '19 at 12:45
Yes Sir, the solution has worked for me. Thank you for the guidance Sir. – PeakyBlinder May 29 '19 at 13:29

score 0 · Answer 2 · answered May 29 '19 at 12:09

These are the things to check

Verify if the database server up and running, and your connection details are working fine in a standalone test.
Grant access if not present, visit :

Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

Why are you running spring boot within tomcat in the first place and not as a fat jar ? Secondly, the mysql client JAR should be included in tomcat library
Increase mySQL max connection count, if server is running out of available connections

Spring Boot, MySQL, Tomcat can't create intital connections of pool in Eclipse

2 Answers2