Zabbix 3.2 upgrade failed

[stav13]

Hi,

I have just attempted to upgrade to 3.2 from 3.0.4 but I'm getting the below;

18686:20160921:180019.330 current database version (mandatory/optional): 03000000/03000000
18686:20160921:180019.330 required mandatory version: 03020000
18686:20160921:180019.330 starting automatic database upgrade
18686:20160921:180019.330 [Z3005] query failed: [1091] Can't DROP 'history_log_2'; check that column/key exists [drop index history_log_2 on history_log]
18686:20160921:180019.330 database upgrade failed

Im assuming this is because I partition my DB ?

I would assume i could dump, restore the DB without the partitions then perform the upgrade and then re-partition however im very unsure how to restore the DB without partitioning or even if its possible? This system was soon to go live so i only need to configuration part, dont need any historical data if that helps.

Thanks + a warning to other users who might want to upgrade!

[asteroidyorkton]

Are you partitioning history_log table as well? Idk if that's the cause, but just asking.

Anyway a work around is to first check if you have history_log_2 indexes in history_log table, according to that error, it says it doesn't exist.
Which means you can create it and run the upgrade and zabbix will drop it. if its needed, then zabbix will create it after it drops and finishes its job. You just make sure it exists for zabbix to delete it :-)

CREATE TABLE `history_log` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`itemid` BIGINT(20) UNSIGNED NOT NULL,
`clock` INT(11) NOT NULL DEFAULT '0',
`timestamp` INT(11) NOT NULL DEFAULT '0',
`source` VARCHAR(64) NOT NULL DEFAULT '' COLLATE 'utf8_bin',
`severity` INT(11) NOT NULL DEFAULT '0',
`value` TEXT NOT NULL COLLATE 'utf8_bin',
`logeventid` INT(11) NOT NULL DEFAULT '0',
`ns` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE INDEX `history_log_2` (`itemid`, `id`),
INDEX `history_log_1` (`itemid`, `clock`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

[glebs.ivanovskis]

Probably ZBX-11203 is your problem.

[stav13]

Are you partitioning history_log table as well? Idk if that's the cause, but just asking.

Anyway a work around is to first check if you have history_log_2 indexes in history_log table, according to that error, it says it doesn't exist.
Which means you can create it and run the upgrade and zabbix will drop it. if its needed, then zabbix will create it after it drops and finishes its job. You just make sure it exists for zabbix to delete it :-)

CREATE TABLE `history_log` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`itemid` BIGINT(20) UNSIGNED NOT NULL,
`clock` INT(11) NOT NULL DEFAULT '0',
`timestamp` INT(11) NOT NULL DEFAULT '0',
`source` VARCHAR(64) NOT NULL DEFAULT '' COLLATE 'utf8_bin',
`severity` INT(11) NOT NULL DEFAULT '0',
`value` TEXT NOT NULL COLLATE 'utf8_bin',
`logeventid` INT(11) NOT NULL DEFAULT '0',
`ns` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE INDEX `history_log_2` (`itemid`, `id`),
INDEX `history_log_1` (`itemid`, `clock`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

Thanks Leftlanef4, i think you are right however your command wants to create a history_log table which already exists and errors out as below;

MariaDB [zabbix]> show index from history_log
-> ;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--- -----+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Pa cked | Null | Index_type | Comment | Index_comment |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--- -----+------+------------+---------+---------------+
| history_log | 1 | history_log_1 | 1 | itemid | A | 39 | NULL | NU LL | | BTREE | | |
| history_log | 1 | history_log_1 | 2 | clock | A | 39 | NULL | NU LL | | BTREE | | |
| history_log | 1 | history_log_0 | 1 | id | A | 39 | NULL | NU LL | | BTREE | | |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--- -----+------+------------+---------+---------------+
3 rows in set (0.01 sec)

MariaDB [zabbix]> CREATE TABLE `history_log` (
-> `id` BIGINT(20) UNSIGNED NOT NULL,
-> `itemid` BIGINT(20) UNSIGNED NOT NULL,
-> `clock` INT(11) NOT NULL DEFAULT '0',
-> `timestamp` INT(11) NOT NULL DEFAULT '0',
-> `source` VARCHAR(64) NOT NULL DEFAULT '' COLLATE 'utf8_bin',
-> `severity` INT(11) NOT NULL DEFAULT '0',
-> `value` TEXT NOT NULL COLLATE 'utf8_bin',
-> `logeventid` INT(11) NOT NULL DEFAULT '0',
-> `ns` INT(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`),
-> UNIQUE INDEX `history_log_2` (`itemid`, `id`),
-> INDEX `history_log_1` (`itemid`, `clock`)
-> )
-> COLLATE='utf8_bin'
-> ENGINE=InnoDB
-> ;
ERROR 1050 (42S01): Table 'history_log' already exists
MariaDB [zabbix]> show index from history_log;
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| history_log | 1 | history_log_1 | 1 | itemid | A | 39 | NULL | NULL | | BTREE | | |
| history_log | 1 | history_log_1 | 2 | clock | A | 39 | NULL | NULL | | BTREE | | |
| history_log | 1 | history_log_0 | 1 | id | A | 39 | NULL | NULL | | BTREE | | |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MariaDB [zabbix]>

Thanks

[stav13]

Got round the issue by dropping the table and rebuilding using your commands, also had to do the same for the history_text table using the below commands

DROP TABLE history_log;
DROP TABLE history_text;

CREATE TABLE `history_log` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`itemid` BIGINT(20) UNSIGNED NOT NULL,
`clock` INT(11) NOT NULL DEFAULT '0',
`timestamp` INT(11) NOT NULL DEFAULT '0',
`source` VARCHAR(64) NOT NULL DEFAULT '' COLLATE 'utf8_bin',
`severity` INT(11) NOT NULL DEFAULT '0',
`value` TEXT NOT NULL COLLATE 'utf8_bin',
`logeventid` INT(11) NOT NULL DEFAULT '0',
`ns` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE INDEX `history_log_2` (`itemid`, `id`),
INDEX `history_log_1` (`itemid`, `clock`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;


CREATE TABLE `history_text` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`itemid` BIGINT(20) UNSIGNED NOT NULL,
`clock` INT(11) NOT NULL DEFAULT '0',
`timestamp` INT(11) NOT NULL DEFAULT '0',
`source` VARCHAR(64) NOT NULL DEFAULT '' COLLATE 'utf8_bin',
`severity` INT(11) NOT NULL DEFAULT '0',
`value` TEXT NOT NULL COLLATE 'utf8_bin',
`logeventid` INT(11) NOT NULL DEFAULT '0',
`ns` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE INDEX `history_text_2` (`itemid`, `id`),
INDEX `history_text_1` (`itemid`, `clock`)
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

[manuelasousa]

1°Access mysql:
mysql-u root
2ºChoice DB
use zabbix
3ºAdd key primary in the table history_log
alter table history_log add primary key(itemid);
4ºDelete table entries history_text
-delete from history_text where itemid;
5ºAdd key primary in the table history_text
alter table history_text add primary key(itemid);

6ºStart zabbix-server;
systemctl start zabbix-server.service

View log zabbix;

13502:20170125:175200.697 current database version (mandatory/optional): 03010020/03010020
13502:20170125:175200.697 required mandatory version: 03020000
13502:20170125:175200.697 starting automatic database upgrade
13502:20170125:175218.289 completed 1% of database upgrade
...
13502:20170125:175218.463 Action "Report problems to Zabbix administrators" condition "Trigger value = PROBLEM" will be removed during database upgrade: this type of condition is not supported anymore
13502:20170125:175218.472 completed 10% of database upgrade
...
13502:20170125:175226.066 completed 37% of database upgrade
13502:20170125:175235.075 slow query: 9.009124 sec, "alter table event_recovery add c_eventid bigint unsigned"
13502:20170125:175235.078 completed 39% of database upgrade
13502:20170125:175243.000 slow query: 7.921798 sec, "alter table event_recovery add correlationid bigint unsigned"
13502:20170125:175243.003 completed 41% of database upgrade
13502:20170125:175245.052 completed 43% of database upgrade
13502:20170125:175301.455 slow query: 16.403086 sec, "alter table event_recovery add constraint c_event_recovery_3 foreign key (c_eventid) references events (eventid) on delete cascade"
13502:20170125:175301.458 completed 44% of database upgrade
...
13502:20170125:175302.680 completed 87% of database upgrade
13502:20170125:175313.187 slow query: 10.506919 sec, "alter table event_recovery add userid bigint unsigned"
13502:20170125:175313.190 completed 89% of database upgrade
...
13502:20170125:175318.925 completed 100% of database upgrade
13502:20170125:175318.925 database upgrade fully completed

[jukka]

Upgrade also failing to db error for me.

I've tried many things I found online but nothing helps. I always endup with database problems.
env;
centos 7, mysql database (mysql or mariadb, both tried), zabbix upgrade from 2.4 to 3.0 or 3.2
quite large enterprise setup
procedure;
- dump zabbix database from old env (centos 6) zabbix 2.4
- restore old database to centos 7 running mysql and zabbix 2.4
- all good, up and running fine
- shutdown zabbix-server
- upgade binaries to 3.0 or 3.2
- start manually to prevent sigterm
- result;

19318:20170210:141219.255 Starting Zabbix Server. Zabbix 3.0.7 (revision 64609).
19318:20170210:141219.256 ****** Enabled features ******
19318:20170210:141219.256 SNMP monitoring: YES
19318:20170210:141219.256 IPMI monitoring: YES
19318:20170210:141219.256 Web monitoring: YES
19318:20170210:141219.256 VMware monitoring: YES
19318:20170210:141219.256 SMTP authentication: YES
19318:20170210:141219.256 Jabber notifications: YES
19318:20170210:141219.256 Ez Texting notifications: YES
19318:20170210:141219.256 ODBC: YES
19318:20170210:141219.256 SSH2 support: YES
19318:20170210:141219.256 IPv6 support: YES
19318:20170210:141219.256 TLS support: YES
19318:20170210:141219.257 ******************************
19318:20170210:141219.257 using configuration file: /etc/zabbix/zabbix_server.conf
19318:20170210:141219.285 current database version (mandatory/optional): 02040000/02040000
19318:20170210:141219.285 required mandatory version: 03000000
19318:20170210:141219.285 starting automatic database upgrade
19318:20170210:141219.297 completed 0% of database upgrade
19318:20170210:141219.970 completed 1% of database upgrade
19318:20170210:141219.993 completed 2% of database upgrade
19318:20170210:141220.013 completed 3% of database upgrade
19318:20170210:141220.031 completed 4% of database upgrade
19318:20170210:141220.039 completed 5% of database upgrade
19318:20170210:141220.097 completed 6% of database upgrade
19318:20170210:141220.102 completed 7% of database upgrade
19318:20170210:141220.110 completed 8% of database upgrade
19318:20170210:141220.115 completed 9% of database upgrade
19318:20170210:141220.496 completed 10% of database upgrade
19318:20170210:141220.530 completed 11% of database upgrade
19318:20170210:141220.545 completed 12% of database upgrade
19318:20170210:141220.547 completed 13% of database upgrade
19318:20170210:141220.549 completed 14% of database upgrade
19318:20170210:141220.605 completed 15% of database upgrade
19318:20170210:141220.652 completed 16% of database upgrade
19318:20170210:141220.698 completed 17% of database upgrade
19318:20170210:141220.744 completed 18% of database upgrade
19318:20170210:141220.794 completed 19% of database upgrade
19318:20170210:141220.795 [Z3005] query failed: [1050] Table 'application_prototype' already exists [create table application_prototype (
application_prototypeid bigint unsigned not null,
itemid bigint unsigned not null,
templateid bigint unsigned,
name varchar(255) default '' not null,
primary key (application_prototypeid)
) engine=innodb]
19318:20170210:141220.795 database upgrade failed

I really need help with this one. I've tried several ways and always same result..

Thanks, Jukka.

[jukka]

Solved; Upgrade also failing to db error for me.

For me it worked like this;

- dump zabbix database from old server (zabbix 2.4), mysqldump -uuser -ppassword zabbix > zabbix_bu.sql
- restore to new server mysql -uuser -ppassword < zabbix_bu.sql
- install zabbix 3 from packages to new server (fix db user, php timezone etc)
- try to start manually (to prevent sigterm during long db upgrade), sudo -u zabbix /usr/sbin/zabbix_server -f -c /etc/zabbix/xabbix_server.conf
- run into trouble with upgrade trying to create tables that exist eg
[Z3005] query failed: [1050] Table 'application_prototype' already exists [create table application_prototype (
application_prototypeid bigint unsigned not null,
itemid bigint unsigned not null,
templateid bigint unsigned,

*** fixing part *****
- install mysql client
- mysql -uuser -ppassword zabbix
- check existing table structure how it was created, show create table tablename;
- check if table is empty, select * from tablename;
- if table is empty drop it, drop table tablename;
- then came problem with constraints. I temporarily disabled checks because I thought that upgrade will create new table with same structure,
SET foreign_key_checks = 0;
- drop table;
- run zabbix (creates table)
- then I compared the old table structure with new one and they matched
- if you ran into same problem with another table -> go back to *** fixing part ***

I had to drop about 10 tables, they all were empty and structure matched.
Now running fine. Remember to re-enable foreign key check.

Maybe upgrade is lacking "if exists" statement?

- Jukka.

[zabbixei]

upgrade from 3 to 3.2 failing

hey guys i am trying to upgrade from 3.0 to 3.2 and after running the ./configure --enable-server --enable-agent --with-mysql --enable-ipv6 --with-net-snmp --with-libcurl --with-libxml2 i am getting this error: MySQL library not found.

any idea what i am missing here?

[glebs.ivanovskis]

any idea what i am missing here?

MySQL development package.

[zabbixei]

MySQL development package.

Hi,

Thanks for the reply the problem has been solved with development package. everything went well with compilation but now i am having another issue when i try to start mysql i am getting this /usr/sbin/mysqld: unknown option '--explicit_defualts_for_timestamp'

[glebs.ivanovskis]

i am getting this /usr/sbin/mysqld: unknown option '--explicit_defualts_for_timestamp'

We are getting off topic. Check your spelling.

[zabbixei]

We are getting off topic. Check your spelling.

hi, the spelling is OK as you can see in snapshot. sorry it was miss spelled when i typed it here.

[zabbixei]

Hi,

sorry to bother again, i have fixed that problem but now i am have different problem. i am getting this error when i try to open the zabbix console.

any idea? i have updated everything.

[glebs.ivanovskis]

You have old 3.0 database. Launch Zabbix server binary, it will update it automatically.