Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-19435

Upgrade to 5.4 causes failure "Cannot change column 'scriptid'"

    XMLWordPrintable

Details

    • Team C
    • Sprint 76 (May 2021), Sprint 77 (Jun 2021), Sprint 78 (Jul 2021), Sprint 79 (Aug 2021), Sprint 80 (Sep 2021), Sprint 81 (Oct 2021), Sprint 82 (Nov 2021)
    • 0.5

    Description

       

      There are multiple people experiencing this bug, there are 2 threads in the forum with at least 4 people experiencing it, across multiple Debian-based operating systems (Ubuntu and Raspbian). See links below to forum threads. Possible solution has also been found, see below or in the forum threads

      Steps to reproduce:

      1. Follow upgrade procedure to upgrade from Zabbix 5.0 LTS to 5.4
      2. Upgrade result = all OK, all components updated correctly
      3. Web interface shows
        "The Zabbix database version does not match current requirements. Your database version: 5030069. Required version: 5040000. Please contact your system administrator."
      1. Checking the zabbix_server.log gives the following
      Unsupported DB! MariaDB version is 50505 which is smaller than minimum of 100037
      current database version (mandatory/optional): 05030069/05030069
      required mandatory version: 05040000
      starting automatic database upgrade
      [Z3005] query failed: [1832] Cannot change column 'scriptid': used in a foreign key constraint 'c_opcommand_2' [alter table opcommand modify `scriptid` bigint unsigned not null]
      database upgrade failed

      Additional information
      Someone suggested to run "show columns from zabbix.opcommand;" which has the following result for someone experiencing this issue

      MariaDB [(none)]> show columns from zabbix.opcommand;
      +-------------+---------------------+------+-----+---------+-------+
      | Field       | Type                | Null | Key | Default | Extra |
      +-------------+---------------------+------+-----+---------+-------+
      | operationid | bigint(20) unsigned | NO   | PRI | NULL    |       |
      | scriptid    | bigint(20) unsigned | YES  | MUL | NULL    |       |
      +-------------+---------------------+------+-----+---------+-------+

      And should show the following for someone not experiencing this issue

      MariaDB [(none)]> show columns from zabbix.opcommand;
      +-------------+---------------------+------+-----+---------+-------+
      | Field       | Type                | Null | Key | Default | Extra |
      +-------------+---------------------+------+-----+---------+-------+
      | operationid | bigint(20) unsigned | NO   | PRI | NULL    |       |
      | scriptid    | bigint(20) unsigned | NO   | MUL | NULL    |       |
      +-------------+---------------------+------+-----+---------+-------+
      

      So the 'Null' column is different, for someone experiencing this issue it is 'null allowed' while for someone not experiencing this issue it is 'null not allowed'

      Our database architect suggested that the database upgrade script doesn't drop foreign key 'c_opcommand_2' before editing the column and that they need to be dropped and re-added. This is confirmed so far by one user who manually dropped the foreign key using the following procedure, after which the problem was solved for him. I am going to try this on Tuesday and suspect it will solve my problem as well

      ALTER TABLE zabbix.opcommand DROP FOREIGN KEY c_opcommand_2;
      ALTER TABLE `opcommand` CHANGE `scriptid` `scriptid` BIGINT(20) UNSIGNED NOT NULL;
      ALTER TABLE `opcommand` ADD CONSTRAINT `c_opcommand_2` FOREIGN KEY (`scriptid`) REFERENCES `scripts`(`scriptid`) ON DELETE RESTRICT ON UPDATE RESTRICT;

      So this is almost certainly a bug with the database update script and because it happened to 4 different people at least across 2 different operating systems, more people will run into this without a doubt. If you need more information don't hesitate to contact me on my email

      **

       

      Links to forum threads discussing this
      First thread
      Second thread

      Attachments

        1. image-2021-06-09-14-36-32-387.png
          image-2021-06-09-14-36-32-387.png
          27 kB
        2. schema.sql
          159 kB
        3. schema-1.sql
          163 kB

        Issue Links

          Activity

            People

              andris Andris Mednis
              BlueBull Tom Plessers
              Votes:
              6 Vote for this issue
              Watchers:
              21 Start watching this issue

              Dates

                Created:
                Updated: