cuba-platform / cuba-gradle-plugin

Gradle plugin for building CUBA platform and applications
https://www.cuba-platform.com
Apache License 2.0
15 stars 18 forks source link

CreateDb fails if currentSchema of the main connection is defined #128

Closed IskandarovRost closed 4 years ago

IskandarovRost commented 4 years ago

Environment

Description of the bug or enhancement

1) Database and Scheme with the required names were not created before 2) Setup the createDB task as follows

    task createDb(dependsOn: assembleDbScripts, description: 'Creates local database', type: CubaDbCreation) {
        dbms = 'postgres'
        host = 'localhost'
        dbName = 'schematest123'
        dbUser = 'cuba'
        dbPassword = 'cuba'
        connectionParams = '?currentSchema=cuba_schema'
    }

3) Launch Create Database 4) Launch Create Database once again 5) Manually create "cuba_schema" in the desired database 7) Remove the "cuba_schema" from "postgres" DB 6) Launch Create Database once again

image.png And the following error occurs

> Task :app-core:createDb FAILED
Using database URL: jdbc:postgresql://localhost/schematest123?currentSchema=cuba_schema, user: cuba
Failed to execute: create table SYS_DB_CHANGELOG (
SCRIPT_NAME varchar(300) not null primary key, 
CREATE_TS timestamp default current_timestamp, 
IS_INIT integer default 0) because: ERROR: no schema has been selected to create in
  �������: 14

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':app-core:createDb'.
> java.lang.RuntimeException (no error message)

And after the second attempt:

> Task :app-core:createDb FAILED
[CubaDbCreation] Failed to execute SQL: create schema "cuba_schema";
Error: ERROR: schema "cuba_schema" already exists

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':app-core:createDb'.
> [CubaDbCreation] Failed to create Postgres scheme
IskandarovRost commented 4 years ago

Try to connect MSSQL as main datastore like described in https://doc.cuba-platform.com/manual-7.2/db_schema_connection.html And launch CreateDb AR: CreateDb drops the database. Then if "JohnDoe" was made DB_Creator and DB_owner, the tables are created in DBO schema image

IskandarovRost commented 4 years ago

1) PostgreSQL is used 2) No database was created before 3) Define the new database name and 'currentSchema' in connection parameters 4) Launch createDb

image.png

5) UpdateDb also fails image.png

Please, also note that when the connection params are application-defined "schema" could be set as separate params for Hikari, (but not sure Studio handles it): https://youtrack.cuba-platform.com/issue/STUDIO-7578

IskandarovRost commented 4 years ago

The last problem occurred due to the MixCase schema name. Use double quoting and it works OK: image