Titanic workshop data import - identifiers table

Support Beginner Support
1

Hi DataSHIELD users,

when trying to import the sample data set used in the “Titanic newcomers workshop” I still run into a problem regarding the PassengerId:

setwd("~/Downloads/titanic/")
library(opalr)
o <- opal.login(username = "duscha", password = "SECRET", url = "https://datashield.research.dhm.mhn.de")

# your destination project
project <- "TITANIC_NEWCOMERS_WORKSHOP"
if (!opal.project_exists(o, project))
  opal.project_create(o, project, database = "mysql", tags = list("Workshop"))

# load data using readr, see readr documentation if you want to specify column data types
# and save tibble in project (overwrite any existing table)
library(readr)
data <- readr::read_csv("titanic_server_1.csv")

works. “data” then contains the csv-data.

But wrting the R-data into a OPAL table then fails:

opal.table_save(o, data, project, table = "TITANIC_NEWCOMERS_DATASET_1", id.name = "PassengerId", overwrite = TRUE, force = TRUE)
Error in opal.table_save(o, data, project, table = "TITANIC_NEWCOMERS_DATASET_1",  : 
  The identifiers column 'PassengerId' is missing.

The csv “data”-object itself doesn’t contain any “PassengerId” column. I suspect this corresponds to an Identifiers table.

In the DataSHIELD web ui, there is no “Identifiers” table. I only assigned a database storage to it. Clicking on “Add Identifiers Table” there leads to a dialogue window, where I should specify the “Entity type”. It is a bit unclear to me, what to enter there.

The sample script that Alex provided to me contains further down a section that seems to refer to the identifiers table:

# apply dictionary
opal.table_dictionary_update(o, project, table = "titanic_server_1", variables = variables, categories = categories)
opal.table_dictionary_update(o, project, table = "titanic_server_2", variables = variables, categories = categories)

But executing these lines first interactively, fails:

 # apply dictionary
> opal.table_dictionary_update(o, project, table = "titanic_server_1", variables = variables, categories = categories)
Error: [Client error: (404) Not Found] TITANIC_NEWCOMERS_WORKSHOP
> opal.table_dictionary_update(o, project, table = "titanic_server_2", variables = variables, categories = categories)
Error: [Client error: (404) Not Found] TITANIC_NEWCOMERS_WORKSHOP

The “TITANIC_NEWCOMERS_WORKSHOP” exists in the OPAL web UI, but contains no tables - it perpetually shows “Loading: the database is being loaded, tables operations are not accessible yet.”

What am I doing wrong?

Best regards,

Sven

2

Hi,

You do not need to setup an identifiers database in Opal, this is a very specific feature (identifiers mapping). But you must provide which is the identifiers column in the dataset you are importing. In the opal.table_save() you have specified that the Ids are to be found in the PassengerId column which cannot be found in the data. What do you get from colnames(data)?

Regards
Yannick

3

Hi Yannick,

thank you for your response. I then got the wrong impression about the role of the (global) identifiers table. But I guess I should read first the Identifiers section in the documentation.

If it is not needed here, then that is good news for the moment. Still, in a follow-up question I would have to figure out how to create a (patient) identifiers table.

The data R table read in from the csv contains these columns:

colnames(data)
 [1] "...1"     "Survived" "Pclass"   "Name"     "Sex"      "Age"      "SibSp"    "Parch"    "Ticket"  
[10] "Fare"     "Cabin"    "Embarked"

Best regards,

Sven

4

This CSV is broken, with no column name specified for the first one. R then gives it the name ...1.

It is a bit ugly but the following should work:

opal.table_save(o, data, project, table = "TITANIC_NEWCOMERS_DATASET_1", id.name = "...1", overwrite = TRUE, force = TRUE)
5

I was wondering about adding

colnames(data)[1] <- "PassengerId"

after the two readr::read_csv call.

Stuart

6

Hi Yannick,

that gets me a step further, but then I get the error that the “TITANIC_NEWCOMERS_WORKSHOP” does not exist:

> opal.table_save(o, data, project, table = "TITANIC_NEWCOMERS_DATASET_1", id.name = "...1", overwrite = TRUE, force = TRUE)
  Creating table TITANIC_NEWCOMERS_DATASET_1 in TITANIC_NEWCOMERS_WORKSHOP [===>---------]  33% / 1sError: [Client error: (404) Not Found] TITANIC_NEWCOMERS_WORKSHOP

That is the project, as created from the script provided by Alex Westerburg, by the command:

# your destination project
project <- "TITANIC_NEWCOMERS_WORKSHOP"
if (!opal.project_exists(o, project))
  opal.project_create(o, project, database = "mysql", tags = list("Workshop"))

In the web UI I do see that project as “TITANIC_NEWCOMERS_WORKSHOP”, but of course with no tables, as none have been created, yet.

I suspect there might still be something wrong with the underlying server/database setup. Looking into to the logs, the /var/log/opal/rest.log shows

{"@timestamp":"2022-03-15T13:47:23.201+01:00","@version":"1","message":"/project/TITANIC_NEWCOMERS_WORKSHOP","logger_name":"org.obiba.opal.web.security.AuditInterceptor","thread_name":"qtp1522875398-115397","level":"WARN","level_value":30000,"method":"GET","ip":"10.0.8.2","username":"Unknown","status":"401"}
{"@timestamp":"2022-03-15T13:47:23.237+01:00","@version":"1","message":"/project/TITANIC_NEWCOMERS_WORKSHOP","logger_name":"org.obiba.opal.web.security.AuditInterceptor","thread_name":"qtp1522875398-115395","level":"WARN","level_value":30000,"method":"GET","ip":"10.0.8.2","username":"Unknown","status":"401"}
{"@timestamp":"2022-03-15T13:47:23.276+01:00","@version":"1","message":"/system/databases queryParams:{usage=[storage]}","logger_name":"org.obiba.opal.web.security.AuditInterceptor","thread_name":"qtp1522875398-111807","level":"WARN","level_value":30000,"method":"GET","created":"/auth/session/225aa1fc-944a-47f9-82e6-288157165715","ip":"10.0.8.2","username":"Unknown","status":"401"}

when the above project commands are executed in R.

Best regards,

Sven

7

Hi Stuart,

With work-around about addingthe PassengerId column manually, I get the same error:

> opal.table_save(o, data, project, table = "TITANIC_NEWCOMERS_DATASET_1", id.name = "PassengerId", overwrite = TRUE, force = TRUE)
  Creating table TITANIC_NEWCOMERS_DATASET_1 in TITANIC_NEWCOMERS_WORKSHOP [===>---------]  33% / 1sError: [Client error: (404) Not Found] TITANIC_NEWCOMERS_WORKSHOP

Best regards,

Sven

8

Does this “mysql” database exists?

If you do not know the database name, you do not need to specify it when creating a project:

opal.project_create(o, project, database = TRUE)

Drop your project with opal.project_delete() and try again.

9

Sven, I get a similar error message but from later in the script, the call to “opal.table_directory_update”. I am investigating…

Stuart

10

This titanic test dataset should be a SPSS file, it would contain both the data + dictionary and would be simple to read with haven.

11

Hi Yannick,

thank you for your comment.

That might apply for the original data, but Alex provided me with the CSV-data used in the “DataSHIELD newcomers workshop”. It seemed easiest to start with a “working example”.

The next step would be to read in some of our data which is also in CSV-format.

I didn’t get much further in tackling the problem, particially due to lack of time, secondly to not have found any error message which would lead to the root of the underlying problem.

There is still the possibility that my DataSHIELD/Opal-Server installation is missing something, but that should manifest itself in an error message in the /var/log/opal/*-log files.

Stuart mentioning that he got the same error, gives me hope, though, that it might not be due to my installation.

I still ponder to go the “Docker route”; but that would be more as a reference, an alternative way of looking into the problem.

The OPAL web interface, the R/rocker-server etc. seem to be working fine now; this seems to be more of a persmission or file structure problem. That would equally happen in a Docker-based instance.

And tweaking permssions/paswords with a stateless docker compose file is not so much my cup of tea, With my limited practical experience with Docker it is always been that it is fine, if you know what to set in the Docker-(compose) file, but fiddiling around, traying things out still feels easier on a conventional machine.

But we have amongst our scientists people who have more experience with Docker and are more keen on its advantages. So, there might be the possibility to at least try, if the same problem occurs there.

Best wishes,

Sven

12

Though, I am not sure, if this is related to the import of csv data for the “Titanic”-project, I do get this error in /var/log/opal/stdout.log:

2022-04-04 12:10:41,351 ERROR org.obiba.opal.core.service.ProjectsServiceImpl - Loading datasource of project omics failed for database: opal_storage
org.obiba.magma.MagmaRuntimeException: org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; liquibase.exception.DatabaseException: (conn=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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:28)
	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.ProjectsServiceImpl.lambda$registerDatasource$0(ProjectsServiceImpl.java:279)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
	at org.obiba.opal.core.service.ProjectsServiceImpl.registerDatasource(ProjectsServiceImpl.java:263)
	at org.obiba.opal.core.service.ProjectsServiceImpl$DatasourceLoader.load(ProjectsServiceImpl.java:388)
	at org.obiba.opal.core.service.ProjectsServiceImpl$DatasourceLoader.run(ProjectsServiceImpl.java:378)
Caused by: org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; liquibase.exception.DatabaseException: (conn=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:359)
	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:55)
	at org.obiba.magma.support.Initialisables.initialise(Initialisables.java:24)
	... 9 common frames omitted
Caused by: java.sql.SQLException: liquibase.exception.DatabaseException: (conn=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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:352)
	... 16 common frames omitted
Caused by: liquibase.exception.DatabaseException: (conn=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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:356)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229)
	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=39) Specified key was too long; max key length is 3072 bytes
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:177)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:334)
	at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:386)
	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)
	... 22 common frames omitted
Caused by: java.sql.SQLException: Specified key was too long; max key length is 3072 bytes
Query is: CREATE TABLE `opal_storage`.`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.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:200)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:328)
	... 26 common frames omitted
2022-04-04 12:10:41,860 ERROR org.obiba.opal.core.service.ProjectsServiceImpl - Loading datasource of project TITANIC_NEWCOMERS_WORKSHOP failed for database: opal_storage
org.obiba.magma.MagmaRuntimeException: org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; liquibase.exception.DatabaseException: (conn=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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:28)
	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.ProjectsServiceImpl.lambda$registerDatasource$0(ProjectsServiceImpl.java:279)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
	at org.obiba.opal.core.service.ProjectsServiceImpl.registerDatasource(ProjectsServiceImpl.java:263)
	at org.obiba.opal.core.service.ProjectsServiceImpl$DatasourceLoader.load(ProjectsServiceImpl.java:388)
	at org.obiba.opal.core.service.ProjectsServiceImpl$DatasourceLoader.run(ProjectsServiceImpl.java:378)
Caused by: org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; liquibase.exception.DatabaseException: (conn=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:359)
	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:55)
	at org.obiba.magma.support.Initialisables.initialise(Initialisables.java:24)
	... 9 common frames omitted
Caused by: java.sql.SQLException: liquibase.exception.DatabaseException: (conn=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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:352)
	... 16 common frames omitted
Caused by: liquibase.exception.DatabaseException: (conn=39) Specified key was too long; max key length is 3072 bytes [Failed SQL: CREATE TABLE `opal_storage`.`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:356)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1229)
	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=39) Specified key was too long; max key length is 3072 bytes
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:177)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:334)
	at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:386)
	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)
	... 22 common frames omitted
Caused by: java.sql.SQLException: Specified key was too long; max key length is 3072 bytes
Query is: CREATE TABLE `opal_storage`.`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.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:200)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:328)
	... 26 common frames omitted
Opal Server successfully started.
Opal is attached to this console. Press ctrl-c to stop.

This seems to exceed the allowed key length of the column defintion when trying to write to the database, but with regard to the “Omics” package.

13

Sven,

below is the script which I have been using successfully.

The CSV files I have been using have the following secure checksums (sha256):

  • 1767aa4eb86c27874853ab6688f1d7179efeb4f9c531ae639292db1dcb2c117f titanic_server_1.csv
  • 5989b0cbeb7f603361c2183eeb91743d05045e3e8d3db274dd0cbb52e7297ecf titanic_server_2.csv

Stuart

# helpful links: 
# https://rdocumentation.org/packages/opalr/versions/2.1.1/topics/opal.table_save
# https://rdocumentation.org/packages/opalr/versions/2.1.1/topics/opal.table_get


# setwd("~/datashield r work/2021 11 newcomers workshop")

library(opalr)

o <- opal.login(username = "administrator", password = "datashield_test&", url = "https://192.168.56.100:8443", opts=getOption('opal.opts', list(ssl_verifyhost=0, ssl_verifypeer=0)))

# your destination project
project <- "TITANIC_NEWCOMERS_WORKSHOP"
if (!opal.project_exists(o, project))
  opal.project_create(o, project, database = "mongodb", tags = list("Workshop"))

# load data using readr, see readr documentation if you want to specify column data types
# and save tibble in project (overwrite any existing table)
library(readr)
data <- readr::read_csv("titanic_server_1.csv")
colnames(data)[1] <- "PassengerId"
opal.table_save(o, data, project, table = "TITANIC_NEWCOMERS_DATASET_1", id.name = "PassengerId", overwrite = TRUE, force = TRUE)
data <- readr::read_csv("titanic_server_2.csv")
colnames(data)[1] <- "PassengerId"
opal.table_save(o, data, project, table = "TITANIC_NEWCOMERS_DATASET_2", id.name = "PassengerId", overwrite = TRUE, force = TRUE)

# build dictionary
variables <- tibble::tribble(
  ~name, ~valueType, ~`label:en`,
  "Survived", "decimal", "Survived Binary Status (0= Didn't)",
  "Pclass",   "decimal", "Ticket Class (1st/2nd/3rd)",
  "Name",     "text",    "Name",
  "Sex",      "text",    "Sex",
  "Age",      "decimal", "Age in Years",
  "SibSp",    "decimal", "Number of siblings and or spouses onboard Titanic",
  "Parch",    "decimal", "Number of parents and or children onboard Titanic",
  "Ticket",   "text",    "Ticket Number",
  "Fare",     "decimal", "Passenger Fare",
  "Cabin",    "text",    "Cabin Number",
  "Embarked", "text",    "Port of Embarkation (S=Southampton/C=Cherbourg/Q=Queenstown)"
)
categories <- tibble::tribble(
  ~variable, ~name, ~missing, ~`label:en`,
  "Survived", "0", 0, "Did not survive",
  "Survived", "1", 0, "Did survive",
  "Pclass",   "1", 0, "First Class",
  "Pclass",   "2", 0, "Second Class",
  "Pclass",   "3", 0, "Third Class",
  "Embarked", "S", 0, "Embarked at Southampton",
  "Embarked", "C", 0, "Embarked at Cherbourg",
  "Embarked", "Q", 0, "Embarked at Queenstown (Cork)"
)
# apply dictionary
opal.table_dictionary_update(o, project, table = "TITANIC_NEWCOMERS_DATASET_1", variables = variables, categories = categories)
opal.table_dictionary_update(o, project, table = "TITANIC_NEWCOMERS_DATASET_2", variables = variables, categories = categories)

# set permissions
opal.table_perm_add(o, project, table = "TITANIC_NEWCOMERS_DATASET_1", subject = "dsuser", permission = "view")
opal.table_perm_add(o, project, table = "TITANIC_NEWCOMERS_DATASET_2", subject = "dsuser", permission = "view")

opal.logout(o)
14

Sven,

What are you trying to import? a omics data file?

Yannick

15

Hi Yannick,

this is still about trying to import the “Titanic” sample .csv. The error message is showing up after logging into the OPAL WebUI, without any action on the dsOmics package or any import.

I had installed the “dsOmics” package, because that is something we want to use at a later stage. I will open a separate forum post, when I get to tackle that problem.

I posted the server error (in the logs, not presented to the user in the R-session), for reference. In the drop-in session I mentioned that there was still the possibility for an error in my installation.

Stuart provided me with a Rcmd-script for the csv-Titanic data to test against my DataSHIELD/Opal-installation. I still have to try that out.

If it works, this should solve this (forum)-issue, if it fails, I hopefully get a more meaningful error message, when I get to try it. Time constraints with other projects are slowing me down.

I wish you all happy Easter holidays!

-Sven

16

The error message has nothing to do with omics things. This is a MySQL/MariaDB misconfiguration:

See recommended config for MySQL/MariaDB.

Regards
Yannick