Database Server Setup for MariaDb on Linux¶

1. Install MariaDB server on Ubuntu (20.4)¶

   $ sudo apt update
   $ sudo apt install mariadb-server

Note:
The minimum version for MariaDB we support is 10.10. Previous versions may generally work, but they require additional configuration of global variables. The logical and simpler way to deal with is to use MariaDB 10.10+.

2. Ensure that MariaDB is running:¶

   $ sudo systemctl start mariadb.service

3. Configuration of the server:¶

   $ sudo mysql_secure_installation
   Enter current password for root (enter for none): <root-password>

3.1 Increase max_allowed_packet¶

The default value of max_allowed_packet will allow transfer of packages of 16MiB. Although this seem reasonable for normal text communication, it can limit transfer of LOBs. In 4GL, LOBs can be up to 1GiB, which is actually the maximum value of max_allowed_packet, so this is the value which should be used if the application uses pieces of data as large.

Open your /etc/mysql/mariadb.cnf and add the following line:

[mariadb]
max_allowed_packet=1073741824

Refs: MariaDb KB, Goldencode KB

3.2 Switch transaction isolation to READ COMMITTED¶

As above, open the MariaDb configuration file and update the following line:

[mariadb]
transaction-isolation=READ-COMMITTED

Refs: MariaDb KB, Goldencode KB

After appying any of the above changes, be sure you restart the MariaDb server so that the configuration file is re-read:

sudo systemctl restart mariadb

4. Checking server's status:¶

   $ sudo systemctl status mariadb

5. Install PHP-MyAdmin (optional)¶

   $ sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl
   $ sudo phpenmod mbstring
   $ sudo systemctl restart apache2

In case the above fails try:

   $ sudo apt-get install libapache2-mod-wsgi-py3

6. Adjusting User Authentication and Privileges¶

   $ sudo mysql
   > CREATE USER fwd_admin@localhost IDENTIFIED BY 'admin';
   > GRANT ALL PRIVILEGES ON *.* TO fwd_admin@localhost WITH GRANT OPTION;

7. Creating a database with specific collation and character set:¶

   > CREATE DATABASE fwd CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

8. Connect with a console and execute the first SQL statements:¶

$ mysql -u fwd_admin -P

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| fwd                |
| mysql              |
| performance_schema |
| phpmyadmin         |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> use fwd;
Database changed

MariaDB [fwd]> show tables;
+---------------------------+
| Tables_in_fwd             |
+---------------------------+
| book                      |
| contact                   |
| cust_omers                |
| customer                  |
[...]
+---------------------------+
49 rows in set (0.000 sec)