Database Check stuck running initial web install - FIXED

[p3isys]

Hello. I've been using OTRS for many years. Currently doing a test install of Znuny to do a new ticketing system
Server Ubuntu 20.04 LTS
Database Ver 15.1 Distrib 10.3.32-MariaDB

Upon initial installation I could not get past the initial install script web page (hostname/ip)/otrs/installer.pl

When I ran the database check the following error would not go away:

Result of database check
Wrong database collation (character_set_database is utf8mb4, but it needs to be utf8)
And it was impossible to proceed with the installation.

I went into MariaDB and altered the database collation and character set to utf8 and utf8_general_ci, but this did not make the error go away.
Changing the server collation and character sets at the Mariadb console also did not work.

The issue is in this file: /etc/mysql/mariadb.conf.d/50-server.cnf
Here are the relevant lines in the file, and I've copied them because the comments explain the source of the issue:

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

The configuration files from the Ubuntu software repositories appear to defer to some legacy settings left over from Debian.
After I first started using OTRS 2 many years ago it installed and ran normally, but several years later I began to experience errors because the original default character set and collation for MySQL installations was latin1_swedish_ci because Sweden was the home country of the creator of MySQL Monty Widenius (who later started the fork of the MySQL project called MariaDB after the original project was taken over by Mordor, er, I mean Oracle Corp, but I digress).

The fix is simple: just delete the characters mb4 out of the character set and collation lines in 50-server.cnf so they read as follows:
character-set-server = utf8
collation-server = utf8_general_ci

The check script doesn't care just about the charset and collation of the database, it wants those values set for the MariaDB Server, so the prompt is a little misleading.
These easy corrections will make Znuny happy and your installation can proceed.

I hope this is helpful for someone else. It took me a half hour to figure out what was going on as I stubbornly kept re-creating the database and setting the character set and collation there.

[root]

Hi,

Your issue can be prevent when the database (and user) is created prior with this SQL statement:

Code: Select all

CREATE DATABASE znuny CHARACTER SET utf8 COLLATE utf8_general_ci

Anyway, I'll look into it because the installer could do the same.

- Roy

[p3isys]

Thanks, Roy, good to hear from you again after several years.

I actually did re-create the database using the very command you suggested, but that did not get rid of the error.
I tried to describe it accurately - as long as the default collation was utfmb4 and the default charset was utfmb4_general_ci, the error persisted.
I am not adept enough at the internal code to find the script that is used to run the DB check, but it appears to be checking the server variables.
Even after dropping the database, then running CREATE DATABASE znuny CHARACTER SET utf8 COLLATE utf8_general_ci
and verifying the database collation and charset using show create database znuny and getting this result:
MariaDB [(none)]> show create database ztsdb;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| ztsdb | CREATE DATABASE `ztsdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+

And even after verifying with this command and result...

MariaDB [(none)]> select * from INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | mysql | utf8mb4 | utf8mb4_general_ci | NULL |
| def | ztsdb | utf8 | utf8_general_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
4 rows in set (0.000 sec)

... the database checker on the installer.pl page would still yield the error:
Wrong database collation (character_set_database is utf8mb4, but it needs to be utf8)

I don't know what the database checker is looking at, but it doesn't appear to be checking the actual collation and character set of the database specified in /opt/otrs/Kernel/Config.pm

In fact the error came up even after dropping the database completely in order to let the scripts run by the installer.pl page create the database, the error appeared even when there was no database yet created, so there would be no database collation or character set to check. As long as you understand what's happening (which took me a few attempts) it's easy enough to fix. The fault is in the Ubuntu Server repository default configuration of MariaDB, just as the original default configuration of MySQL used latin1_swedish_ci as the character set as a paean to Monty Widenius a long time ago.

It could be fixed with no need to visit the code by adding the edits to 50-server.cnf to the install guide. Or the database check script (which is really very smart, able to use a pre-existing database or create the database and a user with just enough privileges to do the necessary work for the Znuny app on the fly without compromising security) could be changed to ignore the server default and simply ensure the database has the right collation and character set defined.

I'm not complaining about anything - the problem is fixed. I just posted my message to try to make sure that there was an easily found fix in case someone else runs into the same issue.

Thanks again for your very prompt reply! And for your ongoing support of the project! I salute you, sir!

kind regards,
Rob

[root]

Hi Rob,

I'm going to look into this. Even if your change of the configuration file works - and we do this too - it should be investigated.
I'll let you know what the outcome will be

One hint: don't edit the distributed file, just add an new on in the same directory with the required settings like etc/mysql/mariadb.conf.d/99-znuny.cnf
And just really add only the required settings. This way you will also get changed/new settings when the database package is updated. This might not really happens often but I recommend it.

- Roy