MS SQL Problem

[radlik01]

Dear all,

does some one successfully run ApiMan with MS SQL server as backend RDBMS ? We are able to create schema etc. but when we run the apiman UI we get error from server "Transaction is not active".

It works perfectly with H2 and PostgreSQL.

Thank you for any hints

Jan

[msavy]

Hmm, I don't actually have a Windows machine. Will try to see if I can find some way of verifying this.

[msavy]

Also, which version of MSSQL are you using?

[radlik01]

We use MS SQL 2016 with SQLServer2012Dialect.

The problem is in Metadata parsing process. The first mistake was that the mssql11 ddl's missing metadata table creation
-- Changeset c:/Users/ewittman/git/apiman/apiman/distro/ddl/src/main/liquibase/current/010-apiman-manager-api.db.tables.changelog.xml::1436469846462-24::apiman (generated) CREATE TABLE [metadata] (id bigint NOT NULL, exported_on datetime, imported_on datetime, apiman_version VARCHAR(255), apiman_version_at_import VARCHAR(255), success [bit]);

After fixing this we move a little bit but still ending with the same exception.

The following rows are from MS SQL configuration

2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [users]
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [users] in mixed case for use in DatabaseMetaData
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [username]
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [username] in mixed case for use in DatabaseMetaData
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [email]
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [email] in mixed case for use in DatabaseMetaData
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [full_name]
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [full_name] in mixed case for use in DatabaseMetaData
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [joined_on]
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [joined_on] in mixed case for use in DatabaseMetaData
2021-06-14 16:44:55,768 DEBUG [org.hibernate.internal.SessionFactoryImpl] (default task-1) HHH000031: Closing
2021-06-14 16:44:55,768 TRACE [org.hibernate.engine.query.spi.QueryPlanCache] (default task-1) Cleaning QueryPlan Cache
2021-06-14 16:44:55,768 TRACE [org.hibernate.type.spi.TypeConfiguration$Scope] (default task-1) Handling #sessionFactoryClosed from [org.hibernate.internal.SessionFactoryImpl@42fa9f7d] for TypeConfiguration

And this lines comes from PostgreSQL configuration

2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [users]
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [users] in lower case for use in DatabaseMetaData
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [username]
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [username] in lower case for use in DatabaseMetaData
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [email]
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [email] in lower case for use in DatabaseMetaData
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [full_name]
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [full_name] in lower case for use in DatabaseMetaData
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Normalizing identifier quoting for object name [joined_on]
2021-06-14 15:26:02,725 TRACE [org.hibernate.engine.jdbc.env.internal.NormalizingIdentifierHelperImpl] (default task-1) Rendering unquoted identifier [joined_on] in lower case for use in DatabaseMetaData
2021-06-14 15:26:02,725 TRACE [org.hibernate.service.internal.AbstractServiceRegistryImpl] (default task-1) Initializing service [role=org.hibernate.engine.transaction.jta.platform.spi.JtaPlatform]
2021-06-14 15:26:02,725 DEBUG [org.hibernate.query.spi.NamedQueryRepository] (default task-1) Checking 0 named HQL queries
2021-06-14 15:26:02,725 DEBUG [org.hibernate.query.spi.NamedQueryRepository] (default task-1) Checking 0 named SQL queries
2021-06-14 15:26:02,725 TRACE [org.hibernate.type.spi.TypeConfiguration$Scope] (default task-1) Handling #sessionFactoryCreated from [org.hibernate.internal.SessionFactoryImpl@87f2e15] for TypeConfiguration
2021-06-14 15:26:02,741 DEBUG [org.hibernate.internal.SessionFactoryRegistry] (default task-1) Initializing SessionFactoryRegistry : org.hibernate.internal.SessionFactoryRegistry@1635f168
2021-06-14 15:26:02,741 DEBUG [org.hibernate.internal.SessionFactoryRegistry] (default task-1) Registering SessionFactory: 67f714bc-fb82-4b16-92ca-3e9fd001a68f ()

It looks like something wrong is still with metadata processing.

Thank you

Jan

[msavy]

It occurred to me that I'd seen some news that MSSQL now runs on Linux. I've had a quick look and it seems that there's a testcontainer now, so it might be possible to test that soon. https://www.testcontainers.org/modules/databases/mssqlserver/

We're getting pretty close to a release, so might not have time to make this fix before 2.1, but will try to look at it for 2.2.

Will change this to a bug ticket as I think it needs investigating.

[msavy]

#1311