Running SpringBoot and MySQL with Flyway in Docker

Question

Starting the spring boot application with a docker mysql instance ends up in the below exception. I have started the mysql docker instance and was able to view the databases via cli. I am not using docker-compose but docker commands as I am setting it up. I am following this to run springboot/mysql in docker. However, only additional component which is not mentioned in the link is the usage of Flyway.

I started the mysql container in a network

docker run -it --name mysqldb --network=my-net -e MYSQL_ROOT_USER=sa -e 
 MYSQL_ROOT_PASSWORD=1234 -e MYSQL_DATABASE=mydb -d mysql:latest

And then the springboot container in the same network which ends up in the below error

docker run --network=my-net --name manager-app -p 8080:8080 -d manager-app

Below is the container log of manager-app

        --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        SQL State  : 08S01
        Error Code : 0
        Message    : Communications link failure

        The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

                at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:60) ~[flyway-core-8.5.13.jar!/:na]
                at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:75) ~[flyway-core-8.5.13.jar!/:na]
                at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:147) ~[flyway-core-8.5.13.jar!/:na]
                at org.flywaydb.core.Flyway.migrate(Flyway.java:124) ~[flyway-core-8.5.13.jar!/:na]
                at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-2.7.7-SNAPSHOT.jar!/:2.7.7-SNAPSHOT]
                at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.24.jar!/:5.3.24]
                at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.24.jar!/:5.3.24]
                ... 26 common frames omitted
        Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

        The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
                at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:828) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar!/:na]
                at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar!/:na]
                at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar!/:na]
                at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar!/:na]
                at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-4.0.3.jar!/:na]
                at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-4.0.3.jar!/:na]
                at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-4.0.3.jar!/:na]
                at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:48) ~[flyway-core-8.5.13.jar!/:na]
                ... 32 common frames omitted
        Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

        The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
                at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
                at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:na]
                at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
                at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) ~[na:na]
                at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:89) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.NativeSession.connect(NativeSession.java:120) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:948) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:818) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                ... 43 common frames omitted
        Caused by: java.net.ConnectException: Connection refused (Connection refused)
                at java.base/java.net.PlainSocketImpl.socketConnect(Native Method) ~[na:na]
                at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399) ~[na:na]
                at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242) ~[na:na]
                at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224) ~[na:na]
                at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[na:na]
                at java.base/java.net.Socket.connect(Socket.java:609) ~[na:na]
                at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:153) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:63) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
                ... 46 common frames omitted

I tried localhost and container name in the jdbc url but did not help. I am using the below configurations.

pom.xml

      <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.31</version>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-mysql</artifactId>
    </dependency>

application.yml

server:
  port: 8080

spring:
  application:
    name: manager-app
  datasource:
    url: jdbc:mysql://mysqldb:3306/mydb?useSSL=false
    driverClassName: com.mysql.cj.jdbc.Driver
    username: root
    password: rootpass

  profiles:
    active: dev

  jpa:
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL5InnoDBDialect
        ddl-auto: update
  h2:
    console:
      enabled: true
  flyway:
    locations: classpath:db/migration

My target is to get the springboot service with Flyway and MySQL run in docker environment. Is there anything I am missing here? I am new to Flyway. Any help/hint is much appreciated. Thanks!

Answer 1

I was facing the same issue with one of my company's projects. To replicate it, I created a sample application and encountered the same problem. I initially thought there was an issue with how Flyway works in a containerized environment with Spring Boot and MySQL.

TL;DR:

Delete the Docker volume, and if you are using a bind mount, delete the folder to which it is being mounted. The configuration from your previous attempts remains, causing issues when you try again. No matter what you do, you'll still get the same error until you delete the volume and/or the mount folder.

Steps to Resolve:

1. Delete the Docker volume created by Docker Compose using:

   docker volume rm <name_of_the_volume>
   

   or

   Delete all the un-named volumes

   docker volume prune
   

2. Delete the directory (or whatever directory you are using for the bind mount). Eg from my sample app: local-mysql-data

After performing these steps, run:

docker-compose up

This should resolve the issue.

You can find a sample application here: GitHub - SpringBoot-MySQL-Flyway-Docker

credits: https://github.com/docker-library/mysql/issues/51

Answer 2

According to the documentation of the MySQL Docker Container there is no MYSQL_ROOT_USER property. Hence trying to connect with user sa will fail as there is no user named sa but only root.

If you want to create a super-user use the MYSQL_USER and MYSQL_PASSWORD properties.

docker run -it --name mysqldb --network=my-net -e 
 MYSQL_ROOT_PASSWORD=1234 -e MYSQL_USER=sa -e MYSQL_PASSWORD=1234 -e MYSQL_DATABASE=mydb -d mysql:latest

This will change the password of the root user to 1234 and as well create a superuser named sa with the same password. Now the sa user should work (not sure what rights it has).

Or simply use the root user instead of sa in your application configuration.

spring:
  application:
    name: manager-app
  datasource:
    url: jdbc:mysql://mysqldb:3306/mydb?useSSL=false
    driverClassName: com.mysql.cj.jdbc.Driver
    username: root
    password: 1234

Answer 3

add -p 3306:3306 to your docker run mysql command. your command should be like this:

docker run -it --name mysqldb -p 3306:3306 --network=my-net -e MYSQL_ROOT_USER=sa -e 
 MYSQL_ROOT_PASSWORD=1234 -e MYSQL_DATABASE=mydb -d mysql:latest