Liquibase setSchemaName

r.walde · April 21, 2023, 12:07pm

Hi,
how can I set the schemaName for Liquibase and also to used MSSQL for the main DB?
I tried it with currentSchema but, that does not work.
When I try to update or init the DB, the scripts will use dbo all the time.
Where to change that to another schemaName?

Regards
Roland

r.walde · April 24, 2023, 11:00am

Hi,

I could set the defaultSchema for the DB user to a specific schema.
That works,
but it does not solve the issue,
that the connectionparam currentSchema is not used when trying to init/update
the main DB.

gorbunkov · May 3, 2023, 12:06pm

Hi,

Have you tried the main.liquibase.liquibase-schema application property?

See:

f.zehnder · May 21, 2025, 12:07pm

Hi Maxim
I have a problem, that at a client the MS SQL Server is ignoring main.liquibase.liquibase-schema.

After checking the documentation of liquibase, I found https://docs.liquibase.com/parameters/home.html

there is
–liquibase-schema-name
–reference-default-schema-name
–default-schema-name
–reference-liquibase-schema-name

I did not find any liquibase.liquibase-schema ?

Before I set now wildy all possible schema expressions; is there anywhere a reference how to do that in Jmix ?

Regards

Felix

f.zehnder · May 23, 2025, 9:47am

Hi @gorbunkov

I have an application which i install actually in a government environment. Therefore, the SQL Server has a default schema which is NOT dbo.

When liquibase is creating new tables, it does not pay attention of main.liquibase.liquibase-schema = dbo

To test this behaviour, i made a new project and set the main.liquibase.liquibase-schema = dbx

2025-05-23T11:05:41.267+02:00  INFO 1924 --- [           main] liquibase.changelog                      : Creating database history table with name: **dbx.**DATABASECHANGELOG
2025-05-23T11:05:41.362+02:00  INFO 1924 --- [           main] liquibase.command                        : Logging exception.
2025-05-23T11:05:41.363+02:00  INFO 1924 --- [           main] liquibase.ui                             : ERROR: Exception Details
2025-05-23T11:05:41.363+02:00  INFO 1924 --- [           main] liquibase.ui                             : ERROR: Exception Primary Class:  SQLServerException
2025-05-23T11:05:41.363+02:00  INFO 1924 --- [           main] liquibase.ui                             : ERROR: Exception Primary Reason:  **The specified schema name "dbx" either does not exist** or you do not have permission to use it.
2025-05-23T11:05:41.363+02:00  INFO 1924 --- [           main] liquibase.ui                             : ERROR: Exception Primary Source:  Microsoft SQL Server 16.00.1135

When creating a new database, why liquibase is not creating this scheme ?

So I created the scheme

CREATE SCHEMA dbx

And I restart the application and the other tables are created; BUT ( except the DATABASECHANGELOG/LOCK) ignoring the schema !!

btw, it is not possible to start the application anymore, as liquibase tries to create the

liquibase.exception.DatabaseException: There is already an object named 'FLOWUI_FILTER_CONFIGURATION' in the database. [Failed SQL: (2714) CREATE TABLE FLOWUI_FILTER_CONFIGURATION (ID uniqueidentifier NOT NULL, COMPONENT_ID varchar(255) NOT NULL, CODE varchar(255) NOT NULL, USERNAME varchar(255), ROOT_CONDITION varchar(MAX), CONSTRAINT PK_FLOWUI_FILTER_CONFIGURATION PRIMARY KEY (ID));]

It seems, liquibase is checking the existence of the table with the scheme dbx, doesn’t find it and tries to create the table with the scheme dbo, which does exist from the wrongly created table from before.

We use liquibase 4.29.2, the actual version is 4.32; Jmix 2.51.2 will contain this version ?

I see
4.30.0 * (#6416) When determining the schema name make sure a value was found (DAT-18673). Thanks, @abrackx

Let me know how I can help further, but this is a serious issue ;/

Regards

Felix

krivopustov · May 26, 2025, 5:04am

Hi Felix,
We’ll check the situation and come back.

BTW, Max Gorbunkov doesn’t work for Jmix anymore.

Regards,
Konstantin

i.gavrilov · May 26, 2025, 11:36am

Hi Felix,

It looks like this case is more about MS SQL itself than liquibase.

Just in case I want to clarify this part:

Am I right the dbo in the first line is typo? And your scenario is just store everything in some custom non-dbo schema (dbx)?

With MS SQL you can’t specify schema within connection string. So it schema is not specified in query - it will use default schema.

It’s important how MS SQL handles the default schema resolution (ALTER USER (Transact-SQL) - SQL Server | Microsoft Learn).
dbo user always has dbo schema as default.

When database is created it has only dbo schema. Liquibase will not create another schema if there is no explicit script for that.

According to your specific case I assume your user ignores any changes in default schema and always uses dbo. So the “business” tables were created in dbo.
But because you use main.liquibase.liquibase-schema = dbx it creates 2 “system” tables in dbx schema you previously created.

For now I don’t see any fully automatical solution. Need additional review on studio side.

Possible WA:

Manually create target database.
Create custom schema.
Create new user within that database. Link it with new/existing login.
Set user the default schema.

After that liquibase starts working with new schema (without additional properties).

Regards,
Ivan

f.zehnder · May 26, 2025, 11:55am

Hi Ivan

There is an existing application which is writing to a MS SQL Server in the old dbo default scheme.

Now we have in parallel a Jmix application which is installed on a server where it has to be logged in on a different user, which is then used as default scheme.

With MS SQL we cannot set a default scheme.

So if liquibase creates a table, it should add the scheme at creation time CREATE TABLE (Transact-SQL) - SQL Server | Microsoft Learn

CREATE TABLE db_different_then_dbo.USER

To show, how inconsistent Jmix / Liquibase is creating tables:

create an empy Jmix project,
change in application.properties main.liquibase.liquibase-schema = db_different_then_dbo
create this scheme at the MS SQL Server
create the database with Jmix.

It will create 2 tables in this db_different_then_dbo scheme ( which is correct ) and all other tables in the scheme dbo ( which is wrong ).

It is NOT about default scheme.

It is about using the main.liquibase.liquibase-schema = db_different_then_dbo, which Jmix / Liquibase does in some cases, in most cases not. It is inconsistent and wrong ( except the tables DATABASECHANGELOG and DATABASECHANGELOGLOCK ).

Regards

Felix

i.gavrilov · May 26, 2025, 2:59pm

It looks like there is an issue in Studio.

Liquibase has 2 parameters:

liquibase-schema-name - defines schema for the system liquibase tables ( DATABASECHANGELOG , DATABASECHANGELOGLOCK). It should not affect “business” tables.
default-schema-name - defines schema what will be used, if no schema is specified in specific script.

Studio handles the first one, but not the second one.

That’s why you have those 2 tables in custom schema, but not the others.

Created an issue https://youtrack.jmix.io/issue/JST-5921/Support-default-schema-name-parameter

Regards,
Ivan

gaslov · May 27, 2025, 8:44am

Hi Felix

The behavior you described with main.liquibase.liquibase-schema property is actually an expected one. This propery sets schema used for Liqubase objects - DATABASECHANGELOG and DATABASECHANGELOGLOCK tables in our case.
To set default schema for all objects, you should use main.liquibase.default-schema application property. But as you mentioned before, MSSQL doesn’t support setting default schema on session level.
So you will get an exception from Liquibase if try to set default schema:

[2025-05-27 11:28:54] SEVERE [liquibase.integration] Unexpected error running Liquibase: Cannot use default schema name dbx on Microsoft SQL Server because the login schema of the current user (dbo) is different and MSSQL does not support setting the default schema per session.
liquibase.exception.DatabaseException: java.lang.RuntimeException: Cannot use default schema name dbx on Microsoft SQL Server because the login schema of the current user (dbo) is different and MSSQL does not support setting the default schema per session.
	at liquibase.integration.commandline.CommandLineUtils.createDatabaseObject(CommandLineUtils.java:140)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1400)
	at liquibase.integration.commandline.Main$1.lambda$run$2(Main.java:398)
	at liquibase.Scope.lambda$child$0(Scope.java:191)
	at liquibase.Scope.child(Scope.java:200)
	at liquibase.Scope.child(Scope.java:190)
	at liquibase.Scope.child(Scope.java:169)
	at liquibase.integration.commandline.Main$1.run(Main.java:397)
	at liquibase.integration.commandline.Main$1.run(Main.java:229)
	at liquibase.Scope.child(Scope.java:200)
	at liquibase.Scope.child(Scope.java:176)
	at liquibase.integration.commandline.Main.run(Main.java:229)
	at liquibase.integration.commandline.Main.main(Main.java:165)
Caused by: java.lang.RuntimeException: Cannot use default schema name dbx on Microsoft SQL Server because the login schema of the current user (dbo) is different and MSSQL does not support setting the default schema per session.
	at liquibase.database.core.MSSQLDatabase.setDefaultSchemaName(MSSQLDatabase.java:154)
	at liquibase.integration.commandline.CommandLineUtils.createDatabaseObject(CommandLineUtils.java:121)
	... 12 more

There is an issue in Jmix Studio with main.liquibase.liquibase-schema property. Studio expect main.liquibase.liquibase-schema-name property, while Liquibase Spring Boot uses main.liquibase.liquibase-schema.
That is the reason why you get liquibase.exception.DatabaseException: There is already an object named...

Regarding this point:
So if liquibase creates a table, it should add the scheme at creation time CREATE TABLE db_different_then_dbo.USER.
Unfortunately Liquibase doesn’t support such appoach. It just adds default schema as a connection/session parameter.

Regards,
Alexander

f.zehnder · May 27, 2025, 9:16am

Hi Alexander

Unfortunately Liquibase doesn’t support such appoach. It just adds default schema as a connection/session parameter.

The documentation says something else !

There IS an attribute schemaName, but liquibase needs to know about it and it seems to me, Jmix does not transfer the schemaName …

Regards

Feliox

i.gavrilov · May 27, 2025, 12:41pm

Hi Felix,

This schemaName is attribute on specific operation within changeset, not the global property.
Value of the property main.liquibase.default-schema is supposed to be used for changeset operations without explicitly specified schemaName attribute. But Liquibase does it the way unsupported by MSSQL.

If you have some project side changeSet with operation with explicitly specified schemaName (like createTable example above) and this schema is ignored - thats different issue.

Changesets for Jmix tables don’t have this schemaName attribute set.

Regards,
Ivan

f.zehnder · May 27, 2025, 3:02pm

Hi Ivan

I don’t understand, why it is possible to set main.liquibase.liquibase-schema, if this attribute is not used for Jmix tables ( only for the two DATABASECHANGELOG and DATABASECHANGELOGLOCK ) ?

What sense does it make, to create a setting which is not used ?

Regards

Felix

i.gavrilov · May 27, 2025, 4:03pm

Because it’s not actually a “fully” Jmix property.
We didn’t create it from scratch.
It’s a progagation to a Spring Liquibase property spring.liquibase.liquibase-schema (Common Application Properties :: Spring Boot). Which is equivalent of “native” liquibase parameter liquibase-schema-name (liquibase-schema-name)

We use main.liquibase prefix instead of spring.liquibase to handle multiple data stores (instead of main there can be another data store name).
It’s mentioned here - Database Schema Migration :: Jmix Documentation.

There is an issue with property naming in Studio, mentioned by Alexander, but this doesn’t affect the logic that Liquibase itself means by this property.

Regards,
Ivan