search

obiba / opal

OBiBa’s core database application for biobanks or epidemiological studies.
http://www.obiba.org/pages/products/opal/
GNU General Public License v3.0
29 stars 22 forks source link

[4.6.4] Many "Duplicate key" errors on start up and when importing data to an project #3812

Closed tuxmaster5000 closed 1 year ago

tuxmaster5000 commented 1 year ago

This issue is unique

Version information

4.6.4

Expected behavior

That the scientists can import data to projects.

Actual behavior

He/she will only see an error about an "duplicate key error"

Reproduction steps

  1. log in
  2. go to project -> tables
  3. use import

Operating System (OS)

Linux

Browser

No response

Contact info

No response

tuxmaster5000 commented 1 year ago

In the logs of opal, I see many errors on start up about "Duplicate key". On start up hibernate try's to create existing tables(for example):

2023-10-12 07:06:34,520 [main] INFO org.obiba.opal.core.service.DefaultDatabaseRegistry - Checking if database Data1 is to be upgraded... 2023-10-12 07:06:34,520 [main] INFO org.obiba.opal.core.service.DefaultDatabaseRegistry - Building DataSource Data1 2023-10-12 07:06:34,546 [main] WARN org.obiba.opal.core.service.DefaultDatabaseRegistry - SQL execution error 'select next_val from hibernate_sequence': Incorrect result size: expected 1, actual 64 2023-10-12 07:06:43,419 [Datasource Loader 3] WARN org.mariadb.jdbc.message.server.ErrorPacket - Error: 1050-42S01: Table 'category' already exists 2023-10-12 07:06:43,423 [Datasource Loader 3] WARN org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl - GenerationTarget encountered exception accepting command : Error executing DDL "create table category (id bigint not null, created datetime not null, updated datetime not null, code varchar(255), missing bit not null, name varchar(255) not null, variable_id bigint not null, category_index integer, primary key (id)) engine=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_bin" via JDBC Statement org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table category (id bigint not null, created datetime not null, updated datetime not null, code varchar(255), missing bit not null, name varchar(255) not null, variable_id bigint not null, category_index integer, primary key (id)) engine=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_bin" via JDBC Statement at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:581) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:526) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:293) at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:74) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:220) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:123) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:196) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:85) at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:335) at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:471) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:728) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:746) at org.springframework.orm.hibernate5.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:616) at org.springframework.orm.hibernate5.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:600) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:431) at org.obiba.opal.core.runtime.jdbc.SessionFactoryFactory.getSessionFactory(SessionFactoryFactory.java:62) at org.obiba.opal.core.runtime.jdbc.SessionFactoryFactory$$FastClassBySpringCGLIB$$d174c549.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:792) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:707) at org.obiba.opal.core.runtime.jdbc.SessionFactoryFactory$$EnhancerBySpringCGLIB$$c7d19fb4.getSessionFactory() at org.obiba.opal.core.service.DefaultDatabaseRegistry$SessionFactoryCacheLoader.load(DefaultDatabaseRegistry.java:474) at org.obiba.opal.core.service.DefaultDatabaseRegistry$SessionFactoryCacheLoader.load(DefaultDatabaseRegistry.java:469) at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3542) at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2323) at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2286) at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2201) at com.google.common.cache.LocalCache.get(LocalCache.java:3953) at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3957) at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4875) at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4881) at org.obiba.opal.core.service.DefaultDatabaseRegistry.getSessionFactory(DefaultDatabaseRegistry.java:167) at org.obiba.opal.core.runtime.jdbc.DatabaseSessionFactoryProvider.getSessionFactory(DatabaseSessionFactoryProvider.java:53) at org.obiba.magma.datasource.hibernate.support.HibernateDatasourceFactory.internalCreate(HibernateDatasourceFactory.java:43) at org.obiba.magma.AbstractDatasourceFactory.create(AbstractDatasourceFactory.java:46) at org.obiba.magma.DefaultDatasourceRegistry.addDatasource(DefaultDatasourceRegistry.java:123) at org.obiba.magma.security.SecuredDatasourceRegistry.addDatasource(SecuredDatasourceRegistry.java:49) at org.obiba.magma.MagmaEngine.addDatasource(MagmaEngine.java:146) at org.obiba.opal.core.service.DatasourceLoaderServiceImpl.lambda$reloadDatasource$1(DatasourceLoaderServiceImpl.java:107) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) at org.obiba.opal.core.service.DatasourceLoaderServiceImpl.reloadDatasource(DatasourceLoaderServiceImpl.java:92) at org.obiba.opal.core.service.DatasourceLoaderServiceImpl$DatasourceLoader.load(DatasourceLoaderServiceImpl.java:137) at org.obiba.opal.core.service.DatasourceLoaderServiceImpl$DatasourceLoader.run(DatasourceLoaderServiceImpl.java:126) Caused by: java.sql.SQLSyntaxErrorException: (conn=10) Table 'category' already exists at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:280) at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:368) at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:137) at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:833) at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:772) at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:691) at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:634) at org.mariadb.jdbc.Statement.executeInternal(Statement.java:935) at org.mariadb.jdbc.Statement.execute(Statement.java:1061) at org.mariadb.jdbc.Statement.execute(Statement.java:452) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ... 52 common frames omitted 2023-10-12 07:06:43,427 [Datasource Loader 3] WARN org.mariadb.jdbc.message.server.ErrorPacket - Error: 1050-42S01: Table 'category_attributes' already exists

ymarcon commented 1 year ago

I would not recommend to use the Hibernate type of database: it has strong limitations, poor performance, and as it is difficult to maintain, it will not be supported any more in a near future. Mongodb is the recommended storage backend. If you want to keep on with SQL, prefer the "Tabular SQL" schema.

tuxmaster5000 commented 1 year ago

Hi, we use the "Tabulator Schema" as far as possible. How can we convert the rest? Mongodb will not be an option, because it will not have an incremental backup/restore function on none cloud setup's. Here the data sources: Screenshot 2023-10-17 at 07-53-19 MDC Opal

tuxmaster5000 commented 1 year ago

"Tabular SQL" schema, will also fails:

  1. create an storage database with schema "Tabular SQL"
  2. create an new project using this storage. Error:

2023-10-17 12:36:37,809 [qtp217062446-1812] INFO org.obiba.opal.core.service.ProjectsState - Project foo to state LOADING
2023-10-17 12:36:37,809 [Datasource Loader 2] INFO org.obiba.opal.core.service.DatasourceLoaderServiceImpl - Datasource Loader 2: loading datasource of project foo
2023-10-17 12:36:37,809 [Datasource Loader 2] INFO org.obiba.opal.core.service.DatasourceLoaderServiceImpl - Datasource load start: foo
2023-10-17 12:36:37,812 [Datasource Loader 2] INFO org.obiba.opal.core.service.DefaultDatabaseRegistry - Building DataSource Data2
2023-10-17 12:36:37,816 [qtp217062446-1812] INFO org.obiba.opal.core.service.security.realm.SpatialRealm - Clear cache perms for USER:administrator
2023-10-17 12:36:37,853 [Datasource Loader 2] WARN org.mariadb.jdbc.message.server.ErrorPacket - Error: 1071-42000: Specified key was too long; max key length is 3072 bytes
2023-10-17 12:36:37,854 [Datasource Loader 2] ERROR org.obiba.opal.core.service.DatasourceLoaderServiceImpl - Datasource Loader 2: loading datasource of project foo failed for database: Data2
org.obiba.magma.MagmaRuntimeException: org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException; SQL state [null]; error code [0]; liquibase.exception.DatabaseException: (conn=671) Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE data2.categories (datasource VARCHAR(255) NOT NULL, value_table VARCHAR(255) NOT NULL, variable VAR CHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, missing BIT(1) NOT NULL, CONSTRAINT PK_CATEGORIES PRIMARY KEY (datasource, value_table, variable, name))]; nested exception is java.sql.SQLException: liquibase.exception.DatabaseException: (conn=671) Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE data2.categories (datasource VARCHAR(255) NOT NULL, value_table VARCHAR(255) NOT NULL, variable VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, missing BIT(1) NOT NULL, CONSTRAINT PK_CATEGORIES PRIMARY KEY (datasource, value_table, variable, name))]
at org.obiba.magma.support.Initialisables.initialise(Initialisables.java:50)
at org.obiba.magma.DefaultDatasourceRegistry.addDatasource(DefaultDatasourceRegistry.java:99)
at org.obiba.magma.DefaultDatasourceRegistry.addDatasource(DefaultDatasourceRegistry.java:123)
at org.obiba.magma.security.SecuredDatasourceRegistry.addDatasource(SecuredDatasourceRegistry.java:49)
at org.obiba.magma.MagmaEngine.addDatasource(MagmaEngine.java:146)
at org.obiba.opal.core.service.DatasourceLoaderServiceImpl.lambda$reloadDatasource$1(DatasourceLoaderServiceImpl.java:107)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at org.obiba.opal.core.service.DatasourceLoaderServiceImpl.reloadDatasource(DatasourceLoaderServiceImpl.java:92) at org.obiba.opal.core.service.DatasourceLoaderServiceImpl$DatasourceLoader.load(DatasourceLoaderServiceImpl.java:137) at org.obiba.opal.core.service.DatasourceLoaderServiceImpl$DatasourceLoader.run(DatasourceLoaderServiceImpl.java:126) Caused by: org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException; SQL state [null]; error code [0]; liquibase.exception.DatabaseException: (conn=671) Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE data2.categories (datasource VARCHAR(255) NOT NULL, value_table VARCHAR(255) NOT NULL, variable VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, missing BIT(1) NOT NULL, CONSTRAINT PK_CATEGORIES PRIMARY KEY (datasource, value_table, variable, name))]; nested exception is java.sql.SQLException: liquibase.exception.DatabaseException: (conn=671) Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE data2.categories (datasource VARCHAR(255) NOT NULL, value_table VARCHAR(255) NOT NULL, variable VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, missing BIT(1) NOT NULL, CONSTRAINT PK_CATEGORIES PRIMARY KEY (datasource, value_table, variable, name))] at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1578) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:344) at org.obiba.magma.datasource.jdbc.JdbcDatasource.doWithDatabase(JdbcDatasource.java:508) at org.obiba.magma.datasource.jdbc.JdbcDatasource.createMetadataTablesIfNotPresent(JdbcDatasource.java:552) at org.obiba.magma.datasource.jdbc.JdbcDatasource.onInitialise(JdbcDatasource.java:237) at org.obiba.magma.support.AbstractDatasource.initialise(AbstractDatasource.java:101) at org.obiba.magma.support.AbstractDatasourceWrapper.initialise(AbstractDatasourceWrapper.java:52) at org.obiba.magma.views.ViewAwareDatasource.initialise(ViewAwareDatasource.java:47) at org.obiba.magma.support.Initialisables.initialise(Initialisables.java:46) ... 9 common frames omitted Caused by: java.sql.SQLException: liquibase.exception.DatabaseException: (conn=671) Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE data2.categories (datasource VARCHAR(255) NOT NULL, value_table VARCHAR(255) NOT NULL, variable VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, missing BIT(1) NOT NULL, CONSTRAINT PK_CATEGORIES PRIMARY KEY (datasource, value_table, variable, name))] at org.obiba.magma.datasource.jdbc.JdbcDatasource$3.doInConnection(JdbcDatasource.java:519) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:336) ... 16 common frames omitted Caused by: liquibase.exception.DatabaseException: (conn=671) Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE data2.categories (datasource VARCHAR(255) NOT NULL, value_table VARCHAR(255) NOT NULL, variable VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, missing BIT(1) NOT NULL, CONSTRAINT PK_CATEGORIES PRIMARY KEY (datasource, value_table, variable, name))] at org.obiba.magma.datasource.jdbc.JdbcDatasource$3.doInConnection(JdbcDatasource.java:519) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:336) ... 16 common frames omitted Caused by: liquibase.exception.DatabaseException: (conn=671) Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE data2.categories (datasource VARCHAR(255) NOT NULL, value_table VARCHAR(255) NOT NULL, variable VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, missing BIT(1) NOT NULL, CONSTRAINT PK_CATEGORIES PRIMARY KEY (datasource, value_table, variable, name))] at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:440) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:78) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:161) at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1299) at org.obiba.magma.datasource.jdbc.JdbcDatasource$ChangeDatabaseCallback.doInDatabase(JdbcDatasource.java:685) at org.obiba.magma.datasource.jdbc.JdbcDatasource$3.doInConnection(JdbcDatasource.java:517) ... 17 common frames omitted Caused by: java.sql.SQLSyntaxErrorException: (conn=671) Specified key was too long; max key length is 3072 bytes at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:280) at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:368) at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:137) at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:833) at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:772) at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:691) at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:634) at org.mariadb.jdbc.Statement.executeInternal(Statement.java:935) at org.mariadb.jdbc.Statement.execute(Statement.java:1061) at org.mariadb.jdbc.Statement.execute(Statement.java:452) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193) at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:436) ... 22 common frames omitted 2023-10-17 12:36:37,855 [Datasource Loader 2] INFO org.obiba.opal.core.service.DefaultDatabaseRegistry - Destroying DataSource Data2 2023-10-17 12:36:37,855 [Datasource Loader 2] INFO org.obiba.opal.core.service.ProjectsState - Project foo to state ERRORS

In the UI: Errors: the database could not be loaded because of errors.

ymarcon commented 1 year ago

What is the version of MariaDB? Do you have specific settings for this database?

tuxmaster5000 commented 1 year ago

We use MariaDB 10.11.5, the LTS version. But I can upload the variable list, because GH rejects the file. :(

tuxmaster5000 commented 1 year ago

To day I have tested "Tabular SQL" with PostgreSQL (version 12) which are selectable in the ui. Here the "Tabular SQL" schema will works. Looking deeper in the limit specification of both data base systems the problem is very clear. PostgreSQL has only an column limit of 32 for indexes. MariaDB will have an hard coded byte limit for it, which depend on the InnoDB page size. Using an page size of 16KB which is the default will limit the index to 3072 bytes. Increase the page size are possible, but dangerous because you will lost table types, which are not an option, when other application also use the data base server. So in my eyes only the move to PostgreSQL or reduce the fields in the index can fix it. See: https://www.postgresql.org/docs/current/limits.html https://mariadb.com/kb/en/innodb-limitations/ https://mariadb.com/kb/en/innodb-system-variables/#innodb_page_size PostgreSQL can be selected in the ui, but the documentation don't list it as an data base option.

ymarcon commented 1 year ago

Apparently this Mariadb limitation appeared recently, it used to work smoothly. This will require more testing on our side.

ymarcon commented 1 year ago

I have tested both mysql and mariadb, the tabular SQL works up to the following versions:

It is very annoying that database vendors introduce breaking changes on non major versions. I will work on a fix.

ymarcon commented 1 year ago

Fix tested with mysql 8.1 and mariadb 11 (latests)

tuxmaster5000 commented 1 year ago

@ymarcon thanks. But what will be the feature of postgresql?

ymarcon commented 1 year ago

it is the same api for mysql, mariadb and postgresql

tuxmaster5000 commented 1 year ago

Thanks.