Upgrade DB 3.4 > 4.2 Column 'acknowledged' cannot be null

[mbuyukkarakas]

Dear all,
We have encountered that problem during the upgrade procedure. We are using a partitioned mysql db.
Could you help us please ?

Thank you. Best regards


30510:20190524:061123.998 using configuration file: /etc/zabbix/zabbix_server.conf
30510:20190524:061124.003 current database version (mandatory/optional): 03050116/03050116
30510:20190524:061124.003 required mandatory version: 04020000
30510:20190524:061124.003 starting automatic database upgrade
30510:20190524:061124.003 [Z3005] query failed: [1048] Column 'acknowledged' cannot be null [update problem set acknowledged=(select acknowledged from events where events.eventid=problem.eventid)]
30510:20190524:061124.003 database upgrade failed

---------------------------------------------------------------------------------

mysql> show create table problem\G

*************************** 1. row ***************************
Table: problem
Create Table: CREATE TABLE `problem` (
`eventid` bigint(20) unsigned NOT NULL,
`source` int(11) NOT NULL DEFAULT '0',
`object` int(11) NOT NULL DEFAULT '0',
`objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
`clock` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
`r_eventid` bigint(20) unsigned DEFAULT NULL,
`r_clock` int(11) NOT NULL DEFAULT '0',
`r_ns` int(11) NOT NULL DEFAULT '0',
`correlationid` bigint(20) unsigned DEFAULT NULL,
`userid` bigint(20) unsigned DEFAULT NULL,
`name` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`acknowledged` int(11) NOT NULL DEFAULT '0',
`severity` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`eventid`),
KEY `problem_1` (`source`,`object`,`objectid`),
KEY `problem_2` (`r_clock`),
KEY `problem_3` (`r_eventid`),
CONSTRAINT `c_problem_2` FOREIGN KEY (`r_eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)


mysql> show create table events\G;

*************************** 1. row ***************************
Table: events
Create Table: CREATE TABLE `events` (
`eventid` bigint(20) unsigned NOT NULL,
`source` int(11) NOT NULL DEFAULT '0',
`object` int(11) NOT NULL DEFAULT '0',
`objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
`clock` int(11) NOT NULL DEFAULT '0',
`value` int(11) NOT NULL DEFAULT '0',
`acknowledged` int(11) NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
`name` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`severity` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`eventid`),
KEY `events_1` (`source`,`object`,`objectid`,`clock`),
KEY `events_2` (`source`,`object`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

ERROR:
No query specified



----------------------------

Edit :

I found nothing can cause this problem


mysql> select acknowledged from events where acknowledged != 0;
Empty set (30.85 sec)

mysql> select acknowledged from problem where acknowledged != 0;
Empty set (2 min 20.71 sec)

mysql> select acknowledged from problem where acknowledged is NULL;
Empty set (0.01 sec)

mysql> select acknowledged from events where acknowledged is NULL;
Empty set (0.00 sec)

[mbuyukkarakas]

Solved but unfortunately I was in rush and I had to delete all my problem records ;
If anybody can update this post with a real solution ; it will help all of us in the future.

thank you.

mysql> alter table events rename events_old;
ERROR 1050 (42S01): Table 'events_old' already exists
mysql> alter table events rename events_eski;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table events_new rename events;
Query OK, 0 rows affected (0.00 sec)

mysql> create table problem_new like problem;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table problem rename problem_eski;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table problem_new rename problem;
Query OK, 0 rows affected (0.00 sec)

#systemctl restart zabbix-server

[Sebatian]

I was able to solve this problem by allowing NULL for the 'acknowledge' fields:

ALTER TABLE `events` CHANGE COLUMN `acknowledged` `acknowledged` INT(11) NULL DEFAULT '0' AFTER `value`;
ALTER TABLE `problem` CHANGE COLUMN `acknowledged` `acknowledged` INT(11) NULL DEFAULT '0' AFTER `name`;

after settings this, my upgrade just continued.

however I also had no "NULL" values in those rows. Neither before or after upgrade.
Upgraded from 3.4 to 4.2 from respository.

Cheers
Seb