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

    • 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), Sprint 83 (Dec 2021), Sprint 84 (Jan 2022), Sprint 85 (Feb 2022), Sprint 86 (Mar 2022), Sprint 87 (Apr 2022), Sprint 88 (May 2022), Sprint 89 (Jun 2022), Sprint 90 (Jul 2022), Sprint 91 (Aug 2022), Sprint 92 (Sep 2022), Sprint 93 (Oct 2022), Sprint 94 (Nov 2022), Sprint 95 (Dec 2022), Sprint 96 (Jan 2023), Sprint 97 (Feb 2023), Sprint 98 (Mar 2023), Sprint 99 (Apr 2023), Sprint 100 (May 2023), Sprint 101 (Jun 2023), Sprint 102 (Jul 2023), Sprint 103 (Aug 2023), Sprint 104 (Sep 2023), Sprint 105 (Oct 2023), Sprint 106 (Nov 2023)
    • 0.5

       

      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

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

            martins-v Martins Valkovskis
            BlueBull Tom Plessers
            Team C
            Votes:
            9 Vote for this issue
            Watchers:
            17 Start watching this issue

              Created:
              Updated:
              Resolved: