MariaDB/MySQL databases: Recent setups require a fix

Recent demand for supporting SeisComP databases in storing a wider range of characters including smileys motivated us to support creating MariaDB/MySQL databases with the utf8mb4 charset. When using scconfig or the seiscomp script for setting up databases, utf8mb4 charset was the default. Unfortunately, the default collation of this charset does not discriminate upper and lower case characters leading to public ID collision. For resolving the issue we provide an easy-to-use script which identifies the potential issue providing the required database migration script.

The issue applies to MariaDB/MySQL SeisComP databases created with the charset utf8mb4 and SeisComP in versions 6.0.0 <= version < 6.7.0 or our nightly releases published after 17 August 2023. Effected are public objects with ID patterns sensitive to lower and upper case characters such as of mb and mB station magnitudes.

The issue has been identified and will be resolved for new MariaDB/MySQL databases created with SeisComP tools as of SeisComP in version >=6.7.0.

Whether or not your database is affected can be easily tested and affected databases can be converted to use case-sensitive collation by the script gempa-check-database which we publicly provide. Apply the procedure below.

1. Login to your computer

2. Install our public package seiscomp-tools as any other gempa module or SeisComP using gsm. First navigate to the gsm directory, then run the update and install the package.

   Example:

   1 2 3

   cd $HOME/install/gsm gsm update gsm install seiscomp-tools

   Alternatively, download and install the package from https://data.gempa.de/packages/Public/seiscomp-tools/nightly/any/noarch/ or just download the script from https://data.gempa.de/packages/Public/tools/ .

   3. Run the script for identifying the potential issue.

   Examples:

   1 2 3

   gempa-check-database -h gempa-check-database --debug gempa-check-database -d mysql://sysop:sysop@localhost/seiscomp

3. If your database is unaffected, you will receive a positive feedback and no further action is required.

   If you database is affected, a database migration script is printed and need to be applied. You will need read/write access to your database. The database credentials can be found in the scmaster configuration.

   • Stop scmaster

     1	seiscomp stop scmaster

   • Ensure, no other modules like scdb, scardac, etc. or custom tools from internal or external clients attempt accessing the database.

   • Use the database migration script to update the database.

     Example for database seiscomp and database user sysop:

     1	mysql -u sysop -p seiscomp < /tmp/update-mysql-charset-utf8mb4.sql

4. Repeat the database check when the database charset has been changed. The script will print the positive feedback and you are done with the changes.

5. Finally start scmaster and verify all default modules are running nominally

   1 2	seiscomp start seiscomp status enabled