Ensembl MySQL services will be upgraded to MySQL 8 soon

by ·Comments Off on Ensembl MySQL services will be upgraded to MySQL 8 soon

Users of the publicly accessible Ensembl MySQL servers should note that we will soon be upgrading these services from MySQL 5.6 to MySQL 8.

The Ensembl public MySQL services are currently available at:

  • ensembldb.ensembl.org (ports 3306 or 5306)
  • useastdb.ensembl.org (ports 3306 or 5306)
  • asiadb.ensembl.org (ports 3306 or 5306)
  • martdb.ensembl.org (port 5316), for BioMart databases
  • mysql-eg-publicsql.ebi.ac.uk (port 4157) for former Ensembl Genomes (non-vertebrates)

These servers are available using the anonymous MySQL user, as described in the Ensembl public MySQL documentation.

This change will affect Ensembl public MySQL services for data releases up to Ensembl 116 and Ensembl Genomes 63.

This change does not affect programmatic access to data from the new Ensembl platform at beta.ensembl.org, which is available via Ensembl GraphQL rather than these MySQL services.

Why are we making this change?

MySQL 5.6 is now an old database platform. Upgrading to MySQL 8 allows us to run these services on a supported, more secure, and more maintainable database version.

The Ensembl schemas and data content are not being redesigned as part of this upgrade. However, MySQL 8 differs from MySQL 5.6 in several behaviours that may affect older clients, scripts, and local database installations.

What is most likely to require attention?

Most users who connect with recent MySQL clients, recent language bindings, and the Ensembl Perl API should only need to test their existing workflows against the upgraded service.

The areas most likely to need attention are:

Older MySQL clients and client libraries

Very old MySQL clients, old libmysqlclient libraries, and older language bindings may fail to connect to a MySQL 8 server.

This may affect users of:

  • Older command-line mysql clients
  • Older Perl DBD::mysql
  • Older Python MySQL connectors
  • Older R MySQL/MariaDB connectors
  • Tools compiled against older MySQL client libraries

We recommend upgrading your MySQL client and language-specific connector packages before the public service is upgraded.

For Perl API users, this means ensuring that DBI, DBD::mysql, and the underlying MySQL or MariaDB client libraries are up to date.

Authentication plugin compatibility

MySQL 8 uses caching_sha2_password as the default authentication plugin instead of mysql_native_password.

The Ensembl public user is anonymous, so many users may not notice this directly. However, this can matter if you run a local MySQL 8 mirror, create your own users, or use older client libraries that do not support the MySQL 8 default authentication plugin.

If you run a local mirror and encounter authentication-related connection errors, check whether your client library supports caching_sha2_password . Alternatively, for local compatibility testing only, you may choose to create local users with a different authentication plugin according to your local security policy.

Importing Ensembl FTP MySQL dumps into a local MySQL 8 server

The MySQL data files available from the Ensembl FTP sites were originally produced for loading into MySQL-compatible Ensembl database installations.

Users importing these files into local MySQL 8 instances may need to adjust their import process.

Potential issues include:

  • Obsolete or version-specific statements in dump files
  • SQL mode differences
  • Stricter parsing in MySQL 8
  • Reserved words introduced after MySQL 5.6
  • Character set and collation defaults
  • LOAD DATA LOCAL INFILE being disabled by default

MySQL 8 has a larger set of reserved keywords than older versions.

For example, identifiers that were legal without quoting in older MySQL versions may require backticks in MySQL 8.

MySQL documents the current reserved keyword list and marks reserved words explicitly.

MySQL 8 also defaults to utf8mb4 and utf8mb4_0900_ai_ci . This is generally beneficial, but users comparing sort order, text matching, or checksums generated from exported text should be aware that character set and collation defaults may differ from older MySQL installations.

LOAD DATA LOCAL INFILE

Some local import workflows rely on LOAD DATA LOCAL INFILE. In MySQL 8, local_infile is disabled by default. If either the server or client disables local loading, imports may fail with an error such as:

ERROR 3950 (42000): Loading local data is disabled; this must be enabled
on both the client and server side

For local imports, you may need to enable this explicitly on both the server and client side, for example with server configuration and the mysql –local-infile=1 client option. MySQL documents the security implications of enabling this feature, so please only enable it for trusted import workflows.

What should users do?

We recommend testing your workflows before the upgrade, especially if you use automated scripts or maintain a local Ensembl mirror.

If you connect directly to the public MySQL servers

Test that your client can connect using the same host, user, and port you currently use.

For example:

mysql -h ensembldb.ensembl.org -u anonymous
# or for a regional mirror:
mysql -h useastdb.ensembl.org -u anonymous
mysql -h asiadb.ensembl.org -u anonymous
# For BioMart databases:
mysql -h martdb.ensembl.org -u anonymous

If you use the Ensembl Perl API, test your registry-loading code, for example:

use Bio::EnsEMBL::Registry;
Bio::EnsEMBL::Registry->load_registry_from_db(
  -host => 'ensembldb.ensembl.org',
  -user => 'anonymous',
);

Or equivalently the ping_ensembl.pl script distributed with the Perl API.

The Ensembl API documentation describes loading the registry from the public MySQL server using ensembldb.ensembl.org and the anonymous user.

If you use Python, R, Java, or other language bindings

Update your MySQL connector packages and retest.

For Perl users, check:
perl -MDBI -e 'print $DBI::VERSION, "\n"'
perl -MDBD::mysql -e 'print $DBD::mysql::VERSION, "\n"'

If needed, update DBD::mysql using your normal package manager or CPAN workflow. For Python, R, Java, or other environments, make sure your connector supports MySQL 8 and has been rebuilt against a compatible MySQL or MariaDB client library where applicable.

If you maintain a local Ensembl MySQL installation

The Ensembl FTP site provides MySQL database files for download. For Ensembl vertebrates, these are available from the Ensembl FTP MySQL directory; Ensembl Genomes divisions provide equivalent MySQL directories, for example for Plants.

These files can be used to populate a local MySQL-compatible instance. If you need exact historical compatibility with existing Ensembl MySQL 5.6-era workflows, running a local MySQL 5.6-compatible environment may still be the simplest option. If you choose to load the data into MySQL 8, test the import process carefully.

A typical local workflow may look like:

# 1. Create a database
mysql -u root -p -e 'CREATE DATABASE homo_sapiens_core_116_38;'

# 2. Download the database directory from the Ensembl FTP site
wget -r -np -nH --cut-dirs=3 \
https://ftp.ensembl.org/pub/current/mysql/homo_sapiens_core_115_38/

# 3. Uncompress downloaded files
for ens_file in *.txt.gz
do
  echo "Uncompressing $ens_file"
  gzip -d $ens_file
done

# 4. Import table definitions
mysql -u root -p -D homo_sapiens_core_116_38 < homo_sapiens_core_116_38.sql

# 5. Load table data
mysqlimport -u root -p --fields-terminated-by='\t' --fields-escaped-by=\\ homo_sapiens_core_115_38 -L *.txt

Then, from inside the MySQL client, load the required data files using the statements provided with the downloaded database files. Exact commands vary depending on the release, database, and whether you are using MySQL, MariaDB, Docker, or another local deployment method.

The important points are:

  • use a MySQL/MariaDB version that is compatible with your workflow
  • ensure your client and server both permit any required LOAD DATA LOCAL INFILE usage
  • check SQL mode, character set, and collation settings
  • run a small test import before importing large databases
  • verify row counts after import

Common issues and possible fixes

“Client does not support authentication protocol” or authentication plugin errors

Upgrade your MySQL client or connector. For local MySQL 8 installations, check whether your user account uses caching_sha2_password and whether your client supports it.

“Loading local data is disabled”

Enable local_infile explicitly for trusted local imports, both on the server and client side.

For example, the command-line client may require:

mysql --local-infile=1 -u root -p

MySQL documents that local_infile is disabled by default in MySQL 8.

SQL syntax errors during import

Check whether the failing statement uses an identifier that is a reserved word in MySQL 8.

If so, quoting the identifier with backticks may be required.

MySQL provides a version-specific list of keywords and reserved words.

Differences in text sorting or comparison

Check the character set and collation used by your local server, database, and tables. MySQL 8 defaults to utf8mb4 and utf8mb4_0900_ai_ci , which may differ from older MySQL installations.

Perl API connection errors

Update DBD::mysql, DBI, and the underlying MySQL/MariaDB client libraries. Then retest a minimal registry connection before running larger workflows.

Database files on the Ensembl FTP site

MySQL files for Ensembl vertebrate databases are available from the Ensembl FTP site, for example:

https://ftp.ensembl.org/pub/current/mysql

Equivalent files are available for Ensembl Genomes divisions, for example Plants:

https://ftp.ensemblgenomes.ebi.ac.uk/pub/plants/current/mysql

These files are free to download and can be used to run a local Ensembl MySQL-compatible database instance.

Programmatic services for the new Ensembl platform

Data from the new Ensembl platform at beta.ensembl.org is programmatically accessible via Ensembl GraphQL and RefGet API.

These services are independent of the public MySQL servers described above and not affected by this MySQL 5.6 to MySQL 8 upgrade.

Need help?

If you have questions or concerns about this change, please contact the Ensembl Helpdesk or write to us via the Ensembl developer mailing list.

When reporting a problem, please include:

  • The MySQL host you are connecting to
  • The database name
  • The client or connector you are using
  • The operating system or container image, if relevant
  • The exact error message
  • Whether the issue occurs against the public server, a local mirror, or both

References

Authored by: Stefano Giorgetti