Issue with loading the database

[Sofia Siampani]

Dear all, 

I am writing on behalf of a partner from the Max Rubner-Institut in Germany. They have had their Opal server for more than a year and we've used it in our DataSHIELD analysis without a problem. Recently, we were unable to connect to it and upon checking the Opal web interface it seems that the database is not being loaded and the dataset is no longer shown. Their opal.log file indicates the following error: 

 ERROR org.obiba.shiro.realm.ObibaRealm - Connection failure with identification server: [I/O error on POST request for "https://localhost:8444/ws/tickets": Connect to localhost:8444 [localhost/127.0.0.1, localhost/0:0:0:0:0:0:0:1] failed: Connection refused (Connection refused); nested exception is org.apache.http.conn.HttpHostConnectException: Connect to localhost:8444 [localhost/127.0.0.1, localhost/0:0:0:0:0:0:0:1] failed: Connection refused (Connection refused)]

Any hints? Thank you in advance for your help!

Best,

Sofia

[Sofia Siampani]

The error message got fixed by using the fix mentioned here:  https://groups.google.com/g/obiba-users/c/tga4dq5Di5o.

But, the issue with the database not being loaded still persists. 

The database is mysql-server (version 8.0.34-0ubuntu0.20.04.1) and the recent log has the following error: 

2023-10-16 11:17:34,414 [Datasource Loader 1] ERROR org.obiba.opal.core.service.DatasourceLoaderServiceImpl - Datasource Loader 1: loading datasource of project Test failed for database: test_db
org.obiba.magma.MagmaRuntimeException: org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException; SQL state [null]; error code [0]; liquibase.exception.DatabaseException: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE `opal_data`.`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`))ENGINE=InnoDB]; nested exception is java.sql.SQLException: liquibase.exception.DatabaseException: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE `opal_data`.`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`))ENGINE=InnoDB]
                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: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE `opal_data`.`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`))ENGINE=InnoDB]; nested exception is java.sql.SQLException: liquibase.exception.DatabaseException: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE `opal_data`.`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`))ENGINE=InnoDB]
                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: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE `opal_data`.`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`))ENGINE=InnoDB]
                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: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE `opal_data`.`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`))ENGINE=InnoDB]
                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: Specified key was too long; max key length is 3072 bytes
                at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
                at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
                at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
                at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
                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

[Ramin H.A.]

Hi,

The error is SQL error Specified key was too long; max key length is 3072 bytes :

Caused by: liquibase.exception.DatabaseException: Specified key was too long; max key length is 3072 bytes [Failed SQL: (1071) CREATE TABLE `opal_data`.`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`))ENGINE=InnoDB]

Maybe you can take a look at the database table and view the data to identify the problem. I suspect the primary key has become too long but it’s just a guess.

Have you considered using MongoDB instead or you need to use MySQL?

Best,

--
You received this message because you are subscribed to the Google Groups "obiba-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to obiba-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/obiba-users/c56c5a49-21a3-4d52-9c5c-e94ce900308fn%40googlegroups.com.

[Sofia Siampani]

Hi Ramin,

Thank you for your response! I will ask the partner from the institute to look into the database. If we can't find a solution, we might consider using MongoDB.

This error seemed to have appeared out of nowhere. Everything had been working smoothly for over a year, and nothing has changed since then. They only recently upgraded to the latest Opal version to see if this would fix the issue.

If anyone else has other suggestions, they are very welcome!

Thank you,

Sofia

[Ramin H.A.]

My only guess is that there is a new entry where some combination of these columns (`datasource`, `value_table`, `variable`, `name`) is too long. 

OBiBa recommends MongoDB as the prefered database for the stack (Opal, Mica, Agate) unless using a MySQL-like database is a must. In the latter case, a "Tabular SQL" schema is preferred.

If you wish to migrate to MongoDB, you can use the Opal Python Client commands to do the migration in batch.

Best,

To view this discussion on the web visit https://groups.google.com/d/msgid/obiba-users/54b6b9a7-aec1-46c6-83f4-560b7bd51756n%40googlegroups.com.

[Ramin H.A.]

Hi Sofia,

I thought this issue may help you see the problem better.

Best,

[Sofia Siampani]

Hi Ramin,

Thank you again for your assistance. I'm already aware of the issue, as it has been raised by the IT department at my institute. Interestingly, the partner institute, on whose behalf I am corresponding, is also facing a similar situation. We are currently in the process of testing the migration to PostgreSQL at our institute. If it proves to be successful, I will suggest the same solution to the partner institute. While we have considered MongoDB as an option, it lacks an incremental backup/restore function for non-cloud setups, as my colleague has pointed out.

Best,

Sofia

[Ramin H.A.]

Maybe a custom solution as this article could help perform an incremental backup using mongodump - not great but could be an option.

mongdump can also be used along with gzip flag, again, not thrilling but depending on the size of your data it can be helpful.

Best,

To view this discussion on the web visit https://groups.google.com/d/msgid/obiba-users/f68af387-92ca-4541-9bf0-09f7608af711n%40googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/obiba-users/f68af387-92ca-4541-9bf0-09f7608af711n%40googlegroups.com.

[Patrick Belton]

I can confirm that this is a new error with current Opal version. I am using Docker Compose with Docker pulled Mysql version 8.1.0. I am getting the same error when trying to create a Mysql housed storage database. I have tried manually creating the "Opal" database [

 CREATE DATABASE opal CHARACTER SET utf8 COLLATE utf8_bin;] and setting the storage database type to Opal SQL and Tabular SQL. Same error. 

I can repeat this error from the SQL command line using this command:

 CREATE TABLE `opal`.`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`))ENGINE=InnoDB;

It's the combination of how the primary key is defined and the varchar lengths.

Server version: 8.1.0 MySQL Community Server - GPL

[Yannick Marcon]

Opal 4.6.5 fixes the Tabular SQL schema failure. I have tested with latest versions of MySQL (8.1) and MariaDB (11).

Regards

Yannick 

To view this discussion on the web visit https://groups.google.com/d/msgid/obiba-users/d9eb5769-cfa9-42b9-a016-41d2ea55ce19n%40googlegroups.com.

[Patrick Belton]

Thanks, Yannick!

[Sofia Siampani]

Hi Yannick and thank you for providing a fix. We are currently trying to migrate our datasets from the OpalSQL database to the TabularSQL one but we get the following error. Do you have any hints?

ConnectionCallback; uncategorized SQLException; SQL state [null]; error code [0]; liquibase.exception.DatabaseException: (conn=404) Data too long for column 'units' at row 1 [Failed SQL: (1406) INSERT INTO `data2`.`variables` (`datasource`, `value_table`, `name`, `value_type`, `ref_entity_type`, `mime_type`, `units`, `is_repeatable`, `occurrence_group`, `index`, `sql_name`) VALUES ('Diet4MicroGut_2', 'Diet4MicroGut', 'PAST_AB_TIME', 'integer', '', '', '1=in the past 0-3 months; 2=in the past 4-6 months; 3=in the past 7-12 months;4=in the past 12+ months', 0, '', '19', 'PAST_AB_TIME')]; nested exception is java.sql.SQLException: liquibase.exception.DatabaseException: (conn=404) Data too long for column 'units' at row 1 [Failed SQL: (1406) INSERT INTO `data2`.`variables` (`datasource`, `value_table`, `name`, `value_type`, `ref_entity_type`, `mime_type`, `units`, `is_repeatable`, `occurrence_group`, `index`, `sql_name`) VALUES ('Diet4MicroGut_2', 'Diet4MicroGut', 'PAST_AB_TIME', 'integer', '', '', '1=in the past 0-3 months; 2=in the past 4-6 months; 3=in the past 7-12 months;4=in the past 12+ months', 0, '', '19', 'PAST_AB_TIME')]

[Yannick Marcon]

Hi,

In the field 'units' you are passing the value '1=in the past 0-3 months; 2=in the past 4-6 months; 3=in the past 7-12 months;4=in the past 12+ months' which is obviously not a unit and is too long to be stored in 50 chars column.

Yannick

To view this discussion on the web visit https://groups.google.com/d/msgid/obiba-users/46ac7c1c-e81f-4b7d-9170-27ba45c1dd1en%40googlegroups.com.