"groups" (8.0.2) and "function" (8.0.1) are reserved words since MySQL 8.0 (ZBXNEXT-4569)

[ZBXNEXT-4615] zabbix server fails after Upgrade from 4.0.0alpha6 to alpha8 Created: 2018 Jun 28  Updated: 2019 Mar 04  Resolved: 2019 Mar 04

Status: Closed
Project: ZABBIX FEATURE REQUESTS
Component/s: Server (S)
Affects Version/s: 4.0.0alpha6, 4.0.0alpha8
Fix Version/s: None

Type: Change Request (Sub-task) Priority: Critical
Reporter: Heðin Ejdesgaard Møller Assignee: Andris Zeila
Resolution: Workaround proposed Votes: 0
Labels: database
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

centos 7 installed alpha6 and upgraded to alpha8

Team: Team A
Sprint: Sprint 14, Sprint 15, Sprint 16, Sprint 32, Sprint 33, Sprint 34, Sprint 35, Sprint 36

 Description   
  1. Output from zabbix_server.log
    2121:20180628:142033.603 Starting Zabbix Server. Zabbix 4.0.0alpha8 (revision 81985).
    2121:20180628:142033.603 ****** Enabled features ******
    2121:20180628:142033.604 SNMP monitoring: YES
    2121:20180628:142033.604 IPMI monitoring: YES
    2121:20180628:142033.604 Web monitoring: YES
    2121:20180628:142033.604 VMware monitoring: YES
    2121:20180628:142033.604 SMTP authentication: YES
    2121:20180628:142033.604 Jabber notifications: YES
    2121:20180628:142033.604 Ez Texting notifications: YES
    2121:20180628:142033.604 ODBC: YES
    2121:20180628:142033.604 SSH2 support: YES
    2121:20180628:142033.604 IPv6 support: YES
    2121:20180628:142033.604 TLS support: YES
    2121:20180628:142033.604 ******************************
    2121:20180628:142033.604 using configuration file: /etc/zabbix/zabbix_server.conf
    2121:20180628:142033.611 current database version (mandatory/optional): 03050081/03050081
    2121:20180628:142033.611 required mandatory version: 03050120
    2121:20180628:142033.611 starting automatic database upgrade
    2121:20180628:142033.612 [Z3005] query failed: [1146] Table 'zabbix.groups' doesn't exist [alter table `groups` rename to `hstgrp`]
    2121:20180628:142033.612 database upgrade failed

Changes to hit the bug.

  1. # yum history info 23
    Loaded plugins: fastestmirror
    Transaction ID : 23
    Begin time     : Sat Jun 23 21:01:44 2018
    Begin rpmdb    : 431:896dc25577f4ac926205906ebb30f669117a377e
    End time       :            21:05:03 2018 (199 seconds)
    End rpmdb      : 432:87f312b2fa695293b459a96ecceac82631ffd77b
    User           : kt <kt>
    Return-Code    : Success
    Command Line   : update
    Transaction performed with:
        Installed     rpm-4.11.3-32.el7.x86_64                      @base
        Installed     yum-3.4.3-158.el7.centos.noarch               @base
        Installed     yum-metadata-parser-1.1.4-10.el7.x86_64       @anaconda
        Installed     yum-plugin-fastestmirror-1.1.31-45.el7.noarch @base
    Packages Altered:
        Updated MariaDB-client-10.3.6-1.el7.centos.x86_64            @mariadb
        Update                 10.3.7-1.el7.centos.x86_64            @mariadb
        Updated MariaDB-common-10.3.6-1.el7.centos.x86_64            @mariadb
        Update                 10.3.7-1.el7.centos.x86_64            @mariadb
        Updated MariaDB-compat-10.3.6-1.el7.centos.x86_64            @mariadb
        Update                 10.3.7-1.el7.centos.x86_64            @mariadb
        Updated MariaDB-server-10.3.6-1.el7.centos.x86_64            @mariadb
        Update                 10.3.7-1.el7.centos.x86_64            @mariadb
        Updated NetworkManager-1:1.10.2-13.el7.x86_64                @base
        Update                 1:1.10.2-14.el7_5.x86_64              @updates
        Updated NetworkManager-libnm-1:1.10.2-13.el7.x86_64          @base
        Update                       1:1.10.2-14.el7_5.x86_64        @updates
        Updated NetworkManager-team-1:1.10.2-13.el7.x86_64           @base
        Update                      1:1.10.2-14.el7_5.x86_64         @updates
        Updated NetworkManager-tui-1:1.10.2-13.el7.x86_64            @base
        Update                     1:1.10.2-14.el7_5.x86_64          @updates
        Updated NetworkManager-wifi-1:1.10.2-13.el7.x86_64           @base
        Update                      1:1.10.2-14.el7_5.x86_64         @updates
        Updated ca-certificates-2017.2.20-71.el7.noarch              @base
        Update                  2018.2.22-70.0.el7_5.noarch          @updates
        Updated centos-release-7-5.1804.el7.centos.x86_64            @base
        Update                 7-5.1804.el7.centos.2.x86_64          @updates
        Updated e2fsprogs-1.42.9-11.el7.x86_64                       @base
        Update            1.42.9-12.el7_5.x86_64                     @updates
        Updated e2fsprogs-libs-1.42.9-11.el7.x86_64                  @base
        Update                 1.42.9-12.el7_5.x86_64                @updates
        Updated htop-2.1.0-1.el7.x86_64                              @epel
        Update       2.2.0-1.el7.x86_64                              @epel
        Updated iptables-1.4.21-24.el7.x86_64                        @base
        Update           1.4.21-24.1.el7_5.x86_64                    @updates
        Updated iwl100-firmware-39.31.5.1-62.el7.noarch              @base
        Update                  39.31.5.1-62.2.el7_5.noarch          @updates
        Updated iwl1000-firmware-1:39.31.5.1-62.el7.noarch           @base
        Update                   1:39.31.5.1-62.2.el7_5.noarch       @updates
        Updated iwl105-firmware-18.168.6.1-62.el7.noarch             @base
        Update                  18.168.6.1-62.2.el7_5.noarch         @updates
        Updated iwl135-firmware-18.168.6.1-62.el7.noarch             @base
        Update                  18.168.6.1-62.2.el7_5.noarch         @updates
        Updated iwl2000-firmware-18.168.6.1-62.el7.noarch            @base
        Update                   18.168.6.1-62.2.el7_5.noarch        @updates
        Updated iwl2030-firmware-18.168.6.1-62.el7.noarch            @base
        Update                   18.168.6.1-62.2.el7_5.noarch        @updates
        Updated iwl3160-firmware-22.0.7.0-62.el7.noarch              @base
        Update                   22.0.7.0-62.2.el7_5.noarch          @updates
        Updated iwl3945-firmware-15.32.2.9-62.el7.noarch             @base
        Update                   15.32.2.9-62.2.el7_5.noarch         @updates
        Updated iwl4965-firmware-228.61.2.24-62.el7.noarch           @base
        Update                   228.61.2.24-62.2.el7_5.noarch       @updates
        Updated iwl5000-firmware-8.83.5.1_1-62.el7.noarch            @base
        Update                   8.83.5.1_1-62.2.el7_5.noarch        @updates
        Updated iwl5150-firmware-8.24.2.2-62.el7.noarch              @base
        Update                   8.24.2.2-62.2.el7_5.noarch          @updates
        Updated iwl6000-firmware-9.221.4.1-62.el7.noarch             @base
        Update                   9.221.4.1-62.2.el7_5.noarch         @updates
        Updated iwl6000g2a-firmware-17.168.5.3-62.el7.noarch         @base
        Update                      17.168.5.3-62.2.el7_5.noarch     @updates
        Updated iwl6000g2b-firmware-17.168.5.2-62.el7.noarch         @base
        Update                      17.168.5.2-62.2.el7_5.noarch     @updates
        Updated iwl6050-firmware-41.28.5.1-62.el7.noarch             @base
        Update                   41.28.5.1-62.2.el7_5.noarch         @updates
        Updated iwl7260-firmware-22.0.7.0-62.el7.noarch              @base
        Update                   22.0.7.0-62.2.el7_5.noarch          @updates
        Updated iwl7265-firmware-22.0.7.0-62.el7.noarch              @base
        Update                   22.0.7.0-62.2.el7_5.noarch          @updates
        Install kernel-3.10.0-862.3.3.el7.x86_64                     @updates
        Updated kernel-tools-3.10.0-862.2.3.el7.x86_64               @updates
        Update               3.10.0-862.3.3.el7.x86_64               @updates
        Updated kernel-tools-libs-3.10.0-862.2.3.el7.x86_64          @updates
        Update                    3.10.0-862.3.3.el7.x86_64          @updates
        Updated libcom_err-1.42.9-11.el7.x86_64                      @base
        Update             1.42.9-12.el7_5.x86_64                    @updates
        Updated libgcc-4.8.5-28.el7.x86_64                           @base
        Update         4.8.5-28.el7_5.1.x86_64                       @updates
        Updated libgomp-4.8.5-28.el7.x86_64                          @base
        Update          4.8.5-28.el7_5.1.x86_64                      @updates
        Updated libss-1.42.9-11.el7.x86_64                           @base
        Update        1.42.9-12.el7_5.x86_64                         @updates
        Updated libstdc++-4.8.5-28.el7.x86_64                        @base
        Update            4.8.5-28.el7_5.1.x86_64                    @updates
        Updated linux-firmware-20180220-62.git6d51311.el7.noarch     @base
        Update                 20180220-62.2.git6d51311.el7_5.noarch @updates
        Updated microcode_ctl-2:2.1-29.el7.x86_64                    @base
        Update                2:2.1-29.2.el7_5.x86_64                @updates
        Updated net-snmp-1:5.7.2-32.el7.x86_64                       @base
        Update           1:5.7.2-33.el7_5.2.x86_64                   @updates
        Updated net-snmp-agent-libs-1:5.7.2-32.el7.x86_64            @base
        Update                      1:5.7.2-33.el7_5.2.x86_64        @updates
        Updated net-snmp-libs-1:5.7.2-32.el7.x86_64                  @base
        Update                1:5.7.2-33.el7_5.2.x86_64              @updates
        Updated net-snmp-utils-1:5.7.2-32.el7.x86_64                 @base
        Update                 1:5.7.2-33.el7_5.2.x86_64             @updates
        Updated nspr-4.17.0-1.el7.x86_64                             @base
        Update       4.19.0-1.el7_5.x86_64                           @updates
        Updated nss-3.34.0-4.el7.x86_64                              @base
        Update      3.36.0-5.el7_5.x86_64                            @updates
        Updated nss-softokn-3.34.0-2.el7.x86_64                      @base
        Update              3.36.0-5.el7_5.x86_64                    @updates
        Updated nss-softokn-freebl-3.34.0-2.el7.x86_64               @base
        Update                     3.36.0-5.el7_5.x86_64             @updates
        Updated nss-sysinit-3.34.0-4.el7.x86_64                      @base
        Update              3.36.0-5.el7_5.x86_64                    @updates
        Updated nss-tools-3.34.0-4.el7.x86_64                        @base
        Update            3.36.0-5.el7_5.x86_64                      @updates
        Updated nss-util-3.34.0-2.el7.x86_64                         @base
        Update           3.36.0-1.el7_5.x86_64                       @updates
        Updated openldap-2.4.44-13.el7.x86_64                        @base
        Update           2.4.44-15.el7_5.x86_64                      @updates
        Updated openldap-clients-2.4.44-13.el7.x86_64                @base
        Update                   2.4.44-15.el7_5.x86_64              @updates
        Updated procps-ng-3.3.10-17.el7.x86_64                       @base
        Update            3.3.10-17.el7_5.2.x86_64                   @updates
        Updated python-perf-3.10.0-862.2.3.el7.x86_64                @updates
        Update              3.10.0-862.3.3.el7.x86_64                @updates
        Updated rsyslog-8.24.0-16.el7.x86_64                         @base
        Update          8.24.0-16.el7_5.4.x86_64                     @updates
        Updated zabbix-agent-4.0.0-1.1alpha6.el7.x86_64              @zabbix
        Update               4.0.0-1.1alpha8.el7.x86_64              @zabbix
        Updated zabbix-get-4.0.0-1.1alpha6.el7.x86_64                @zabbix
        Update             4.0.0-1.1alpha8.el7.x86_64                @zabbix
        Updated zabbix-server-mysql-4.0.0-1.1alpha6.el7.x86_64       @zabbix
        Update                      4.0.0-1.1alpha8.el7.x86_64       @zabbix
        Updated zabbix-web-4.0.0-1.1alpha6.el7.noarch                @zabbix
        Update             4.0.0-1.1alpha8.el7.noarch                @zabbix
        Updated zabbix-web-mysql-4.0.0-1.1alpha6.el7.noarch          @zabbix
        Update                   4.0.0-1.1alpha8.el7.noarch          @zabbix
    Scriptlet output:

 Comments   
Comment by Vladislavs Sokurenko [ 2018 Jun 28 ]

Thank you for your report, could you please check if someone has manually renamed groups table ? Do you have hstgrp table ?

Comment by Heðin Ejdesgaard Møller [ 2018 Jun 28 ]

It was not manually renamed.
The table name hstgrp was there.

I ran
rename table zabbix.hstgrp to groups;
and started the server again, now the database was correctly upgraded.

Old zabbix-server logs from when the upgrade went through:
22606:20180623:210318.055 Got signal [signal:15(SIGTERM),sender_pid:21699,sender_uid:0,reason:0]. Exiting ...
22606:20180623:210320.059 syncing history data...
22606:20180623:210320.077 syncing history data done
22606:20180623:210320.077 syncing trend data...
22606:20180623:210321.128 syncing trend data done
22606:20180623:210321.128 Zabbix Server stopped. Zabbix 4.0.0alpha6 (revision 80305).
21704:20180623:210321.194 Starting Zabbix Server. Zabbix 4.0.0alpha8 (revision 81985).
21704:20180623:210321.194 ****** Enabled features ******
21704:20180623:210321.194 SNMP monitoring: YES
21704:20180623:210321.194 IPMI monitoring: YES
21704:20180623:210321.194 Web monitoring: YES
21704:20180623:210321.194 VMware monitoring: YES
21704:20180623:210321.194 SMTP authentication: YES
21704:20180623:210321.194 Jabber notifications: YES
21704:20180623:210321.194 Ez Texting notifications: YES
21704:20180623:210321.194 ODBC: YES
21704:20180623:210321.194 SSH2 support: YES
21704:20180623:210321.194 IPv6 support: YES
21704:20180623:210321.194 TLS support: YES
21704:20180623:210321.194 ******************************
21704:20180623:210321.194 using configuration file: /etc/zabbix/zabbix_server.conf
21704:20180623:210321.238 current database version (mandatory/optional): 03050068/03050068
21704:20180623:210321.239 required mandatory version: 03050120
21704:20180623:210321.239 starting automatic database upgrade
21704:20180623:210321.242 completed 1% of database upgrade
21704:20180623:210321.254 completed 3% of database upgrade
21704:20180623:210321.256 completed 5% of database upgrade
21704:20180623:210321.259 completed 7% of database upgrade
21704:20180623:210321.262 completed 9% of database upgrade
21704:20180623:210321.268 completed 11% of database upgrade
21704:20180623:210321.287 completed 13% of database upgrade
21704:20180623:210321.290 completed 15% of database upgrade
21704:20180623:210321.297 completed 17% of database upgrade
21704:20180623:210321.299 completed 19% of database upgrade
21704:20180623:210321.301 completed 21% of database upgrade
21704:20180623:210321.302 completed 23% of database upgrade
21704:20180623:210321.305 completed 25% of database upgrade
21704:20180623:210321.401 [Z3005] query failed: [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "No such file or directory") [update dbversion set mandatory=3050082,optional=3050082]
21704:20180623:210321.401 [Z3001] connection to database 'zabbix' failed: [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "No such file or directory")
21704:20180623:210321.401 database is down: reconnecting in 10 seconds
21704:20180623:210331.405 database connection re-established
21704:20180623:210331.405 database upgrade failed
23397:20180623:210341.568 Starting Zabbix Server. Zabbix 4.0.0alpha8 (revision 81985).
23397:20180623:210341.568 ****** Enabled features ******
23397:20180623:210341.568 SNMP monitoring: YES
23397:20180623:210341.568 IPMI monitoring: YES
23397:20180623:210341.568 Web monitoring: YES
23397:20180623:210341.568 VMware monitoring: YES
23397:20180623:210341.568 SMTP authentication: YES
23397:20180623:210341.568 Jabber notifications: YES
23397:20180623:210341.568 Ez Texting notifications: YES
23397:20180623:210341.568 ODBC: YES
23397:20180623:210341.568 SSH2 support: YES
23397:20180623:210341.568 IPv6 support: YES
23397:20180623:210341.569 TLS support: YES
23397:20180623:210341.569 ******************************
23397:20180623:210341.569 using configuration file: /etc/zabbix/zabbix_server.conf
23397:20180623:210341.606 current database version (mandatory/optional): 03050081/03050081
23397:20180623:210341.607 required mandatory version: 03050120
23397:20180623:210341.607 starting automatic database upgrade
23397:20180623:210341.607 [Z3005] query failed: [1146] Table 'zabbix.groups' doesn't exist [alter table `groups` rename to `hstgrp`]
23397:20180623:210341.607 database upgrade failed

Comment by Vladislavs Sokurenko [ 2018 Jun 28 ]

Unfortunately queries such as rename table cannot be rolled back so if you lost connection to MySQL server, you should fix table manually. I am glad that it works now.

Generated at Wed Apr 24 18:19:44 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.