Setup of the CaosDB SQL back end

Dependencies

  • MariaDB Client 10.1 or later, MySQL Client >=5.5, <=5.7.36.

  • make

Create the configuration

  • Create an empty .config file. For the default values and the meaning of these default values see config.defaults. For each parameter that you want to change, add a corresponding line in your .config file. You probably want to change the passwords. As the passwords are stored unencrypted in the .config file, make sure nobody else can read it.

    • If there is no mysql-config-editor (MySQL 5.5. and MariaDB) then the MYSQL_USER_PASSWORD must be provided, that is the password of the MYSQL_USER.

    • If you are using MariaDB and the root database user uses pam authentication, no pasword string is required. You can set the MYSQL_USER_PASSWORD to an empty string. But you need to be logged in as root for the installation and upgrade process.

Setup the SQL database for CaosDB

  • Run make install. If a there is a database with the name you have choosen during the configuration, you need to reconfigure or delete the database first.

    • Required database privileges:

      • If the user does not exist yet, you need the appropriate global privileges, for example CREATE USER and the privileges to grant that user all global privileges. The required privileges may be reduced in the future.

      • For normal usage, database privileges are required.

Upgrade the SQL database

  • Run make upgrade. This upgrades the database to the latest version.

Drop Database

  • If you want to delete your database, run make drop-$DATABASE_NAME. Use this with caution!!! If you did not backup your database, everything will be lost afterwards. And no, there is no additional prompt to ask if you are sure. If you make drop-..., you actually delete the database.

Versioning

The versioning feature is still experimental. Therefore it is possible to turn if on and off with a patch file and a special property.

Procedures and Functions

The procedures which need to behave differently if the versioning is on or off check the return value of is_feature_config("ENTITY_VERSIONING", "ENABLED"). The is_feature_config function checks the feature_config table, which is a key-value store.

Turn off versioning: Run UPDATE feature_config SET _value = "DISABLED" WHERE _key = "ENTITY_VERSIONING"; on your database.

Turn on versioning again: Run UPDATE feature_config SET _value = "ENABLED" WHERE _key = "ENTITY_VERSIONING"; on your database.

Data

When the versioning patch is installed, the versioning is turned on by default and all old entities become versioned entities with their current version as the oldest known version. That is, they all need an entry in the entity_version table. These entries are generated by the _fix_unversioned procedure.

If you want to turn off the versioning for the time being you can just turn it off for the procedures as described above. You should also empty the entity_version table because the _fix_unversioned procedure is only designed to cope with entities which do not have any versioning information at all. The already recorded versioning information is of course lost then! If you switch on the versioning at some point in the future, the history begins anew with the then current version of the stored entities.

Unit tests

  • We use MyTAP-1.0 for unit tests.

  • Requirements:

    • mysqladmin

    • mysqldump

    • mysql client program

  • Tests are in tests/test_*.sql.

  • Run make test.

    • Alternatively, to run the tests in a containerized MariaDB instance, run make test-docker, followed by make test-docker-stop.

Running in a Docker Container

You can use .docker/docker-compose.yml to start a docker container (docker-compose -f .docker/docker-compose.yml up -d) that runs mariadb. You need appropriate settings in .config:

  • MYSQL_OPTS="--protocol=TCP" and

  • DATABASE_USER_HOST_LIST=%, After the first start, you need to install the database: make install. Then, you can connect to it with mariadb --protocol=TCP -u caosdb -prandom1234 caosdb.

Troubleshooting

MySQL has failing tests

Our test suite is developed with MariaDB. That is why some tests, those which check constraints based on auto-generated names, fail because MySQL generates other names.

Also some test fail with the message “Expected: NULL / Was: NULL” which is obviously what was expected.

Please look carefully through the tests. If something more serious than that comes up, please report a bug.

Failure to restore a database dump created with an older MariaDB version

Have a look into the dump_updates/README.md. In cases of version incompatibilities, the necessary steps to migrate the dump are probably described there and scripts for the migrations are provided in the same directory.

Documentation

Build documentation in build/ with make doc.

Requirements

  • sphinx

  • sphinx-autoapi

  • python3-sqlparse

  • doxygen

  • python3-lxml

  • breathe (Doxygen extension for Sphinx)

  • recommonmark