spring boot mysql application issues using docker compose

Question

OS details

Mint version 19, 
Code name : Tara,
PackageBase : Ubuntu Bionic
Cinnamon (64-bit)

I followed this URL to install mysql 5.7 and workbench 6.3

I can check mysql service running

xxxxxxxxx:~$ sudo netstat -nlpt | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1547/mysqld  

I also checked bind-address inside file: mysqld.cnf under directory : /etc/mysql/mysql.conf.d/

bind-address = 127.0.0.1

I have written a simple spring application using mysql Here are all classes written

SpringBootDataJpaExampleApplication

@EnableJpaRepositories(basePackages = "com.springbootdev.examples.repository")
@SpringBootApplication
public class SpringBootDataJpaExampleApplication
{

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

UserController

@RestController
@RequestMapping("/api")
public class UserController {

    @Autowired
    private UserRepository userRepository;


    @GetMapping("/create")
    public List<User> users() {
        User users = new User();
        users.setId(new Long(1));
        users.setName("Sam");
        users.setCountry("Development");

        userRepository.save(users);

        return userRepository.findAll();
    }


    @GetMapping("/users")
    public List<User> findAll()
    {
        return userRepository.findAll();
    }
}

UserRepository

public interface UserRepository extends JpaRepository<User, Long>
{

}

User

@Entity
@Table(name = "user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;

    private String country;
    //getters and setters
}

partial pom.xml file. Please see I am using spring boot 1.5.9 and jdk 1.8

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.9.RELEASE</version>
        <relativePath/>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

application.properties

spring.datasource.url = jdbc:mysql://mysql-standalone:3306/test
spring.datasource.username = testuser
spring.datasource.password = testpassword
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

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

spring.jpa.hibernate.ddl-auto = create

Dockerfile

FROM openjdk:8
VOLUME /tmp
EXPOSE 8080
ADD target/spring-boot-app.jar spring-boot-app.jar
ENTRYPOINT ["java","-jar","spring-boot-app.jar"]

When I run this application locally using IDE it works fine and I can access: http://localhost:8080/api/users/ and http://localhost:8080/api/create/

I build image out of my application using command

docker build . -t spring-boot-app

I can see image is being built.

xxxxxxxxxx:$ docker images
REPOSITORY                        TAG                 IMAGE ID            CREATED             SIZE
spring-boot-app                   latest              069e53a7c389        27 minutes ago      652MB
mysql                             5.7                 1b30b36ae96a        8 days ago          372MB

Now I run Command to run the mysql container

docker run --name mysql-standalone -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=test -e MYSQL_USER=testuser -e MYSQL_PASSWORD=testpassword -d mysql:5.7

Then I connect to mysql-standalone container from my application(spring-boot-app) (referencing this)

docker run --name spring-boot-app-container --link mysql-standalone:mysql -d spring-boot-app

With this, I can see my application working fine from docker using http://(docker-container-ip):8080/api/users/ and http://(docker-container-ip):8080/api/create/

Here I wanted to make the same thing work with docker-compose. Referenced this to install docker compose.

xxxxxxxxxx:~$ docker-compose --version
docker-compose version 1.22.0, build f46880fe

Then I created file docker-compose.yml in my project directory.

version: '3'

services:
  mysql-docker-container:
    image: mysql:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=adminpassword
      - MYSQL_DATABASE=test
      - MYSQL_USER=testuser
      - MYSQL_PASSWORD=testpassword
    volumes:
      - /data/mysql
  spring-boot-app-container:
    image: spring-boot-app
    build:
      context: .
      dockerfile: Dockerfile
    depends_on:
      - mysql-docker-container
    ports:
      - 8087:8080
    volumes:
      - /data/spring-boot-app

Changed one line in my application.properties and commented earlier datasource url as below.

spring.datasource.url = jdbc:mysql://mysql-docker-container:3306/test
#spring.datasource.url = jdbc:mysql://mysql-standalone:3306/test

Did clean install so new target jar gets created. I also deleted images and containers from docker.

Then ran below command:

docker-compose up

This is what I see in logs

Creating spring-boot-data-jpa-mysql-docker-no-composer-master_mysql-docker-container_1 ... done
Creating spring-boot-data-jpa-mysql-docker-no-composer-master_spring-boot-app-container_1 ... done
Attaching to spring-boot-data-jpa-mysql-docker-no-composer-master_mysql-docker-container_1, spring-boot-data-jpa-mysql-docker-no-composer-master_spring-boot-app-container_1
spring-boot-app-container_1  | 
spring-boot-app-container_1  |   .   ____          _            __ _ _
spring-boot-app-container_1  |  /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
spring-boot-app-container_1  | ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
spring-boot-app-container_1  |  \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
spring-boot-app-container_1  |   '  |____| .__|_| |_|_| |_\__, | / / / /
spring-boot-app-container_1  |  =========|_|==============|___/=/_/_/_/
spring-boot-app-container_1  |  :: Spring Boot ::        (v1.5.9.RELEASE)
spring-boot-app-container_1  | 
spring-boot-app-container_1  | 2018-10-26 01:24:48.748  INFO 1 --- [           main] .s.e.SpringBootDataJpaExampleApplication : Starting SpringBootDataJpaExampleApplication v0.0.1-SNAPSHOT on 582c035536e4 with PID 1 (/spring-boot-app.jar started by root in /)
spring-boot-app-container_1  | 2018-10-26 01:24:48.752  INFO 1 --- [           main] .s.e.SpringBootDataJpaExampleApplication : No active profile set, falling back to default profiles: default
spring-boot-app-container_1  | 2018-10-26 01:24:48.875  INFO 1 --- [           main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@28c97a5: startup date [Fri Oct 26 01:24:48 UTC 2018]; root of context hierarchy
spring-boot-app-container_1  | 2018-10-26 01:24:51.022  INFO 1 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
spring-boot-app-container_1  | 2018-10-26 01:24:51.063  INFO 1 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
spring-boot-app-container_1  | 2018-10-26 01:24:51.065  INFO 1 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.23
spring-boot-app-container_1  | 2018-10-26 01:24:51.218  INFO 1 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
spring-boot-app-container_1  | 2018-10-26 01:24:51.218  INFO 1 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 2399 ms
spring-boot-app-container_1  | 2018-10-26 01:24:51.335  INFO 1 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean  : Mapping servlet: 'dispatcherServlet' to [/]
spring-boot-app-container_1  | 2018-10-26 01:24:51.340  INFO 1 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
spring-boot-app-container_1  | 2018-10-26 01:24:51.341  INFO 1 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
spring-boot-app-container_1  | 2018-10-26 01:24:51.341  INFO 1 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'httpPutFormContentFilter' to: [/*]
spring-boot-app-container_1  | 2018-10-26 01:24:51.341  INFO 1 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
spring-boot-app-container_1  | 2018-10-26 01:24:51.895 ERROR 1 --- [           main] o.a.tomcat.jdbc.pool.ConnectionPool      : Unable to create initial connections of pool.
spring-boot-app-container_1  | 
spring-boot-app-container_1  | com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
spring-boot-app-container_1  | 
spring-boot-app-container_1  | The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
spring-boot-app-container_1  |  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_181]

How to fix this error: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

I can see lot of answers on this forum for this similar error message. Tried bunch of those options/answers, but that didn't work. No answer talks about this combination (linux + spring boot + mysql + docker compose)

Note: This has worked fine without using docker-compose. Have already mentioned the same in above description. Am I making any mistake in docker-compose file or application properties file?

Answer 1

I did see lot of people posting about adding hikari dependency in pom.xml if you are using any spring-boot version < 2.0

  <!-- Spring Boot Data 2.0 includes HikariCP by default -->
  <!-- <dependency>
         <groupId>com.zaxxer</groupId>
         <artifactId>HikariCP</artifactId>
         <version>3.1.0</version>
  </dependency> -->

With that I thought of using same application but made changes to my pom.xml as below

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

Then I followed exact same things as mentioned in description of issue. With that I saw bit more clear error as below:

spring-boot-app-container_1  | 2018-10-27 18:51:47.259  INFO 1 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
spring-boot-app-container_1  | 2018-10-27 18:51:48.464 ERROR 1 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Exception during pool initialization.
spring-boot-app-container_1  | 
spring-boot-app-container_1  | com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
spring-boot-app-container_1  | 
spring-boot-app-container_1  | The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

That confirms spring-boot 2.0 uses hikari datasource default.

Now coming back to how I solved the issue. I changed connection string in application.properties like below:

spring.datasource.url = jdbc:mysql://mysql-docker-container:3306/test?autoReconnect=true&failOverReadOnly=false&maxReconnects=10&useSSL=false

instead of earlier used:

spring.datasource.url = jdbc:mysql://mysql-docker-container:3306/test

The answer was simple. Below change worked for me in Spring-boot 2.0 as well as Spring-boot 1.5.9: (Add this to your connection string)

?autoReconnect=true&failOverReadOnly=false&maxReconnects=10

---

Some handy commands:

Once containers are up, you can check ip addresses of containers using command:

docker inspect -f '{{.Name}} - {{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $(docker ps -aq)

========================================

UPDATE: some additional handy information...

Below is DEPRECATED

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

This needs to be replaced by

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

To fix HikariCP Pool initialization issue/exception, please set HikariCP’s initializationFailTimeout property to 0 (zero), or a negative number.

# HikaryCP Properties
spring.datasource.hikari.initialization-fail-timeout=0

This property controls whether the pool will "fail fast" if the pool cannot be seeded with an initial connection successfully. Any positive number is taken to be the number of milliseconds to attempt to acquire an initial connection; the application thread will be blocked during this period. If a connection cannot be acquired before this timeout occurs, an exception will be thrown. This timeout is applied after the connectionTimeout period. If the value is zero (0), HikariCP will attempt to obtain and validate a connection. If a connection is obtained, but fails validation, an exception will be thrown and the pool not started. However, if a connection cannot be obtained, the pool will start, but later efforts to obtain a connection may fail. A value less than zero will bypass any initial connection attempt, and the pool will start immediately while trying to obtain connections in the background. Consequently, later efforts to obtain a connection may fail. Default: 1