Maintain database version

Introduction

With the server solution of Easy Redmine, there is the advantage of hosting your own application and having the ability to completely control it. This is, however, tied to the responsibility to maintain the whole environment and infrastructure that the application is running on. This article is a (quite precise) guideline to maintaining your MySQL or MariaDB up-to-date.

You have an error in your SQL syntax

During application update an error message containing "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ..." appears in the log.

Cause
It means your database is outdated and it can't work with the request our application sends to it. It just doesn't understand it.

Solution
You need to update your database.

How to update the database?
Check your recent database version with the following command.

mysql --version

It will show you that you either have MySQL 5.5 or less or MariaDB 10.1 or less. Now depending on your DB type (Mysql or MariaDB) upgrade the database. Please note, this instruction is for Debian 10, which is right now the main OS we support. Supported DB versions, for now, are: MySQL 5.7 or higher, MariaDB 10.3 or higher.

MySQL

First of all, let’s back up the databases.

mysqldump --all-databases > all_databases.sql

Download the latest APT package repository.

wget https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

Make sure you download the latest version of the package. Then install it using dpkg.

sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb

You will be prompted with a dialog box, asking which version to install. If MySQL 5.8 isn’t there, press Enter then select version 5.8 from the list, select OK. Then run the package update.

sudo apt-get update

You will see the MySQL package is on the list.

sudo apt-get install mysql-server

This is the most important step you can’t miss, upgrade the existing databases.

sudo mysql_upgrade -u root -p

The final step, restart the database.

sudo /etc/init.d/mysql restart

MariaDB

Take a full backup.

      --user=mariabackup_user \
      --password=mariabackup_passwd \
      --target-dir=/backup/preupgrade_backup

Confirm successful completion of the backup operation. The backup must be prepared.

$ sudo mariadb-backup --prepare \
      --target-dir=/data/backup/preupgrade_backup

Confirm the successful completion of the prepared operation. Backups should be tested before they are trusted. Uninstall the old version. When upgrading to a new major release of MariaDB Community Server, it is necessary to remove the existing installation of MariaDB Community Server before installing the new version of MariaDB Community Server. Otherwise, the package manager will refuse to install the new version of MariaDB Community Server. Before the old version can be uninstalled, we first need to stop the current MariaDB Server process.

$ sudo systemctl stop mariadb

Uninstall all of the MariaDB Community Server packages. Note that a wildcard character is used to ensure that all MariaDB Community Server packages are uninstalled.

$ sudo apt remove "mariadb-*"

Uninstall the Galera package as well. The name of the package depends on the specific version of MariaDB Community Server. When upgrading from MariaDB Community Server 10.4, the package is called galera-4.

$ sudo apt remove galera-4

When upgrading from MariaDB Community Server 10.3 or earlier, the package is called galera.

$ sudo apt remove galera

Before proceeding, verify that all MariaDB Community Server packages are uninstalled. The following command should not return any results.

$ apt list --installed | grep -i -E "mariadb|galera"

Install the new version. MariaDB Corporation provides an APT package repository for Debian 10. Install via APT (Debian/Ubuntu). To configure APT package repositories, use the following commands.

$ sudo apt install wget

$ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

$ echo "2de6253842f230bc554d3f5ab0c0dbf717caffbf45ae6893740707961c8407b7 mariadb_repo_setup" \
    | sha256sum -c -

$ chmod +x mariadb_repo_setup

$ sudo ./mariadb_repo_setup \
   --mariadb-server-version="mariadb-10.5"

$ sudo apt update

Install MariaDB Community Server and package dependencies.

$ sudo apt install mariadb-server mariadb-backup libmysqlclient-dev

Configure MariaDB. Installation only loads MariaDB Community Server to the system. MariaDB Community Server requires configuration before the database server is ready for use. Upgrade the Data Directory using the following command.

$ sudo mariadb-upgrade