Can't connect to local MySQL in docker with JDBC

Question

I'm working on a Java app which uses MySQL running in a Docker container. I recently switched from a Windows environment to Mac OS, and I can't connect to the database in the Java code using JDBC. I can connect to the database with MySQL Workbench and also from the command line client.

I use Docker for Mac (i.e. without a default docker machine).

I start my Docker container with this:

CONTAINER_NAME=hypo-mysql
PORT="3306:3306"
VOLUME_FROM="hypo-mysql-data"
CONFIG_DIR="$(pwd)/conf.d"
MYSQL_ROOT_PASSWORD="xxxxx"
MYSQL_USER="hypo"
MYSQL_PASSWORD="doktorBenArmsson"
MYSQL_DATABASE="Hypo"

docker run --name ${CONTAINER_NAME} \
            -p ${PORT} \
            --volumes-from ${VOLUME_FROM} \
            -v ${CONFIG_DIR}:/etc/mysql/conf.d \
            -e MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \
            -e MYSQL_USER=${MYSQL_USER} \
            -e MYSQL_PASSWORD=${MYSQL_PASSWORD} \
            -e MYSQL_DATABASE=${MYSQL_DATABASE} \
            -d --restart=always mysql \

When I start my Java app I started out with this error:

Caused by: java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'.

Since my JDBC driver does not support the new authentication method used by MySQL I altered the user to use the old native method instead:

mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| hypo             | %         | caching_sha2_password |
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

ALTER USER 'hypo'@'%' IDENTIFIED WITH mysql_native_password BY ‘xxxxxxx’;

mysql> select user, host, plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| hypo             | %         | mysql_native_password |
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

Now I get the following error from Java:

Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'

I upgraded my JDBC driver in gradle:

//old driver: compile 'mysql:mysql-connector-java:5.1.38'
compile 'mysql:mysql-connector-java:5.1.46'

Now I get this error from Java:

Caused by: java.io.IOException: Keystore was tampered with, or password was incorrect
    at sun.security.provider.JavaKeyStore.engineLoad(JavaKeyStore.java:780)
    at sun.security.provider.JavaKeyStore$JKS.engineLoad(JavaKeyStore.java:56)
    at sun.security.provider.KeyStoreDelegator.engineLoad(KeyStoreDelegator.java:224)
    at sun.security.provider.JavaKeyStore$DualFormatJKS.engineLoad(JavaKeyStore.java:70)
    at java.security.KeyStore.load(KeyStore.java:1445)
    at com.mysql.jdbc.ExportControlled.getSSLSocketFactoryDefaultOrConfigured(ExportControlled.java:381)
    ... 27 more
Caused by: java.security.UnrecoverableKeyException: Password verification failed
    at sun.security.provider.JavaKeyStore.engineLoad(JavaKeyStore.java:778)

But I can't figure out what this error actually means or what to do to get rid of it. I know that the password is correct since I use the same when connecting through Workbench. However, the message about the keystore puzzles me... I didn't touch the keystore to begin with, but then I tried to regenerate the keystore file, but I still get the same error message from Java.

UPDATE: I re-created my docker container for mysql and explicitly used version 5:

docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
8bf6b9ddd29f        mysql:5             "docker-entrypoint.s…"   15 seconds ago      Up 13 seconds       0.0.0.0:3306->3306/tcp   hypo-mysql
1a0add303fe0        mysql:5             "docker-entrypoint.s…"   4 minutes ago       Created                                      hypo-mysql-data

docker exec -it hypo-mysql bash
mysql --version
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

And after I reverted back to our original jdbc driver version 5.1.38 it seems to be working!

Any help and tips are greatly appreciated!

Possible duplicate of [java.sql.SQLException: Unknown system variable 'query\_cache\_size'](https://stackoverflow.com/questions/49984267/java-sql-sqlexception-unknown-system-variable-query-cache-size) — Shihe Zhang, Jul 04 '18 at 06:17

fly2matrix · Accepted Answer · 2018-05-25T11:08:25.410

0

You are using mysql8 in docker images and it has a different implementation for authentication.

see caching_sha2_password changes in the following links

So for the time being try downgrading mysql version to 5.7 .
And on the side note go through the links to use proper steps with mysql-8. Take a look at following link for jdbc-connector.
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatible-connectors

edited May 25 '18 at 11:08

answered May 25 '18 at 11:02

fly2matrix

2,351
12
13

I think you're right, re-created the docker image with tag 5 and went back to use our old/original JDBC driver and at least I didn't get the keystore error anymore! Thank you. – poa May 25 '18 at 12:26

Can't connect to local MySQL in docker with JDBC

1 Answers1