Problem updating ispconfig 3.1.4 to 3.1.5

Discussion in 'Installation/Configuration' started by Stephan Ververda, Jun 30, 2017.

  1. Stephan Ververda

    Stephan Ververda New Member

    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.
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

  3. Stephan Ververda

    Stephan Ververda New Member

    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
    [...]
     
  4. Stephan Ververda

    Stephan Ververda New Member

    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.
     
    Last edited: Jul 2, 2017
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    +
    Yes, that's right. It has to be the exact value.
     

Share This Page