Upgrade to 3.1.7p1 - mysqld error (event scheduler)

Discussion in 'ISPConfig 3 Priority Support' started by tsapi, Oct 12, 2017.

  1. tsapi

    tsapi New Member HowtoForge Supporter

    Good evening.
    I was running ispconfig 3.1.6 on debian stable and upgraded today directly to 3.1.7p1 following the instructions here: https://www.ispconfig.org/blog/ispconfig-3-1-7p1-released/
    After rebooting I had a look in the logfile, where I see following:
    Code:
    Oct 12 18:25:29 isp mysqld[1027]: 2017-10-12 18:25:29 4144400384 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE',
    'IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_
    ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_C
    HAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS',
    'NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
    Oct 12 18:25:29 isp mysqld[1027]: 2017-10-12 18:25:29 4144400384 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
    Oct 12 18:25:29 isp mysqld[1027]: 2017-10-12 18:25:29 4144400384 [Note] /usr/sbin/mysqld: ready for connections.
    Oct 12 18:25:29 isp mysqld[1027]: Version: '10.1.26-MariaDB-0+deb9u1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Debian 9.1
    Oct 12 18:25:29 isp systemd[1]: Started MariaDB database server.
    Everything seems to be running fine.
    Should I worry aboute these errors? Can I do something to correct them?
    Thanks a lot in advance,
    tsapi
     
  2. ztk.me

    ztk.me ISPConfig Developer ISPConfig Developer

    have you checked the correct syntax in your /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf for
    Code:
    sql-mode="NO_ENGINE_SUBSTITUTION"
    
     
  3. tsapi

    tsapi New Member HowtoForge Supporter

    Thank you for your reply.
    Yes. The sql-mode="NO_ENGINE_SUBSTITUTION" is present in /etc/mysql/my.cnf - it is not there in /etc/mysql/mariadb.conf.d/50-server.cnf

    /etc/mysql/my.cnf :
    Code:
    ...
    [mysqld]
    # added manually
    sql-mode="NO_ENGINE_SUBSTITUTION"
    #
    # * Basic Settings
    ...
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    That should be ok as well. May you please try to comment it out and restart mysql, do you still get the same error message in the log?
     
  5. tsapi

    tsapi New Member HowtoForge Supporter

    I commented it out and syslog shows:
    Code:
    Oct 13 10:39:18 isp mysqld[20315]: 2017-10-13 10:39:18 4144068608 [Note] Server socket created on IP: '::'.
    Oct 13 10:39:18 isp mysqld[20315]: 2017-10-13 10:39:18 4144068608 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
    Oct 13 10:39:18 isp mysqld[20315]: 2017-10-13 10:39:18 4144068608 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
    
     
  6. ztk.me

    ztk.me ISPConfig Developer ISPConfig Developer

    uhm maybe ( make backup first ) there was an issue with the table definition really
    my mysql.event table looks like on debian stretch ( mariadb though )
    Code:
    CREATE TABLE `event` (
      `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
      `name` char(64) NOT NULL DEFAULT '',
      `body` longblob NOT NULL,
      `definer` char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
      `execute_at` datetime DEFAULT NULL,
      `interval_value` int(11) DEFAULT NULL,
      `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
      `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `last_executed` datetime DEFAULT NULL,
      `starts` datetime DEFAULT NULL,
      `ends` datetime DEFAULT NULL,
      `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
      `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
      `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
      `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
      `originator` int(10) unsigned NOT NULL,
      `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
      `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      `body_utf8` longblob,
      PRIMARY KEY (`db`,`name`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';
    
     
  7. tsapi

    tsapi New Member HowtoForge Supporter

    Hi,
    thanks a lot for your reply.
    I am running mariadb too..
    mariadb.jpg
    I am quite experienced in linux but have minimal knowledge and experience in mysql. I also attach a screenshot of phpmyadmin, so you can see the content of my mysql.event table (nothing, as far as I can understand). If you have any suggestions about how I should further troubleshoot or correct my problem, please give detailed instructions (as I am really inexperienced in mysql).
    phpmyadmin-event.jpg
    Thanks a lot!
     
  8. ztk.me

    ztk.me ISPConfig Developer ISPConfig Developer

    it would be more useful to see the actual structure of the fields.
    You can run SQL like
    Code:
    explain event;
    within mysql database and compare the output for the sql_mode tuple.
    should be something like that.
    Code:
    | sql_mode             | set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') | NO   |     |                     |                             |
    
    I'm on 10.1.26-MariaDB-0+deb9u1
     
  9. tsapi

    tsapi New Member HowtoForge Supporter

    Here is my output:
    Code:
    sql_mode
    set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')

    Me too:
    Code:
    Package: mariadb-server                
    Version: 10.1.26-0+deb9u1
    Thans a lot for your help..
     
  10. ztk.me

    ztk.me ISPConfig Developer ISPConfig Developer

    yeah the entries differ, for example the 5th position states "NOT_USED" in your output.
    something went wrong with your database-update, try either recreating the event-table from my post using the create-statement ( delete the old one first ) or install a test-machine and get a copy of it from there using export-function or mysqldump.
    maybe it's a good idea to check other mysql.* tables for differences aswell.
     

Share This Page