Maintain database version
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.
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.
You need to update your database.
How to update the database?
Check your recent database version with the following command.
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.
First of all, let’s back up the databases.
mysqldump --all-databases > all_databases.sql
Download the latest APT package repository.
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
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