search

quarkusio / quarkus

Quarkus: Supersonic Subatomic Java.
https://quarkus.io
Apache License 2.0
14.63k stars 2.88k forks source link

Quarkus devservices oracledb with flyway #47743

Closed kraeftbraeu closed 1 month ago

kraeftbraeu commented 1 month ago

Describe the bug

A quarkus application with plugins quarkus-jdbc-oracle and quarkus-flyway fails when starting a devservices oracle instance and trying to create a schema inside. All it takes to reproduce this bug is to include the dependencies

<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-jdbc-oracle</artifactId>
</dependency>
<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-flyway</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-oracle</artifactId>
</dependency>

and add these lines to application.properties:

quarkus.flyway.migrate-at-start=true
quarkus.flyway.schemas=myschema

Then the quarkus:dev startup fails with Error : 1031, Position : 0, SQL = CREATE USER "myschema" IDENTIFIED BY "FFllyywwaayy00!!", Original SQL = CREATE USER "myschema" IDENTIFIED BY "FFllyywwaayy00!!", Error Message = ORA-01031: Keine ausreichenden Berechtigungen

Expected behavior

Expected is that flyway is able to create a schema in the oracle instance provided by quarkus devservices.

Actual behavior

Tthe quarkus:dev startup fails with

2025-05-07 14:44:13,341 INFO  [io.qua.dat.dep.dev.DevServicesDatasourceProcessor] (build-7) Dev Services for default datasource (oracle) started - container ID is 20ce2dc00bcf
2025-05-07 14:44:13,694 INFO  [io.agr.pool] (Quarkus Main Thread) Datasource '<default>': Pool interceptors: [io.quarkus.jdbc.oracle.runtime.RollbackOnConnectionClosePoolInterceptor_ClientProxy@54c37a62 (priority 0)]
2025-05-07 14:44:13,694 INFO  [io.agr.pool] (Quarkus Main Thread) Datasource '<default>': Pool interceptors: [io.quarkus.jdbc.oracle.runtime.RollbackOnConnectionClosePoolInterceptor_ClientProxy@54c37a62 (priority 0)]
2025-05-07 14:44:13,928 INFO  [org.fly.cor.FlywayExecutor] (Quarkus Main Thread) Database: jdbc:oracle:thin:@localhost:32775/quarkus (Oracle 23.4)
2025-05-07 14:44:13,928 INFO  [org.fly.cor.FlywayExecutor] (Quarkus Main Thread) Database: jdbc:oracle:thin:@localhost:32775/quarkus (Oracle 23.4)
2025-05-07 14:44:14,034 WARN  [org.fly.cor.int.dat.bas.Database] (Quarkus Main Thread) Flyway upgrade recommended: Oracle 23.4 is newer than this version of Flyway and support has not been tested. The latest supported version of Oracle is 21.3.
2025-05-07 14:44:14,034 WARN  [org.fly.cor.int.dat.bas.Database] (Quarkus Main Thread) Flyway upgrade recommended: Oracle 23.4 is newer than this version of Flyway and support has not been tested. The latest supported version of Oracle is 21.3.
2025-05-07 14:44:14,045 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:14,045 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:15,049 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:15,049 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:16,051 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:16,051 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:17,055 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:17,055 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:18,057 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:18,057 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:19,060 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:19,060 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:20,063 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:20,063 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:21,065 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:21,065 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:22,067 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:22,067 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:23,070 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:23,070 INFO  [org.fly.cor.int.dat.bas.Schema] (Quarkus Main Thread) Creating schema "myschema" ...
2025-05-07 14:44:23,079 ERROR [io.qua.run.Application] (Quarkus Main Thread) Failed to start application: java.lang.RuntimeException: Failed to start quarkus
        at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
        at io.quarkus.runtime.Application.start(Application.java:101)
        at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:121)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:77)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:48)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:137)
        at io.quarkus.runner.GeneratedMain.main(Unknown Source)
        at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
        at java.base/java.lang.reflect.Method.invoke(Method.java:580)
        at io.quarkus.runner.bootstrap.StartupActionImpl$1.run(StartupActionImpl.java:116)
        at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: org.flywaydb.core.internal.exception.FlywaySqlException: Unable to create schema "myschema"
----------------------------------
SQL State  : 42000
Error Code : 1031
Message    : ORA-01031: Keine ausreichenden Berechtigungen

Caused by: Error : 1031, Position : 0, SQL = CREATE USER "myschema" IDENTIFIED BY "FFllyywwaayy00!!", Original SQL = CREATE USER "myschema" IDENTIFIED BY "FFllyywwaayy00!!", Error Message = ORA-01031: Keine ausreichenden Berechtigungen

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:720)
        ... 37 more

How to Reproduce?

oracleexample.zip

Output of uname -a or ver

Linux 6.8.0-59-generic #61~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Tue Apr 15 17:03:15 UTC 2 x86_64 x86_64 x86_64 GNU/Linux

Output of java -version

openjdk version "21.0.2" 2024-01-16 LTS OpenJDK Runtime Environment Zulu21.32+17-CA (build 21.0.2+13-LTS) OpenJDK 64-Bit Server VM Zulu21.32+17-CA (build 21.0.2+13-LTS, mixed mode, sharing)

Quarkus version or git rev

3.20.0

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

Apache Maven 3.9.9 (8e8579a9e76f7d015ee5ec7bfcdc97d260186937) Java version: 21.0.2, vendor: Azul Systems, Inc. Default locale: de_DE, platform encoding: UTF-8 OS name: "linux", version: "6.8.0-59-generic", arch: "amd64", family: "unix"

Additional information

No response

quarkus-bot[bot] commented 1 month ago

/cc @cristhiank (flyway), @gastaldi (flyway), @geoand (devservices,flyway), @gsmet (flyway)

gastaldi commented 1 month ago

This seems to be a common problem when using TestContainers. I am investigating a workaround and may propose a PR soon

kraeftbraeu commented 1 month ago

Wow @gastaldi, thanks a lot, that was quick!

gastaldi commented 1 month ago

@kraeftbraeu you're welcome, I created https://github.com/quarkusio/quarkus/pull/47766 documenting the steps to make that work too

kraeftbraeu commented 1 month ago

Thanks again for your quick solution @gastaldi, I just tried it out with quarkus 3.22.3 and although your fix made the schema creation working I then failed to introduce flyway scripts since the quarkus user lacks permissions for this new schema (ORA-02420). But this made me pop the hood with help of quarkus.datasource.devservices.show-logs=true and I realized the solution is a lot easier:

quarkus.flyway.migrate-at-start=true
quarkus.flyway.schemas=myschema
quarkus.datasource.username=myschema

Just use the schema's standard user instead of "quarkus" and flyway can rely on the schema's owner user. Maybe that helps others who run into the same issue.

gastaldi commented 1 month ago

@kraeftbraeu you need to grant the DBA role to the quarkus user in that case, as documented in this PR