--- last_review: "2025-01-01" last_reviewer: "-" documented_code: [ ] --- ```{tags} how-to ``` # Setup of the LinkAhead MariaDB backend :::{note} This page has been migrated from the old documentation, and has not yet been fully revised. There might be inconsistencies or errors when using with current LinkAhead versions. ::: % TODO: Issue: https://gitlab.indiscale.com/caosdb/src/linkahead-docs/-/work_items/99 % TODO: Split into README, Tutorial, and How-To % TODO: This is a README page from the caosdb-mysqlbackend repository, see % TODO: [here](https://docs.indiscale.com/caosdb-mysqlbackend/README_SETUP.html). The README should % TODO: be rewritten to be a minimal setup and usage guide, with a link to this documentation page % TODO: for further information. ## Dependencies * `MariaDB Client 11.2` or later. * 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` program, 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 password 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 chosen 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](https://mariadb.com/kb/en/grant/#global-privileges), for example `CREATE USER` and the privileges to grant that user all global privileges. * For normal usage, [database privileges](https://mariadb.com/kb/en/grant/#database-privileges) are required. % TODO: The required privileges may be reduced in the future. ## 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`. :::{warning} Use this with caution! This deletes the database permanently, and there is no prompt to ask if you are sure. If you do not back up your database before running the command, your data will be lost. ::: ## 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](https://hepabolu.github.io/mytap/) 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`. ### Developing tests ### To use the mytap framework: ```console $ cd libs; unzip mytap-1.0.zip; cd mytap-1.0 # Use the necessary mariadb options for connecting to the server in the following commands $ mariadb ... < mytap.sql # Set up the "tap" database and functions. $ mariadb ... < scripts/autotap.sql # Insert the "autotap" function. # Create autotap file (for comparison with current state) $ mariadb ... --raw -B --skip-column-names -e "call tap.autotap('_caosdb_schema_unit_tests')" > test_autotap.current.sql # Run the autotap tests (may fail unfortunately with current MariaDB): $ mariadb ... < test_autotap.current.sql ``` ### Troubleshooting #### 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. #### MySQL has failing tests *Note: Since we switched from MySQL to MariaDB, the two DBMS projects have diverged quite far. As a result, we no longer support MySQL.