MySql reactive with SSL uses SSL settings only for first connection in pool

[Frank12B]

Describe the bug

When using mysql reactive extension (mariadb 10.x) with ssl-mode required or preferred (others not tested), the first connection in the pool successfully establishes a connection to the database successfully, but subsequent connection seems not to use given ssl settings in the config.

When a connection from the pool except the first one is used following Exception is thrown:
Access denied for user 'user'@'host' (using password: YES)

I assume that only the first connection is valid because limiting the connection pools max size to 1 does not cause the error above.

Expected behavior

All connections in a MySqlPool are using ssl-mode: required flag to establish an encrypted connection.

Actual behavior

Only the first connection seems to be able to establish an encrypted connection to the database.

How to Reproduce?

1. Create a simple app with quarkus starter and mysql reactive/resteasy reactive extension
2. Create a mariadb 10.x instance
3. Secure the database instance and require ssl for user connections
4. Edit following example settings for your specific needs:
   quarkus.datasource.db-kind=mysql
   quarkus.datasource.username=
   quarkus.datasource.password=
   quarkus.datasource.reactive.url=mysql://myServer:3306/mydb
   quarkus.datasource.reactive.mysql.ssl-mode=required
   quarkus.datasource.reactive.max-size=1

test wit timeout of 5 seconds

quarkus.datasource.reactive.idle-timeout=5

quarkus.datasource.reactive.reconnect-attempts: 1

quarkus.datasource.reactive.reconnect-interval: 1

Output of uname -a or ver

running in container: registry.access.redhat.com/ubi8/openjdk-17:1.16

Output of java -version

No response

GraalVM version (if different from Java)

No response

Quarkus version or git rev

3.0.3+

Build tool (ie. output of mvnw --version or gradlew --version)

gradle kotlin

Additional information

This behavior started from version 3.0.3 until the latest release now.

[quarkus-bot]

/cc @evanchooly (kotlin), @geoand (kotlin)

[cescoffier]

CC @tsegismont

[matthkarr]

We currently have the same problem
For testing, we set the "datasource.reactive.idle-timeout" down to 10 seconds.
After the timeout, the connection is closed as expected.
A new connection is no longer established with the SSL (... using TCP/IP instead of using SSL/TLS).

Testet also with:
quarkusPluginVersion=3.2.4.Final
quarkusPlatformVersion=3.2.4.Final => same behavior.

On quarkusPlatformVersion=3.0.2.Final => no failure (works)

application.properties
quarkus.datasource.db-kind=mariadb
quarkus.datasource.username=testuser
quarkus.datasource.password=XXXXXXXXXXXXXXXXXXX
quarkus.datasource.reactive.url=mariadb://xxxxxxx.xxxxxx.xx:3306/dev_service_db
quarkus.datasource.reactive.mysql.ssl-mode=required
quarkus.datasource.reactive.max-size=1

// test with timeout of 10 seconds
quarkus.datasource.reactive.idle-timeout=10

=== Quarkus - LOG ===
2023-08-21 21:25:40,074 INFO (vert.x-eventloop-thread-1) checkDbConnection() -> Connected!
2023-08-21 21:25:40,074 INFO (vert.x-eventloop-thread-1) isSSL -> true
2023-08-21 21:25:40,075 INFO (vert.x-eventloop-thread-1) productName -> MariaDB
2023-08-21 21:25:40,076 INFO (vert.x-eventloop-thread-1) majorVersion -> 10
2023-08-21 21:25:40,076 INFO (vert.x-eventloop-thread-1) minorVersion -> 8
2023-08-21 21:25:40,076 INFO (vert.x-eventloop-thread-1) fullVersion -> 5.5.5-10.8.7-MariaDB-1:10.8.7+maria~deb11-log

=== Mariadb - LOG ===
230821 21:25:40 6709 Connect [email protected] on dev_service_db using SSL/TLS

=== Mariadb - LOG => 10 seconds after quarkus.datasource.reactive.idle-timeout ===
230821 21:25:50 6709 Quit

=== Quarkus - LOG => 10 seconds after quarkus.datasource.reactive.idle-timeout ===
2023-08-21 21:26:05,602 ERROR (vert.x-eventloop-thread-1) checkDbConnection() -> Failed to connect: io.vertx.mysqlclient.MySQLException: {errorMessage=Access denied for user 'testuser'@'XX.XXX.XXX.XX' (using password: YES), errorCode=1045, sqlState=28000}

=== Mariadb - LOG => 10 seconds after quarkus.datasource.reactive.idle-timeout ===
230821 21:26:05 6710 Connect [email protected] on dev_service_db using TCP/IP
6710 Connect Access denied for user 'testuser'@'XX.XXX.XXX.XX' (using password: YES)

[matthkarr]

Today we updated our db-ssl-connection test application to the new Quarkus 3.3.0 version.
Unfortunately with the same misbehavior.

application.properties

quarkusPluginId=io.quarkus
quarkusPluginVersion=3.3.0
quarkusPlatformGroupId=io.quarkus.platform
quarkusPlatformArtifactId=quarkus-bom
quarkusPlatformVersion=3.3.0

build.gradle.kts

plugins {
    java
    id("io.quarkus")
}

repositories {
    mavenCentral()
    mavenLocal()
}

val quarkusPlatformGroupId: String by project
val quarkusPlatformArtifactId: String by project
val quarkusPlatformVersion: String by project

dependencies {
    implementation(enforcedPlatform("${quarkusPlatformGroupId}:${quarkusPlatformArtifactId}:${quarkusPlatformVersion}"))
    implementation("io.quarkus:quarkus-resteasy-reactive-qute")
    implementation("io.quarkus:quarkus-resteasy-reactive-jackson")
    implementation("io.quarkus:quarkus-reactive-mysql-client")
    implementation("io.quarkus:quarkus-resteasy-reactive")
    implementation("io.quarkus:quarkus-arc")
    testImplementation("io.quarkus:quarkus-junit5")
    testImplementation("io.rest-assured:rest-assured")
}

group = "org.acme"
version = "1.0.0-SNAPSHOT"

Our class "PrtgResource" for PRTG Monitoring

package org.acme;

import io.smallrye.mutiny.Uni;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.mutiny.mysqlclient.MySQLPool;
import jakarta.inject.Inject;
import jakarta.ws.rs.GET;
import jakarta.ws.rs.Path;
import jakarta.ws.rs.Produces;
import jakarta.ws.rs.core.MediaType;
import org.jboss.logging.Logger;
import org.jboss.resteasy.reactive.ResponseStatus;

@Path("/status")
public class PrtgResource {
    @Inject
    public MySQLPool client;

    @Inject
    Logger logger;

    @GET
    @ResponseStatus(200)
    @Produces(MediaType.APPLICATION_JSON)
    public Uni<JsonObject> get() {

        final JsonObject json = new JsonObject();
        final JsonObject prtg = new JsonObject();
        json.put("prtg", prtg);
        prtg.put("text", "Version: Demo 0815");

        final JsonArray result = new JsonArray();
        prtg.put("result", result);

        final JsonObject dbChannel = new JsonObject();
        dbChannel
                .put("channel", "DatabaseConnection")
                .put("value", 2)
                .put("showChart", 1)
                .put("showTable", 1)
                .put("LimitMode", 1)
                .put("LimitMinError", 1)
                .put("LimitErrorMsg", "Currently not available!");

        result.add(dbChannel);

        return checkDbConnection()
                .onItem()
                .transform(x -> {
                    json.put("serviceStates", "Database connection UP!");
                    return json;
                })
                .onFailure()
                .recoverWithItem(err -> {
                    json.put("serviceStates", "Database connection DOWN!");
                    dbChannel.put("value", 1);

                    return json;
                });
    }

    private Uni<Void> checkDbConnection() {
        return client.getConnection()
                .onItem()
                .transformToUni(con -> {
                    logger.info("\n");
                    logger.info("checkDbConnection() -> Connected!");
                    logger.info("isSSL -> " + con.isSSL());
                    logger.info("productName -> " + con.databaseMetadata().productName());
                    logger.info("majorVersion -> " + con.databaseMetadata().majorVersion());
                    logger.info("minorVersion -> " + con.databaseMetadata().minorVersion());
                    logger.info("fullVersion -> " + con.databaseMetadata().fullVersion());
                    return con.close();
                })

                .onFailure()
                .invoke(err -> {
                    logger.error("checkDbConnection() -> Failed to connect: " + err);
                });
    }
}

[tsegismont]

Thanks for the updates @matthkarr and @Frank12B

We'll come back to you asap

[tsegismont]

This will be fixed in Vert.x 4.4.5

eclipse-vertx/vertx-sql-client#1347