1. Home
  2. Docs
  3. Infrastructure
  4. MariaDB

MariaDB

We use MariaDB instead of MySQL whenever possible. The primary reason is ERPNext supports MariaDB better. The downside of this is:

  • Some apps, like FusionAuth, are known to have issues with MariaDB.
  • If we need AWS Aurora, it only supports MySQL, not MariaDB. However, Aurora itself is not 100% compatible with MySQL especially newer features.

We use AWS RDS MariaDB instead of DigitalOcean’s managed database, because DigitalOcean requires providing a certificate chain which requires additional configuration and in some cases significant workarounds especially in Node.js based apps.

Parameter groups

Parameter group: lovia-mariadb-10-4

character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
max_connections=150

As MariaDB is used by FusionAuth, WordPress, Camunda, etc. and each can open multiple connections, the MariaDB instance must have plenty max_connections limit.

Converting database and tables to utf8mb4 with collation utf8mb4_unicode_ci

If you already had database and tables in latin1 or utf8 character set, here’s how to change them to utf8mb4 and utf8mb4_unicode_ci combo.

Note: FusionAuth uses utf8mb4 character set but with utf8mb4_bin collation.

Check current character set and collation for a database and specific table:

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "camunda";

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "camunda" AND T.table_name = "ACT_HI_JOB_LOG";

Change character set and collation of database:

ALTER DATABASE `camunda` CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci";

Note that you’ll still need to change character set and collation for individual tables. To do this you can generate the needed ALTER TABLE DDL statements:

mysql -h "$MYSQL_HOST" -u "$MYSQL_USER" -p --database=camunda -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE `" $1 "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1; "}'

You’ll get DDL statements like this that you can then execute:

SET foreign_key_checks = 0; ALTER TABLE `application_daily_active_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1;
SET foreign_key_checks = 0; ALTER TABLE `application_monthly_active_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1;
SET foreign_key_checks = 0; ALTER TABLE `application_registration_counts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1;
Was this article helpful to you? Yes No

How can we help?

Leave a Reply

Your email address will not be published. Required fields are marked *