[ZBX-12866] Database upgrade from Zabbix 2.2 to Zabbix 3.4 fails Created: 2017 Oct 12  Updated: 2018 Feb 28  Resolved: 2017 Oct 17

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 3.4.2
Fix Version/s: None

Type: Incident report Priority: Blocker
Reporter: Adail Horst Assignee: Unassigned
Resolution: Won't fix Votes: 0
Labels: dbpatches, mysql, sourcecode, upgrade
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Centos 7 x64
mysql Ver 14.14 Distrib 5.7.14-8, for Linux (x86_64) using 6.2


Issue Links:
Duplicate
is duplicated by ZBX-13555 Zabbix Upgrade from 2.4.4 to 3.4.x fa... Closed

 Description   

Normal Zabbix Upgrade Process from Sources
Compile and Link - OK
DB Upgrade works fine until error at 45%.

zabbix_server.log

25983:20171011:190511.204 Starting Zabbix Server. Zabbix 3.4.2 (revision 72885).
25983:20171011:190511.204 ****** Enabled features ******
25983:20171011:190511.204 SNMP monitoring: YES
25983:20171011:190511.204 IPMI monitoring: NO
25983:20171011:190511.204 Web monitoring: YES
25983:20171011:190511.204 VMware monitoring: YES
25983:20171011:190511.204 SMTP authentication: YES
25983:20171011:190511.204 Jabber notifications: NO
25983:20171011:190511.204 Ez Texting notifications: YES
25983:20171011:190511.204 ODBC: NO
25983:20171011:190511.204 SSH2 support: YES
25983:20171011:190511.204 IPv6 support: YES
25983:20171011:190511.204 TLS support: NO
25983:20171011:190511.204 ******************************
25983:20171011:190511.204 using configuration file: /usr/local/etc/zabbix_server.conf
25983:20171011:190511.231 current database version (mandatory/optional): 02020000/02020001
25983:20171011:190511.231 required mandatory version: 03040000
25983:20171011:190511.231 optional patches were found
25983:20171011:190511.232 starting automatic database upgrade
25983:20171011:190511.233 completed 0% of database upgrade
25983:20171011:190512.260 completed 1% of database upgrade
25983:20171011:190513.902 completed 2% of database upgrade
25983:20171011:190515.827 completed 3% of database upgrade

...

25983:20171011:190932.204 completed 43% of database upgrade
25983:20171011:190932.304 completed 44% of database upgrade
25983:20171011:190932.339 completed 45% of database upgrade
25983:20171011:190932.343 [Z3005] query failed: [1091] Can't DROP 'history_log_2'; check that column/key exists [drop index history_log_2 on history_log]
25983:20171011:190932.344 database upgrade failed



 Comments   
Comment by Alexey Pustovalov [ 2017 Oct 12 ]

do you have partitioning on history_log table?

Comment by Adail Horst [ 2017 Oct 12 ]

Describe of history_log;

mysql> desc history_log;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| id         | bigint(20) unsigned | NO   | MUL | NULL    |       |
| itemid     | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock      | int(11)             | NO   |     | 0       |       |
| timestamp  | int(11)             | NO   |     | 0       |       |
| source     | varchar(64)         | NO   |     |         |       |
| severity   | int(11)             | NO   |     | 0       |       |
| value      | text                | NO   |     | NULL    |       |
| logeventid | int(11)             | NO   |     | 0       |       |
| ns         | int(11)             | NO   |     | 0       |       |
+------------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
Comment by Alexey Pustovalov [ 2017 Oct 12 ]

show us "show create table history_log;"

Comment by Adail Horst [ 2017 Oct 12 ]

Hi Alexey, thanks for fast answer.

mysql> show create table history_log;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| history_log | 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 '',
  `severity` int(11) NOT NULL DEFAULT '0',
  `value` text NOT NULL,
  `logeventid` int(11) NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_log_1` (`itemid`,`clock`),
  KEY `history_log_0` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Comment by Adail Horst [ 2017 Oct 12 ]

"do you have partitioning on history_log table?"
Yes, has partitioning in this enviroment !

Comment by Glebs Ivanovskis (Inactive) [ 2017 Oct 12 ]

Can you please attach the full log without ...? Was there anything worthy?

Comment by Glebs Ivanovskis (Inactive) [ 2017 Oct 12 ]

I think we won't be able to extract any useful information here.

Dear spaww, seems Zabbix has dropped index but for some reason thinks that it hasn't and retries. You can increase dbversion by one (both mandatory and optional) and give it another chance to finish the upgrade.

Closing a Duplicate of ZBX-11203.

Comment by Adail Horst [ 2017 Oct 12 ]

Hi Glebs,

If you want I can send the full log... but is only "25983:20171011:190515.827 completed X% of database upgrade" ... because this I cut to relevant part...

About upgrade of "dbversion' is this the expected command?
`update DBVERSION set mandatory = 02020001, optional = 02020002 `
or
`update DBVERSION set mandatory = 02030000, optional = 02030001 `

Comment by Alexey Pustovalov [ 2017 Oct 12 ]

how many records in history_log table? I do not see that you have partitioning there. But there is changed indexes.

Comment by Glebs Ivanovskis (Inactive) [ 2017 Oct 12 ]

You need to find out your current version:

select * from dbversion;

Increase it by one. Follow these steps.

Comment by Adail Horst [ 2017 Oct 16 ]

Hi Glebs,
I make the required changes (select and increase one) but still with same error.

mysql> select * from dbversion;
+-----------+----------+
| mandatory | optional |
+-----------+----------+
|   2020000 |  2020001 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> update dbversion set mandatory = 02020001, optional = 02020002 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

service zabbix-server restart

I will try the second update sugestion...

update DBVERSION set mandatory = 02030000, optional = 02030001

Comment by Glebs Ivanovskis (Inactive) [ 2017 Oct 16 ]

Wait, have you started upgrade all over again? From the backup? Then you should not change dbversion.

Comment by Adail Horst [ 2017 Oct 16 ]
 25254:20171016:181133.099 Starting Zabbix Server. Zabbix 3.4.2 (revision 72885).
 25254:20171016:181133.099 ****** Enabled features ******
 25254:20171016:181133.099 SNMP monitoring:           YES
 25254:20171016:181133.099 IPMI monitoring:            NO
 25254:20171016:181133.099 Web monitoring:            YES
 25254:20171016:181133.099 VMware monitoring:         YES
 25254:20171016:181133.099 SMTP authentication:       YES
 25254:20171016:181133.099 Jabber notifications:       NO
 25254:20171016:181133.099 Ez Texting notifications:  YES
 25254:20171016:181133.099 ODBC:                       NO
 25254:20171016:181133.099 SSH2 support:              YES
 25254:20171016:181133.099 IPv6 support:              YES
 25254:20171016:181133.099 TLS support:                NO
 25254:20171016:181133.099 ******************************
 25254:20171016:181133.099 using configuration file: /usr/local/etc/zabbix_server.conf
 25254:20171016:181133.125 current database version (mandatory/optional): 02020001/02020002
 25254:20171016:181133.125 required mandatory version: 03040000
 25254:20171016:181133.125 optional patches were found
 25254:20171016:181133.125 starting automatic database upgrade
 25254:20171016:181133.126 completed 0% of database upgrade
 25254:20171016:181138.844 completed 1% of database upgrade
 25254:20171016:181146.673 completed 2% of database upgrade
 25254:20171016:181153.696 completed 3% of database upgrade
 25254:20171016:181208.175 completed 4% of database upgrade
 25254:20171016:181503.453 completed 5% of database upgrade
 25254:20171016:181600.879 completed 6% of database upgrade
 25254:20171016:181605.197 completed 7% of database upgrade
 25254:20171016:181605.302 completed 8% of database upgrade
 25254:20171016:181657.254 completed 9% of database upgrade
 25254:20171016:181657.261 completed 10% of database upgrade
 25254:20171016:181657.261 completed 10% of database upgrade
 25254:20171016:181657.268 completed 11% of database upgrade
 25254:20171016:181657.275 completed 12% of database upgrade
 25254:20171016:181657.290 completed 13% of database upgrade
 25254:20171016:181657.344 completed 14% of database upgrade
 25254:20171016:181657.676 completed 15% of database upgrade
 25254:20171016:181657.742 completed 16% of database upgrade
 25254:20171016:181657.793 completed 17% of database upgrade
 25254:20171016:181658.160 completed 18% of database upgrade
 25254:20171016:181811.400 completed 19% of database upgrade
 25254:20171016:181811.490 completed 20% of database upgrade
 25254:20171016:181811.563 completed 21% of database upgrade
 25254:20171016:181812.693 completed 22% of database upgrade
 25254:20171016:181817.097 completed 23% of database upgrade
 25254:20171016:181825.739 completed 24% of database upgrade
 25254:20171016:181827.835 completed 25% of database upgrade
 25254:20171016:181834.770 completed 26% of database upgrade
 25254:20171016:181834.947 completed 27% of database upgrade
 25254:20171016:181835.116 completed 28% of database upgrade
 25254:20171016:181835.194 completed 29% of database upgrade
 25254:20171016:181835.277 completed 30% of database upgrade
 25254:20171016:181840.691 completed 31% of database upgrade
 25254:20171016:181840.732 completed 32% of database upgrade
 25254:20171016:181840.796 completed 33% of database upgrade
 25254:20171016:181845.394 completed 34% of database upgrade
 25254:20171016:181847.799 completed 35% of database upgrade
 25254:20171016:181847.876 completed 36% of database upgrade
 25254:20171016:181848.238 completed 37% of database upgrade
 25254:20171016:181848.322 completed 38% of database upgrade
 25254:20171016:181848.417 completed 39% of database upgrade
 25254:20171016:181848.605 completed 40% of database upgrade
 25254:20171016:181848.710 completed 41% of database upgrade
 25254:20171016:181848.787 completed 42% of database upgrade
 25254:20171016:181848.929 completed 43% of database upgrade
 25254:20171016:181849.129 completed 44% of database upgrade
 25254:20171016:181849.153 completed 45% of database upgrade
 25254:20171016:181849.153 [Z3005] query failed: [1091] Can't DROP 'history_log_2'; check that column/key exists [drop index history_log_2 on history_log]
 25254:20171016:181849.154 database upgrade failed
Comment by Glebs Ivanovskis (Inactive) [ 2017 Oct 17 ]

OK. Now do select * from dbversion;

I mean do not restore from backup, try skipping this step.

Comment by Adail Horst [ 2017 Oct 17 ]

Glebs,

I already restore my backup. So, for this case You need I execute:

  • `select * from dbversion;`
  • Try again, and receive the error;
  • `select * from dbversion;` a second time;
  • Send the results to this ticket

Is this ? I don't know if I understand this part: "I mean do not restore from backup, try skipping this step." ... How I can ignore part of DB upgrade process ? I can try to start Zabbix_server again after this fail and will works without problems on DB ?

Comment by Glebs Ivanovskis (Inactive) [ 2017 Oct 17 ]

I'll try to explain the best I can. You cannot skip upgrade procedure, but upgrade procedure is not a one big lump, it consists of many-many small patches. Each patch does some stuff with DB and increases dbversion so that if upgrade is interrupted in the middle Zabbix knows which patches were already applied and which still need to be applied.

The patch which is failing for you wants to drop history_log_2 index on history_log table. It fails because index is not there. Most likely someone has modified your DB schema. And there are basically two ways out: create this index before upgrade so that schema is back to normal and Zabbix has an index to delete during upgrade or assist Zabbix manually during upgrade process.

I assume that now you've read carefully and fully understand Zabbix database upgrade procedure. Now the instructions...

Create missing indexes
  1. Restore your 2.2 database
  2. Compare your show create table history log with this one:
    CREATE TABLE `history_log` (
    	`id`                     bigint unsigned                           NOT NULL,
    	`itemid`                 bigint unsigned                           NOT NULL,
    	`clock`                  integer         DEFAULT '0'               NOT NULL,
    	`timestamp`              integer         DEFAULT '0'               NOT NULL,
    	`source`                 varchar(64)     DEFAULT ''                NOT NULL,
    	`severity`               integer         DEFAULT '0'               NOT NULL,
    	`value`                  text                                      NOT NULL,
    	`logeventid`             integer         DEFAULT '0'               NOT NULL,
    	`ns`                     integer         DEFAULT '0'               NOT NULL,
    	PRIMARY KEY (id)
    ) ENGINE=InnoDB;
    CREATE INDEX `history_log_1` ON `history_log` (`itemid`,`clock`);
    CREATE UNIQUE INDEX `history_log_2` ON `history_log` (`itemid`,`id`);
    
  3. Create missing indexes
  4. Launch Zabbix server
Skip failing upgrade patch
  1. Restore your 2.2 database
  2. Launch Zabbix server
  3. Wait until it fails
  4. Now select * from dbversion; (I assume it will be something like 3010000) and increase both mandatory and optional by one
  5. Launch Zabbix server again to finish the remaining part of upgrade

First option is safer but will probably take more time. Second one is faster but less safer because you can end up with non-standard DB schema. Make this decision responsibly and don't skip any failing patch this way!

Comment by Glebs Ivanovskis (Inactive) [ 2017 Oct 17 ]

Changing the resolution to Won't Fix, because the root cause was non-standard DB schema.

Generated at Fri Mar 29 00:35:19 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.