1

Topic: MariaDB 10.4 not creating iRedMail databases

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 1.1
- Deployed with iRedMail Easy or the downloadable installer? downloadable installer
- Linux/BSD distribution name and version: FreeBSD 12.1
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): LDAP
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? No
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Brought over from the 1.1 Release Announcement:

Due to the way MariaDB 10.4 changed how the root account accesses the database, and how MariaDB stores users (mysql.user is now a view of mysql.global_priv), changing the root password in functions/mysql.sh doesn't work:

            # MySQL 5.7
            ECHO_DEBUG "Setting password for MySQL root user: ${MYSQL_ROOT_USER}
."
            #if [ X"${LOCAL_ADDRESS}" == X'127.0.0.1' ]; then
            #    # Get initial random root password from /root/.mysql-secret
            #    export _mysql_root_pw="$(tail -1 /root/.mysql_secret)"
            #    mysqladmin -h ${MYSQL_SERVER_ADDRESS} -u${MYSQL_ROOT_USER} -p${
_mysql_root_pw} password ${MYSQL_ROOT_PASSWD} >> ${INSTALL_LOG} 2>&1
            #else
            #    # Jail
                mysql -h ${MYSQL_SERVER_ADDRESS} -u${MYSQL_ROOT_USER} --connect-
expired-password mysql -e "UPDATE user SET host='${LOCAL_ADDRESS}',authenticatio
n_string=PASSWORD('${MYSQL_ROOT_PASSWD}'),password_expired='N' WHERE User='root'
 AND Host='localhost'; FLUSH PRIVILEGES;" >> ${INSTALL_LOG} 2>&1
           # The above code doesn't work anymore, root has to connect via socket

Here's the relevant error from runtime/install.log (ERROR 1348 (HY000) at line 1: Column 'authentication_string' is not updatable:

[ INFO ] Configure MariaDB database server.
 + < DEBUG > Initialize MySQL server.
 + < DEBUG > Stop MySQL service before initializing database or updating my.cnf.
 + < DEBUG > Enable mysql service when system start up.
mysql_enable:  -> YES
mysql_optfile:  -> /var/db/mysql/my.cnf
 + < DEBUG > Copy sample MySQL config file: /usr/local/share/mysql/my-large.cnf
-> /var/db/mysql/my.cnf.
 + < DEBUG > Disable 'skip-networking' in my.cnf.
 + < DEBUG > Set max_connections to 1024.
 + < DEBUG > Enable 'skip_grant_tables' option, so that we can reset password.
 + < DEBUG > Enable 'bind-address = 192.168.0.3' in my.cnf.
 + < DEBUG > Restart service: mysql-server.
mysql not running? (check /var/db/mysql/mx.peakbias.com.pid).
2020-03-20 21:33:20 0 [Warning] WSREP: Failed to guess base node address. Set it
 explicitly via wsrep_node_address.
2020-03-20 21:33:20 0 [Warning] WSREP: Failed to guess base node address. Set it
 explicitly via wsrep_node_address.
2020-03-20 21:33:20 0 [Warning] WSREP: Guessing address for incoming client conn
ections failed. Try setting wsrep_node_incoming_address explicitly.
 + < DEBUG > Sleep 10 seconds for MySQL daemon initialization ...
 + < DEBUG > Setting password for MySQL root user: root.
ERROR 1348 (HY000) at line 1: Column 'authentication_string' is not updatable
 + < DEBUG > Remove 'skip_grant_tables'.
 + < DEBUG > Restart service: mysql-server.
 + < DEBUG > Sleep 10 seconds for MySQL daemon initialization ...
 + < DEBUG > Generate defauts file for MySQL client option --defaults-file: /roo
t/.my.cnf.
 + < DEBUG > Delete anonymous database user.

As root I connected to the MariaDB server, via localhost, and you can see no databases were created.

[root@mx ~]# mysql
ERROR 1130 (HY000): Host '192.168.0.3' is not allowed to connect to this MariaDB server
[root@mx ~]# mysql -h localhost
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 64
Server version: 10.4.12-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

MariaDB [(none)]> 

In my previous install, I installed MariaDB 10.4 in a separate jail and used the "install with remote MySQL" instructions. This worked. Here I'm installing on another host, and I wanted to make a proper post and not muddy up the 1.1 announcement thread.

As suggested I will try again with the master branch from GitHub and will update this thread on how it goes.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2

Re: MariaDB 10.4 not creating iRedMail databases

Well, install from master branch didn't work but not because of MariaDB, just ports weirdness:

===>  Checking if dbus-glib is already installed
===>   Registering installation for dbus-glib-0.110 as automatic
pkg-static: Cannot get an exclusive lock on a database, it is locked by another process
*** Error code 75

Stop.
make[3]: stopped in /basejail/usr/ports/devel/dbus-glib
*** Error code 1

Stop.
make[2]: stopped in /basejail/usr/ports/net/avahi-app
*** Error code 1

Stop.
make[1]: stopped in /basejail/usr/ports/lang/gnustep-base
*** Error code 1

Stop.
make: stopped in /basejail/usr/ports/devel/sope4
<< ERROR >> Port was not successfully installed, please fix it manually and then re-execute this script.

3

Re: MariaDB 10.4 not creating iRedMail databases

rogwynn wrote:

pkg-static: Cannot get an exclusive lock on a database, it is locked by another process

- Did you modify "functions/packages_freebsd.sh" to build/install ports concurrently?
- Does re-running iRedMail installer trigger this issue again?

4

Re: MariaDB 10.4 not creating iRedMail databases

ZhangHuangbin wrote:
rogwynn wrote:

pkg-static: Cannot get an exclusive lock on a database, it is locked by another process

- Did you modify "functions/packages_freebsd.sh" to build/install ports concurrently?
- Does re-running iRedMail installer trigger this issue again?

I didn't modify "functions/packages_freebsd.sh".

I didn't re-run the installer again, but today I ran a fresh install on a new jail from the master branch. The previous error "pkg-static: Cannot get an exclusive lock on a database, it is locked by another process" did not appear, but the MariaDB problem is still there.

[ INFO ] Configure MariaDB database server.
 + < DEBUG > Initialize MySQL server.
 + < DEBUG > Stop MySQL service before initializing database or updating my.cnf.
 + < DEBUG > Enable mysql service when system start up.
mysql_enable:  -> YES
mysql_optfile:  -> /var/db/mysql/my.cnf
 + < DEBUG > Copy sample MySQL config file: /usr/local/share/mysql/my-large.cnf
-> /var/db/mysql/my.cnf.
 + < DEBUG > Disable 'skip-networking' in my.cnf.
 + < DEBUG > Set max_connections to 1024.
 + < DEBUG > Enable 'skip_grant_tables' option, so that we can reset password.
 + < DEBUG > Enable 'bind-address = 192.168.0.3' in my.cnf.
 + < DEBUG > Restart service: mysql-server.
mysql not running? (check /var/db/mysql/mx.DOMAIN.com.pid).
2020-03-30 20:36:30 0 [Warning] WSREP: Failed to guess base node address. Set it
 explicitly via wsrep_node_address.
2020-03-30 20:36:30 0 [Warning] WSREP: Failed to guess base node address. Set it
 explicitly via wsrep_node_address.
2020-03-30 20:36:30 0 [Warning] WSREP: Guessing address for incoming client conn
ections failed. Try setting wsrep_node_incoming_address explicitly.
 + < DEBUG > Sleep 10 seconds for MySQL daemon initialization ...
 + < DEBUG > Setting password for MySQL root user: root.
ERROR 1348 (HY000) at line 1: Column 'authentication_string' is not updatable
 + < DEBUG > Remove 'skip_grant_tables'.
 + < DEBUG > Restart service: mysql-server.
 + < DEBUG > Sleep 10 seconds for MySQL daemon initialization ...
 + < DEBUG > Generate defauts file for MySQL client option --defaults-file: /roo
t/.my.cnf.
 + < DEBUG > Delete anonymous database user.

I decided to install MariaDB 10.4 in the jail before running the install script. I set up a user/password and used the remote MySQL instructions to install. The install fails because it tries to install MariaDB anyway. I checked the "functions/packages_freebsd.sh" script, and it shows this:

    if [ X"${BACKEND}" == X'OPENLDAP' ]; then
        ALL_PORTS="${ALL_PORTS} net/openldap${PREFERRED_OPENLDAP_VER}-sasl-client net/openldap${PREFERRED_OPENLDAP_VER}-server"
        ALL_PORTS="${ALL_PORTS} databases/mariadb${PREFERRED_MARIADB_VER}-server"
 
    elif [ X"${BACKEND}" == X'MYSQL' ]; then
        # Install client before server.
        ALL_PORTS="${ALL_PORTS} databases/mariadb${PREFERRED_MARIADB_VER}-client"

        if [ X"${USE_EXISTING_MYSQL}" != X'YES' ]; then
            ALL_PORTS="${ALL_PORTS} databases/mariadb${PREFERRED_MARIADB_VER}-server"   
        fi
    
    elif [ X"${BACKEND}" == X'PGSQL' ]; then
        ALL_PORTS="${ALL_PORTS} databases/postgresql${PGSQL_VERSION}-server databases/postgresql${PGSQL_VERSION}-contrib"
    fi

Since I'm using the OpenLDAP backend, it adds the MariaDB to the package list. USE_EXISTING_MYSQL=YES doesn't mean anything in this case. I changed the script to also check in the "X"${BACKEND}" == X'OPENLDAP' " block to check for USE_EXISTING_MYSQL:

    if [ X"${BACKEND}" == X'OPENLDAP' ]; then
        ALL_PORTS="${ALL_PORTS} net/openldap${PREFERRED_OPENLDAP_VER}-sasl-client net/openldap${PREFERRED_OPENLDAP_VER}-server"
        if [ X"${USE_EXISTING_MYSQL}" != X'YES' ]; then
            ALL_PORTS="${ALL_PORTS} databases/mariadb${PREFERRED_MARIADB_VER}-server"   
        fi
 
    elif [ X"${BACKEND}" == X'MYSQL' ]; then

5

Re: MariaDB 10.4 not creating iRedMail databases

I can not reproduce this issue with the latest iRedMail development edition. sad
Anyway to give me ssh access to try this deployment for debugging purpose?

6

Re: MariaDB 10.4 not creating iRedMail databases

Hi Zhang, I've mentioned this in the Telegram group but will post it here as well. I'm having similar issues as this on a fresh install on FreeBSD 12.2 amd64 using 1.4.0 MARIADB edition.

The installation went well and installed everything except the db is empty, similar to OP's. So is there a script or scripts to re-create the necessary databases again ?

7

Re: MariaDB 10.4 not creating iRedMail databases

GenZod wrote:

The installation went well and installed everything except the db is empty

Looks like MariaDB was not correctly setup and running, please check console output and file "runtime/install.log" for more details.

GenZod wrote:

So is there a script or scripts to re-create the necessary databases again ?

No.

8

Re: MariaDB 10.4 not creating iRedMail databases

I got the database up and running. However it's still empty after re-running the install script. Where are the .sql files stored so I can just it to re-create the missing databases ?

9

Re: MariaDB 10.4 not creating iRedMail databases

GenZod wrote:

I got the database up and running. However it's still empty after re-running the install script.

Looks like MariaDB was not correctly setup and running, please check console output and file "runtime/install.log" for more details.

We need to figure out why MariaDB was not running or not correctly set root password.