Detect duplicate resources being included - e.g. import.sql

mjurc commented 4 years ago

Add functionality to Quarkus to detect duplicate resources being included.

This issue was originally filed as bug and the investigation done by @Sanne revealed that there is a shadowing problem with the script name: there were two import.sql resources in the classpath. ORM itself can't detect duplicate resources; we probably want to look at making some improvements in Quarkus core.

Details from the originally filed bug: The SQL from import.sql results in CommandAcceptanceException exceptions when native image is executed when using MariaDB 10.2/10.3 or MS SQL Server 2019. The very same import.sql will execute properly when the same application is ran in JVM mode.

Expected behavior The SQL from import.sql file gets executed without exceptions when running application working properly in JVM mode.

Actual behavior Running Quarkus application from native image with the following import.sql

INSERT INTO book (id, title, author) VALUES (1, 'Foundation', 'Isaac Asimov');
INSERT INTO book (id, title, author) VALUES (2, '2001: A Space Odyssey', 'Arthur C. Clarke');
INSERT INTO book (id, title, author) VALUES (3, 'Stranger in a Strange Land', 'Robert A. Heinlein');
INSERT INTO book (id, title, author) VALUES (4, 'Ender''s Game', 'Orson Scott Card');
INSERT INTO book (id, title, author) VALUES (5, 'Hyperion', 'Dan Simmons');
INSERT INTO book (id, title, author) VALUES (6, 'Anathem', 'Neal Stephenson');
INSERT INTO book (id, title, author) VALUES (7, 'Perdido Street Station', 'China Miéville');
UPDATE hibernate_sequence SET next_val = 8;

will result in the following exceptions:

2020-08-16 23:28:15,164 WARN  [org.hib.too.sch.int.ExceptionHandlerLoggedImpl] (main) GenerationTarget encountered exception accepting command : Error executing DDL "INSERT INTO book (id, title, author) VALUES (nextval('hibernate_sequence'), 'Foundation', 'Isaac Asimov')" via JDBC Statement: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "INSERT INTO book (id, title, author) VALUES (nextval('hibernate_sequence'), 'Foundation', 'Isaac Asimov')" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:439)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applyImportSources(SchemaCreatorImpl.java:492)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:180)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:156)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
    at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.build(FastBootEntityManagerFactoryBuilder.java:73)
    at io.quarkus.hibernate.orm.runtime.FastBootHibernatePersistenceProvider.createEntityManagerFactory(FastBootHibernatePersistenceProvider.java:54)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:80)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
    at io.quarkus.hibernate.orm.runtime.JPAConfig$LazyPersistenceUnit.get(JPAConfig.java:163)
    at io.quarkus.hibernate.orm.runtime.JPAConfig.startAll(JPAConfig.java:84)
    at io.quarkus.hibernate.orm.runtime.HibernateOrmRecorder.startAllPersistenceUnits(HibernateOrmRecorder.java:95)
    at io.quarkus.deployment.steps.HibernateOrmProcessor$startPersistenceUnits-1681903524.deploy_0(HibernateOrmProcessor$startPersistenceUnits-1681903524.zig:70)
    at io.quarkus.deployment.steps.HibernateOrmProcessor$startPersistenceUnits-1681903524.deploy(HibernateOrmProcessor$startPersistenceUnits-1681903524.zig:36)
    at io.quarkus.runner.ApplicationImpl.doStart(ApplicationImpl.zig:484)
    at io.quarkus.runtime.Application.start(Application.java:90)
    at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:91)
    at io.quarkus.runtime.Quarkus.run(Quarkus.java:61)
    at io.quarkus.runtime.Quarkus.run(Quarkus.java:38)
    at io.quarkus.runtime.Quarkus.run(Quarkus.java:106)
    at io.quarkus.runner.GeneratedMain.main(GeneratedMain.zig:29)
Caused by: java.sql.SQLSyntaxErrorException: (conn=9) FUNCTION mydb.nextval does not exist
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
    at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:363)
    at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:501)
    at io.agroal.pool.wrapper.StatementWrapper.execute(StatementWrapper.java:232)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 24 more
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: FUNCTION mydb.nextval does not exist
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:262)
    at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:357)
    ... 27 more
Caused by: java.sql.SQLException: FUNCTION mydb.nextval does not exist
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1688)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1550)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1513)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:256)
    ... 28 more

When MS SQL is used, the exceptions look like this:

2020-08-17 00:10:39,079 WARN  [org.hib.too.sch.int.ExceptionHandlerLoggedImpl] (main) GenerationTarget encountered exception accepting command : Error executing DDL "INSERT INTO book (id, title, author) VALUES (nextval('hibernate_sequence'), 'Foundation', 'Isaac Asimov')" via JDBC Statement: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "INSERT INTO book (id, title, author) VALUES (nextval('hibernate_sequence'), 'Foundation', 'Isaac Asimov')" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:439)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applyImportSources(SchemaCreatorImpl.java:492)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:180)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
    at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:156)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
    at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.build(FastBootEntityManagerFactoryBuilder.java:73)
    at io.quarkus.hibernate.orm.runtime.FastBootHibernatePersistenceProvider.createEntityManagerFactory(FastBootHibernatePersistenceProvider.java:54)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:80)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
    at io.quarkus.hibernate.orm.runtime.JPAConfig$LazyPersistenceUnit.get(JPAConfig.java:163)
    at io.quarkus.hibernate.orm.runtime.JPAConfig.startAll(JPAConfig.java:84)
    at io.quarkus.hibernate.orm.runtime.HibernateOrmRecorder.startAllPersistenceUnits(HibernateOrmRecorder.java:95)
    at io.quarkus.deployment.steps.HibernateOrmProcessor$startPersistenceUnits-1681903524.deploy_0(HibernateOrmProcessor$startPersistenceUnits-1681903524.zig:74)
    at io.quarkus.deployment.steps.HibernateOrmProcessor$startPersistenceUnits-1681903524.deploy(HibernateOrmProcessor$startPersistenceUnits-1681903524.zig:40)
    at io.quarkus.runner.ApplicationImpl.doStart(ApplicationImpl.zig:608)
    at io.quarkus.runtime.Application.start(Application.java:90)
    at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:92)
    at io.quarkus.runtime.Quarkus.run(Quarkus.java:61)
    at io.quarkus.runtime.Quarkus.run(Quarkus.java:38)
    at io.quarkus.runtime.Quarkus.run(Quarkus.java:106)
    at io.quarkus.runner.GeneratedMain.main(GeneratedMain.zig:29)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 'nextval' is not a recognized built-in function name.
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:744)
    at io.agroal.pool.wrapper.StatementWrapper.execute(StatementWrapper.java:232)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
    ... 24 more

The same import.sql will work in JVM mode.

To Reproduce Steps to reproduce the behavior:

Check out https://github.com/mjurc/quarkus-openshift-test-suite/tree/mariadb-native-repro

Start MariaDB with users/table required for the application,

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 --name mydb -e MYSQL_USER=mydb -e MYSQL_PASSWORD=mydb -e MYSQL_DATABASE=mydb -p 3306:3306 registry.access.redhat.com/rhscl/mariadb-102-rhel7

Build a runner JAR for MariaDB app and launch it. No exceptions are produced and the import.sql is executed properly.

mvn -V -B clean install -DskipTests -pl 'app-metadata/deployment,app-metadata/runtime,common,sql-db/mariadb'
java -jar ./sql-db/mariadb/target/sql-db-mariadb-1.0.0-SNAPSHOT-runner.jar

Build a native image for MariaDB app and launch it. Aforemetioned exceptions are produced and the script is not executed.

mvn -V -B clean install -DskipTests -pl 'app-metadata/deployment,app-metadata/runtime,common,sql-db/mariadb' -Dquarkus.profile=native -Dquarkus.native.container-runtime=docker
./sql-db/mariadb/target/sql-db-mariadb-1.0.0-SNAPSHOT-runner

Start MSSQL 2019 with users/table required for the application.

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 --name mydb -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=My1337p@ssworD" -p 1433:1433 mcr.microsoft.com/mssql/rhel/server
docker exec -it mydb /bin/bash
bash-4.4$ /opt/mssql-tools/bin/sqlcmd -S localhost -Q "create database mydb"

Build a runner JAR for MS SQL app. No exceptions are produced and the import.sql is executed properly.

mvn -V -B clean install -DskipTests -pl 'app-metadata/deployment,app-metadata/runtime,common,sql-db/mssql'
java -jar ./sql-db/mssql/target/sql-db-mssql-1.0.0-SNAPSHOT-runner.jar

Build a native image for MS SQL app and launch it. Aforemetioned exceptions are produced and the script is not executed.

mvn -V -B clean install -DskipTests -pl 'app-metadata/deployment,app-metadata/runtime,common,sql-db/mssql' -Dquarkus.profile=native -Dquarkus.native.container-runtime=docker
./sql-db/mssql/target/sql-db-mssql-1.0.0-SNAPSHOT-runner

Configuration

quarkus.datasource.driver=org.mariadb.jdbc.Driver
quarkus.datasource.url=jdbc:mysql://localhost:3306/mydb
quarkus.datasource.username=mydb
quarkus.datasource.password=mydb

quarkus.hibernate-orm.database.charset=utf-8
quarkus.hibernate-orm.database.generation=drop-and-create
quarkus.hibernate-orm.sql-load-script=import.sql
quarkus.hibernate-orm.dialect=org.hibernate.dialect.MariaDB102Dialect

quarkus.datasource.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
quarkus.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=mydb
quarkus.datasource.username=sa
quarkus.datasource.password=My1337p@ssworD

quarkus.hibernate-orm.database.charset=utf-8
quarkus.hibernate-orm.database.generation=drop-and-create
quarkus.hibernate-orm.sql-load-script=import.sql

Environment (please complete the following information):

Output of uname -a or ver: Linux tigris 5.7.14-200.fc32.x86_64 #1 SMP Fri Aug 7 23:16:37 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
Output of java -version: openjdk version "11.0.8" 2020-07-14
GraalVM version (if different from Java): Quarkus default builder image and quarkus/ubi-quarkus-mandrel: 20.1.0.1.Alpha2-java11
Quarkus version or git rev: 1.6.0.Final
Build tool (ie. output of mvnw --version or gradlew --version): Apache Maven 3.6.1 (d66c9c0b3152b2e69ee9bac180bb8fcc8e6af555; 2019-04-04T21:00:29+02:00)

mjurc commented 4 years ago

cc @Ladicek @rsvoboda @geoand @Sanne

Sanne commented 4 years ago

Many thanks @mjurc , I'll look at this.

Sanne commented 4 years ago

I don't have Openshift installed so I couldn't follow the reproducer instructions literally; I tried to convert the reproducer into a Quarkus integration test, but then it seems to work..

I suspect there's a shadowing problem with the script name: you have two import.sql resources in the classpath. Could you try deleting (or renaming) /sql-db/app/src/main/resources/import.sql ? I suspect it's being loaded instead of sql-db/mysql/src/main/resources/import.sql, and the ordering rules might be different in the normal classloader vs the native one.

rsvoboda commented 4 years ago

I think this reproducer doesn't need OpenShift, Michal mentions just Docker, that's why he references mjurc/quarkus-openshift-test-suite/ and local run like ./sql-db/mariadb/target/sql-db-mariadb-1.0.0-SNAPSHOT-runner

I think small change to the mvn command should be applied: -DskipTests => -DskipTests -DskipITs

@mjurc is on PTO, he will be back next week, @Sanne please try the reproducer with the above change

Sanne commented 4 years ago

thanks, I tried with the above reproducer: if you delete /sql-db/app/src/main/resources/import.sql the problem is solved.

(It's still a bit hard to actually ensure you get a clean build, as it seems I had trouble with the snapshots system: even after deleting the file and doing a mvn clean, at first the second resource copy was still being included).

I wish we could detect duplicate resources being included, but this isn't something ORM can do; we probably want to look at making some improvements in Quarkus core. But this isn't a bug, I'd classify it as a request for enhancements.

Ladicek commented 4 years ago

I no longer recall why I added sql-db/app/src/main/resources/import.sql, but I think it's for the "unit test" in the app module. If I remember correctly, I tried to put it into sql-db/app/src/test/resources, but it didn't work. I guess we could rename it and adjust sql-db/app/src/test/resources/application.properties accordingly.

Ladicek commented 4 years ago

But a request for enhancement this totally is. (Hello Yoda!) I currently can't think of a better way of supporting multiple DBs with a single application than what I built in that test suite.

rsvoboda commented 4 years ago

Used Fixes keyword in PR in quarkus-qe/quarkus-openshift-test-suite which triggered auto-close of this issue after merge.

quarkusio / quarkus

Detect duplicate resources being included - e.g. import.sql #11407