Ubuntu 18.04 LTS with MariaDB 10.1 and Apache

Apologies, I have made a mistake: I have tried upgrading to Zabbix 5.4, which is of course not an LTS version as there is no Zabbix 5.4 LTS

Second forum user confirms the solution mentioned above works for him

[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]

Cannot reproduce

Unsupported DB! MariaDB version is 50505 which is smaller than minimum of 100037

Confirmed

Closing as a dublicae of ZBX-19400

There are more reports of 

// Cannot change column 'scriptid': used in a foreign key constraint 'c_opcommand_2' [alter table opcommand modify `scriptid` bigint unsigned not null]

This issue wasn't created about the MariaDB version, so in my point of view not a duplicate.

I agree CyberMCM. The "cannot chance column....." error seems to be unique to this issue and does not appear with the other issue. I don't think this is a duplicate and I do think this is a bug. @Igor: there are so far 5 users that have experienced this specific error so it is something reproducible but the question is, how?

Another member has come forward and told us he has this exact issue and that the solution has worked for him. So that makes 6 people with this exact issue and 3 people for whom this solution worked. I will not push so if you don't want to take a look at this, that is fine, we have a solution but I request this issue to be reopened and the "is duplicate" to be removed

edit

Thank you Igor, much appreciated. If anyone on the support team needs more data I'll be happy to provide it, just give me a shout. Also, I have encountered someone in another forum that had this issue and he told me that the issue went away when he didn't upgrade from 5.0 to 5.4 directly but first upgraded 5.0 to 5.2 and then to 5.4. So maybe the bug can be found by comparing 5.4 to 5.2, specifically the database upgrade script?

Link: here see the comment from Алексей (Alexei in English translation) and Aperam Support (Aperam Support is me). It is a Russian forum though but some comments are in English

Hello,
Please note that this issue closed as duplicate of ZBX-19400

ZBX-19400 does not have the error

// Cannot change column 'scriptid': used in a foreign key constraint 'c_opcommand_2' [alter table opcommand modify `scriptid` bigint unsigned not null]

So in my opinion it is not a duplicate. The solution in our case is also not to update MariaDB but to drop the foreign key 'c_opcommand_2' mentioned in the above error, to then change the 'Null' option to 'No' on column 'scriptid' and to then add the foreign key again. But, we have a solution so as far as I'm concerned I have done my part

I don't see how this issue is a duplicate of ZBX-19400 at all, unless the database version detection results in different commands being executed with the upgrade that could result in this specific foreign key being an issue.

We experienced this exact same issue when we upgraded from Zabbix 5.2.4 to 5.5.0 with an Aurora MySQL 5.6.10a cluster that was behind a ProxySQL server that still reported 5.5.30 as its version.

Our Zabbix server was incrementally upgraded from like 4.4 -> 5.0 -> 5.2 -> 5.4

After we first experienced this foreign key issue and that Zabbix said the "MySQL version is 50530 which is smaller than minimum of 50728", we upgraded our Aurora MySQL to 5.7, but unfortunately it caps at 5.7.12, and ofcourse this did not fix the stuck upgrade issue for us.

Our solution was also to drop the foreign key and then start the Zabbix 5.4.0 server again, however we still need to recreate the foreign key.

How is this marked as a Duplicate? This is clearly a different error.

Right. Re-opening. This issue mentions incorrect DB version detection in the log file but this is not the cause of the upgrade patch

alter table opcommand modify `scriptid` bigint unsigned not null 

failure.

Could you attach your database schema ( without any data ) before the upgrade?

sorry, I can't provide db schema, was my home installation and backup from before upgrade was already overwritten

I have a checkpoint from before the upgrade, I'll see what I can do once I'm at work, should be possible to provide the schema. I'll provide feedback in a bit

Is the below what you need? This is a schema exported with mysqldump --no-data after restoring the checkpoint from before the upgrade. I am not well-versed with database management so it could be that I have exported it incorrectly, just tell me what the correct way is then and I'll try again

schema.sql

Just throwing my hat in the ring to report that I also am experiencing the same exact problem. I am trying to upgrade from 5.2 to 5.4 and getting the 'scriptid' error in the server logs.

My instance is running on Ubuntu 18.04 LTS with MySQL as the back end.

I'd love to provide the database schema as another data point but I have no idea how to pull that out.

If I got some instructions on what I can provide for further troubleshooting I'd be happy to.

Igor, I think Tom did something like this:

mysqldump -d zabbix > schema.sql

Got it, thanks Mark.

Attached.

schema.sql

Install Zabbix server at Ubuntu Server 20.04.2

1143:20210609:111428.161 Zabbix Server stopped. Zabbix 5.4.1 (revision ecb5b3378c).
3383:20210609:112238.776 Starting Zabbix Server. Zabbix 5.4.1 (revision ecb5b3378c).
3383:20210609:112238.776 using configuration file: /etc/zabbix/zabbix_server.conf
3383:20210609:112238.790 Unsupported DB! MariaDB version is 50505 which is smaller than minimum of 100037
3383:20210609:112238.792 current database version (mandatory/optional): 05040000/05040000
3383:20210609:112238.792 required mandatory version: 05040000

Vadim, do you also have the below error in zabbix_server.log? If not, this is probably a separate issue, I have linked that separate issue below

[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]

Link to separate issue

Hi! Same issue on my Zabbix host. Zabbix 5.4.1, Ubuntu 20.04, MariaDB 10.5.10

On Zabbix dashboard: MariaDB 5.05.05 Minimum required database version is 10.00.37.

Zabbix_server.log: Unsupported DB! MariaDB version is 50505 which is smaller than minimum of 100037.

No errors in mysql error log.

I guess it would be fixed in next 5.4.2 release? Thank you!

@Dimitry Kolosov: This issue isn't about the version mismatch, this is covered here

Hi there! Is there a fix for this in the works?
We could drop FK constraint 'c_opcommand_2' and re-add it after migration, but that seems like an ugly workaround to me.

Experiencing the same on Zabbix 5.2.6 -> 5.4.1 upgrade:

• Debian Buster 10.10
• Percona Server for MySQL 8.0: 8.0.23-14 Percona Server (GPL), Release '14', Revision '3558242'

/var/log/zabbix/zabbix_proxy.log:

Starting Zabbix Proxy (passive) [Zabbix-Proxy]. Zabbix 5.4.1 (revision ecb5b3378c).
**** Enabled features ****
SNMP monitoring:       YES
IPMI monitoring:       YES
Web monitoring:        YES
VMware monitoring:     YES
ODBC:                  YES
SSH support:           YES
IPv6 support:          YES
TLS support:           YES
**************************
using configuration file: /etc/zabbix/zabbix_proxy.conf
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

zabbix.opcommand schema dump:

$ mysqldump -d zabbix opcommand

CREATE TABLE `opcommand` (
  `operationid` bigint unsigned NOT NULL,
  `type` int NOT NULL DEFAULT '0',
  `scriptid` bigint unsigned DEFAULT NULL,
  `execute_on` int NOT NULL DEFAULT '0',
  `port` varchar(64) COLLATE utf8_bin NOT NULL,
  `authtype` int NOT NULL DEFAULT '0',
  `username` varchar(64) COLLATE utf8_bin NOT NULL,
  `password` varchar(64) COLLATE utf8_bin NOT NULL,
  `publickey` varchar(64) COLLATE utf8_bin NOT NULL,
  `privatekey` varchar(64) COLLATE utf8_bin NOT NULL,
  `command` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`operationid`),
  KEY `opcommand_1` (`scriptid`),
  CONSTRAINT `c_opcommand_1` FOREIGN KEY (`operationid`) REFERENCES `operations` (`operationid`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `c_opcommand_2` FOREIGN KEY (`scriptid`) REFERENCES `scripts` (`scriptid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

$ mysql zabbix -e 'show columns from zabbix.opcommand'
+-------------+-----------------+------+-----+---------+-------+
| Field       | Type            | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+---------+-------+
| operationid | bigint unsigned | NO   | PRI | NULL    |       |
| type        | int             | NO   |     | 0       |       |
| scriptid    | bigint unsigned | YES  | MUL | NULL    |       |
| execute_on  | int             | NO   |     | 0       |       |
| port        | varchar(64)     | NO   |     | NULL    |       |
| authtype    | int             | NO   |     | 0       |       |
| username    | varchar(64)     | NO   |     | NULL    |       |
| password    | varchar(64)     | NO   |     | NULL    |       |
| publickey   | varchar(64)     | NO   |     | NULL    |       |
| privatekey  | varchar(64)     | NO   |     | NULL    |       |
| command     | text            | NO   |     | NULL    |       |
+-------------+-----------------+------+-----+---------+-------+

any help greatly appreciated! Thanks!

this workaround worked fine for me:

1. Drop FK constraint

ALTER TABLE `zabbix`.`opcommand` DROP FOREIGN KEY `c_opcommand_2`;

2. Start zabbix-proxy (or zabbix-server)

$ systemctl start zabbix-proxy

DB migrations then went through fine. Wait a while until you complete step 3, check /var/log/zabbix/zabbix_proxy.log (or /var/log/zabbix/zabbix_server.log) and look for the following lines:

completed 100% of database upgrade
database upgrade fully completed

3. Re-add FK constraint

ALTER TABLE `zabbix`.`opcommand` ADD CONSTRAINT `c_opcommand_2` FOREIGN KEY (`scriptid`) REFERENCES `scripts` (`scriptid`) ON DELETE RESTRICT ON UPDATE RESTRICT;

Philip,

Did you perform these steps after upgrading to 5.4.2 or before running the upgrade?

Hi @Dobrosavljevic,

Did you perform these steps after upgrading to 5.4.2 or before running the upgrade?

On a zabbix-proxy instance, I performed this step (dropping FK constraint 'c_opcommand_2') AFTER upgrading to Zabbix 5.4.1, but BEFORE starting zabbix-proxy (as the Systemd service did not start after failed upgrade because of this issue).

On a zabbix-server instance, I performed this step (dropping FK constraint 'c_opcommand_2') BEFORE upgrading to Zabbix 5.4.1. The whole upgrade went through fine and I was able to re-add the FK constraint afterwards.

Basically the same effect, as Zabbix db migrations are not part of package upgrade, but only happen on first start of Zabbix-server/-proxy, so that issue did not break the upgrade itself.
 
And AFAIK, Zabbix 5.4.2 is not out yet, currently still on 5.4.2rc2. So, I have upgraded from official deb package repo from 5.2.6 to 5.4.1

Thank you.

Can confirm this on alpine docker images as well. Upgrade from alpine-5.0-latest to alpine-5.4-latest.

Sql script from db architect solved the issue.

We are planing to upgrade from 5.2.7 to 5.4.7 this Friday. I'm quite positive this will be a success, but this issue here, gives me a bad feeling.

Why isn't this being worked on, with Zabbix 5.4 being available for 6 months now?

Any news about how I can handle this?

Marco, see my comment above, this is the only thing you need to do BEFORE upgrading:

ALTER TABLE `zabbix`.`opcommand` DROP FOREIGN KEY `c_opcommand_2`;

and right AFTER upgrading, re-add this FK constraint:

ALTER TABLE `zabbix`.`opcommand` ADD CONSTRAINT `c_opcommand_2` FOREIGN KEY (`scriptid`) REFERENCES `scripts` (`scriptid`) ON DELETE RESTRICT ON UPDATE RESTRICT;

But... I would strongly advice NOT to upgrade to Zabbix 5.4! Aside this issue, I struggled with a lot of other regressions after upgrading from Zabbix 5.2 to 5.4 (e.g. ZBX-19481 was reintroduced and only fixed in 5.4.3). Seems like they didn't quite put enough of love into this release. According to the roadmap Zabbix 6.0 LTS is planned for Q4 2021. Rather wait for that release and maybe some first minor version. An LTS version must be taken more serious by the Zabbix devs and issues must be fixed faster, I hope.

I told myself to stick only with LTS releases in the future.

onlime Thanks for your reply. I will make a test run before Friday, and clone the VM.

Regarding your warning, yes 5.4 seems of less quality than previous releases, that's the reason we waited until 5.4.7 with the upgrade, as I have the strong feeling, that the most important bugs are fixed by now.

Regarding the upcoming LTS, we are at 6.0 alpha5 now, and the stable will be GA around Zabbix summit'21 I guess, so maybe we won't stick with 5.4 for so long.
But as Zabbix 5.2 is unsupported for quite some time now, and 5.4 has fixed quite a few bugs that were present in 5.2 but never fixed, I feel quite the need to switch.

Hi,

Got exactly this error from 5.0.20 to 6.0.3. Do we have any updates ?

We are waiting to update our clients from 5 LTS to 6 LTS.

According to this bug report for MySQL it is not allowed to alter the table when the sql_mode is not strict. This is confirmed in the MySQL documentation here. In case of MariaDB I was unable to reproduce the issue.

DieWeb21 and BlueBull can You please provide me the output of the following SQL command:

SELECT @@sql_mode; 

The configuration files /etc/mysql/my.cnf or /etc/mysql/mariadb.cnf can contain the setting for sql_mode under section [mysqld].  As a workaround you can also execute the following sql command as a root user before updating Zabbix database:

SET GLOBAL sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Hi,

My sql_mode is empty.
sql_mode = "" in my /etc/my.cnf file.

Woops, forgot to reply. Output of command is as follows:

+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

The workaround I will try soon in my test environment

DieWeb21 , BlueBull well, it seems that what you have set to sql_mode is causing the issue. The sql_mode should be outputting by default:

"STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

You can try to do one of the following, set sql_mode for all the sessions and upgrade like I did in the comment above, or (should be more reliable) configure your mysql to have the sql_mode set to the value mentioned above.

I have not set anything, the database is running using the settings provided by the zabbix documentation, without exception. If that setting is wrong, it means that it has changed automatically, not manually. My point with saying this is that I suspect there are particular circumstances where that settings changes to the wrong one. It might for instance be in specific versions of MariaDB that this setting is different or something, or because of another reason. I will try out the fix as soon as possible but this might take some time

[kprutkovs] It is not relevant whether it changed automatically or not. A command altering the column in question cannot succeed if the STRICT_TRANS_TABLES is not specified in the sql_mode as it is a limitation of MySql. The sql_mode needs to be adjusted in order to upgrade if the setting is not specified already.

WON'T FIX as it is setup issue and needs documenting

Documentation updated:

• Known issues (5.4, 6.0, 6.4, 7.0)