Problem updating ispconfig 3.1.4 to 3.1.5

OS: Debian 9 (upgrade 2 weeks ago from Debian 8)
ISPConfig: 3.1.4 (upgraded from 3.1.3 when OS was already on Debian 9)
MySQL: Maria-db 10.1

When i run the upgrade script i get error about wrong SQL-Mode after the backup phase:

Code:

--------------------------------------------------------------------------------
_____ ___________   _____              __ _         ____
|_   _/  ___| ___ \ /  __ \            / _(_)       /__  \
  | | \ `--.| |_/ / | /  \/ ___  _ __ | |_ _  __ _    _/ /
  | |  `--. \  __/  | |    / _ \| '_ \|  _| |/ _` |  |_ |
_| |_/\__/ / |     | \__/\ (_) | | | | | | | (_| | ___\ \
\___/\____/\_|      \____/\___/|_| |_|_| |_|\__, | \____/
                                              __/ |
                                             |___/
--------------------------------------------------------------------------------


>> Update

Operating System: Debian 9.0 (Stretch) or compatible

This application will update ISPConfig 3 on your server.

Shall the script create a ISPConfig backup in /var/backup/ now? (yes,no) [yes]:

Creating backup of "/usr/local/ispconfig" directory...
Creating backup of "/etc" directory...
Checking ISPConfig database .. OK
Wrong SQL-mode. You should use NO_ENGINE_SUBSTITUTION. Add

    sql-mode="NO_ENGINE_SUBSTITUTION"

to the mysqld-section in your mysql-config on this server and restart mysqld afterwards

I tried adding that line sql-mode="NO_ENGINE_SUBSTITUTION" to my.cnf first and restarted mysql but same message, next i read somewhere about adding it to mariadb.cnf so i did that and restarted mysql.. but still same problem.

 

To no success unfortunately ....
Edited /etc/mysql/mariadb.conf.d/50-server.cnf to reflect the change of the sql-mode, edited /etc/mysql/debian.cnf also to add the root password and even did the step to change to native password for phpmyadmin as those steps where not on the Debian 8 list.
Restarted MySQL, and the error on the update script for ISPConfig remains.

When i check the MySQL Variables it does show:

Code:

[...]
sql_log_bin    ON
sql_log_off    OFF
sql_mode    NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_notes    ON
sql_quote_show_create    ON
sql_safe_updates    OFF
[...]

 

Hmmm... at first sight it seemed the problem is the use of the Local and Global variable in MariaDB (only to discover that was not even the problem).
So the code in update.lib.php only queries the local value:

Code:

        //* check sql-mode
        $check_sql_mode = $inst->db->queryOneRecord("SELECT @@sql_mode");
        if ($check_sql_mode['@@sql_mode'] != '' && $check_sql_mode['@@sql_mode'] != 'NO_ENGINE_SUBSTITUTION') {
                echo "Wrong SQL-mode. You should use NO_ENGINE_SUBSTITUTION. Add\n\n";
                echo "    sql-mode=\"NO_ENGINE_SUBSTITUTION\"\n\n";
                echo"to the mysqld-section in your mysql-config on this server and restart mysqld afterwards\n";
                die();
        }

Meanwhile in this version of MariaDB (10.1) the sql-mode already has a default value of

Code:

NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

but apparently set in the Global variable.
So checking the variable value directly from MySQL shows me this:

Code:

MariaDB [(none)]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
+------------+--------------------------------------------+
| @@SQL_MODE | @@GLOBAL.SQL_MODE                          |
+------------+--------------------------------------------+
|            | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------+--------------------------------------------+
1 row in set (0.00 sec)

Setting the value in the appropriate config file does NOT change the local value for some reason.
So this time i changed it directly from the MySQL prompt

Code:

MariaDB [(none)]> SET sql_mode = 'NO_ENGINE_SUBSTITUTION'
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
+------------------------+--------------------------------------------+
| @@SQL_MODE             | @@GLOBAL.SQL_MODE                          |
+------------------------+--------------------------------------------+
| NO_ENGINE_SUBSTITUTION | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+--------------------------------------------+
1 row in set (0.00 sec)

Restarted MySQL and now it shows:

Code:

MariaDB [(none)]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
+--------------------------------------------+--------------------------------------------+
| @@SQL_MODE                                 | @@GLOBAL.SQL_MODE                          |
+--------------------------------------------+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)

Run the update script again.... and exactly the same error.

I guess the update php script is checking now for the exact value of 'NO_ENGINE_SUBSTITUTION' and not if it contains that value.....
For now i just disabled that part in the script (my php knowledge is not good enough to adjust it on the spot) and at least the update ran now successfully.