Docker Flyway MySQL 8 : Client does not support authentication protocol requested by server. Consider upgrading MariaDB client

Question

I am running my application in docker container where flyway migration tool gives error while connecting to MySQL DB (8.0.11) : Here is the complete error:

Unable to obtain connection from database (jdbc:mysql://docker-mysql:3306) for user 'deepti': 
Client does not support authentication protocol requested by server. 
Consider upgrading MariaDB client. plugin was = caching_sha2_password

Here is my docker-compose.yml :

version: '3'

services: 
  docker-mysql:
    image: mysql:8.0.11
    environment:
      - MYSQL_ROOT_PASSWORD=...
      - MYSQL_DATABASE=test1
      - MYSQL_USER=...
      - MYSQL_PASSWORD=...

  flyway-service-i:
    image: boxfuse/flyway
    command: -url=jdbc:mysql://docker-mysql:3306 -schemas=test1 -user=... -password=... migrate
    volumes:
     - "../resources/db/migration:/flyway/sql"
    depends_on:
     - docker-mysql 

  spring-boot-jpa-docker-webapp:
    image: deepti/spring-boot-docker
    depends_on:
      - docker-mysql
    ports:
      - 8080:8080
    environment:
      - DATABASE_HOST=docker-mysql
      - DATABASE_USER=...
      - DATABASE_PASSWORD=...
      - DATABASE_NAME=test1 
      - DATABASE_PORT=3306
   

Can anyone please help me on this. Thanks

Answer 1

The default authentication method in MySQL changed to caching_sha2_password in version 8.0.4. It doesn't look like the MariaDB connector supports it.

You can revert the default authentication plugin to the old version by adding the command shown below:

version: '3'

services: 
  docker-mysql:
    image: mysql:8.0.11
    command: --default-authentication-plugin=mysql_native_password
    environment:
...

For any existing users in the db that have already been created with the caching_sha2_password authentication method, you can alter the user to use mysql_native_password:

ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

Or just remove the existing container with docker-compose rm.

I have created a working example in this repository. Here is the successful output:

Flyway Community Edition 5.1.4 by Boxfuse

Database: jdbc:mysql://docker-mysql:3306/test1 (MySQL 8.0)
WARNING: You are connected to a MySQL database using the MariaDB driver. This is known to cause issues. An upgrade to Oracle's MySQL JDBC driver is highly recommended.
Successfully validated 1 migration (execution time 00:00.010s)
Creating Schema History table: `test1`.`flyway_schema_history`
Current version of schema `test1`: << Empty Schema >>
Migrating schema `test1` to version 1.0 - init
Successfully applied 1 migration to schema `test1` (execution time 00:00.290s)

As you can see, although it works, there is a warning about issues using the MariaDB driver with MySQL.

One other option would be to download the official MySQL JDBC driver, add it to a ./drivers directory and mount it in the Flyway container:

  flyway-service-i:
    image: boxfuse/flyway
    command: ...
    volumes:
     - "./sql:/flyway/sql"
     - "./drivers:/flyway/drivers"

This also worked:

Flyway Community Edition 5.1.4 by Boxfuse

Database: jdbc:mysql://docker-mysql:3306/test1 (MySQL 8.0)
Successfully validated 1 migration (execution time 00:00.011s)
Creating Schema History table: `test1`.`flyway_schema_history`
Current version of schema `test1`: << Empty Schema >>
Migrating schema `test1` to version 1.0 - init
Successfully applied 1 migration to schema `test1` (execution time 00:00.229s)

But to get rid of the following warning:

WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

I did need to add verifyServerCertificate=false and useSSL=true to the jdbc url:

jdbc:mysql://docker-mysql:3306/test1?verifyServerCertificate=false&useSSL=true