Studio thinks database schema does not conform even if it's not true (SQL Server)

Support
#1

Hi
I have the following scenario (using Studio 6.10.0):

  • An existing database created in MSSQL, but without any CUBA related objects
  • Studio is configured to talk to this existing DB
  • Init scripts already generated, and successfully applied, using updateDb task (I already checked the SYS_DB_CHANGELOG table and it contains the init scripts for the project)
  • All the tables and constraints have been created successfully on the target DB

MSSQL version is 2017-GA for linux, running in a Docker container.

After the above steps, if I try to run the project from within Studio, I receive the “database schema does not conform error”, asking me to generate update scripts.
If I try generating update scripts, Studio generates ALTER statements to add constraints, that are already in place (they have been created by the init script). In fact, running this script lead to errors like:

Task 'updateDb' failed
com.microsoft.sqlserver.jdbc.SQLServerException: There is already an object named 'FK_WHATEVER' in the database.

So there’s definitely something wrong with the change detection algorithm in Studio, at least in my scenario.

Paolo

1 Like
#2

Hi.
Could you please provide more information about how Studio is configured to talk to this existing DB? Did you use this DB as a main or as an additional datastore? Which version of the platform do you use?

1 Like
#3

it’s a plain MSSql server connection, using sa user and password (so full server access), talking to standard 1433 port on localhost

Main data store, no additional stores are used in this project.

Studio 6.10.0 with CUBA 6.10.1, fresh project with just entities and an empty web module.

I want to add that I use a mix of DB views and generated entities in the project, with relations between them. Every entity has Integer Identity PKs, both the DB views and the normal entities.
This same approach was used for a former project, with studio/cuba 6.9, and there were no issues whatsoever.

Thanks,
P.

#4

Hi @firstova, I found this in the Studio logs:

2018-10-21 17:56:22,744 ERROR [com.haulmont.studio.backend.ed.ddl.AbstractDdlGenerator] Exception when try get imported keys
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

I suspect this could be the source of the problem… Have you ever seen this before when dealing with MSSQL databases? The DB I’m working on is not mine, but there should be some collation conflict.

Thx
P.

#5

UPDATE
I confirm that the problem was a conflict between the server collation, and the DB collation.

Issuing the following SELECT on the server:

SELECT convert(varchar, SERVERPROPERTY('COLLATION')), convert(varchar, DATABASEPROPERTYEX(DB_NAME(), 'COLLATION'));

gave me SQL_Latin1_General_CP1_CI_AS for the server collation, while Latin1_General_CI_AS for the target DB.
I’m working with a docker container running MSSQL for linux (2017), so I changed the run parameters to specify the Latin1_General_CI_AS collation for the entire server, and after that the original issue in Studio disappeared.

But this is not something that can be done when targeting an existing database server, that contains several other databases.
So this is something that should be addressed in Studio, if possible.
Maybe the COLLATE DATABASE_DEFAULT clause could be a solution in your case, take a look at this article

Regards
Paolo

#6

Hi.
This is a problem with the JDBC driver version (see this topic). Update the driver version to the newest one for java8 as it described in the topic.