Use MySQL 8.0

[pvannierop]

In this blueprint I will detail the steps needed to update cBioPortal source code and deployment details to run MySQL 8 (current version is MySQL 5.7)

Step 1

MySQL requires new allowPublicKeyRetrieval and allowLoadLocalInfile parameters to be added to the client connection parameters. Rather than extending DatabaseProperties (and by extension portal.properties) to accommodate these properties, I propose to force users to provide only the _db.connectionstring (existing property that appears not/rarely used), like so.

db.connection_string=jdbc:mysql://<host>/cbioportal?allowPublicKeyRetrieval=true&allowLoadLocalInfile=true

This will allow full flexibility to configure previous, current and future MySQL versions.

Note: username and password will still be provided as the existing db.user and db.password properties (not passed as part of connection string for MySQL jdbc connector). The existing db.host, db.database, db.port and _db.usessl properties become obsolete.

Finally, a single connection string is also used by default for configuration of Spring Boot datasources.

This change is proposed in PR cBioPortal/cbioportal#10037

Step 2

Set the allowPublicKeyRetrieval parameter in the connection URL (_db.connectionstring) to true:

jdbc:mysql://<host>/cbioportal?allowPublicKeyRetrieval=true

Step 3

In order to provide bulk load functionality to the Java importer, the local infile capability must be activated in MySQL 8 and in the client connection string, like so:

In MySQL 8 run :

SET GLOBAL local_infile = 'ON';

In client connection URL (_db.connectionstring) set the allowLoadLocalInfile parameter to true:

jdbc:mysql://<host>/cbioportal?&allowPublicKeyRetrieval=true&allowLoadLocalInfile=true

Note on allowLoadLocalInfile

Activation of the allowLoadLocalInfile parameter is considered a security risk. It should be encouraged to only activate this property in the connection string when provisioning the database, not when running the portal web appplication.

Step 4

Because the table structure of MySQL 5 and MySQL 8 are incompatible, migration of an exiting database files is performed via a sequential data dump and import.

[jagnathan]

cBioPortal/icebox#355 linking issue on the mysql 8 upgrade