[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 |
Issue Links: |
|
Description |
Normal Zabbix Upgrade Process from Sources zabbix_server.log 25983:20171011:190511.204 Starting Zabbix Server. Zabbix 3.4.2 (revision 72885). ... 25983:20171011:190932.204 completed 43% of database upgrade |
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?" |
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 |
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? |
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, 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:
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
Skip failing upgrade patch
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. |