[ZBX-11224] Proxy SQL Upgrade fails to 3.2.0 Created: 2016 Sep 17  Updated: 2017 May 30  Resolved: 2016 Sep 20

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P)
Affects Version/s: 3.0.3, 3.0.4
Fix Version/s: None

Type: Incident report Priority: Critical
Reporter: Antti Hurme Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates ZBX-11203 database upgrade from Zabbix 3.0 to Z... Closed

 Description   

Updated our zabbix server from 3.0.4 to 3.2.0 where after i upgraded our proxy servers. I received multiple errors during the upgrade scripts for the proxy servers databases. Luckily they were proxy servers and could be dropped and re-created.

Here's a list of all the errors, not sure which versions they are but database versions are included from all.

 4781:20160917:125638.393 using configuration file: /etc/zabbix/zabbix_proxy.conf
  4781:20160917:125638.398 current database version (mandatory/optional): 03010018/03010018
  4781:20160917:125638.398 required mandatory version: 03020000
  4781:20160917:125638.398 starting automatic database upgrade
  4781:20160917:125638.408 [Z3005] query failed: [1005] Can't create table `zabbix`.`#sql-6af7_da` (errno: 121 "Duplicate key on write or update") [alter table event_recovery add constraint c_event_recovery_1 foreign key (eventid) references events (eventid) on delete cascade]
  4781:20160917:125638.408 database upgrade failed

  24549:20160917:125658.653 using configuration file: /etc/zabbix/zabbix_proxy.conf
 24549:20160917:125658.664 current database version (mandatory/optional): 03010021/03010021
 24549:20160917:125658.664 required mandatory version: 03020000
 24549:20160917:125658.664 starting automatic database upgrade
 24549:20160917:125658.665 [Z3005] query failed: [1060] Duplicate column name 'recovery' [alter table operations add recovery integer default '0' not null]
 24549:20160917:125658.665 database upgrade failed

  19342:20160917:125740.684 using configuration file: /etc/zabbix/zabbix_proxy.conf
 19342:20160917:125740.688 current database version (mandatory/optional): 03010061/03010061
 19342:20160917:125740.688 required mandatory version: 03020000
 19342:20160917:125740.688 starting automatic database upgrade
 19342:20160917:125740.695 [Z3005] query failed: [1005] Can't create table `zabbix`.`#sql-24d9_ee` (errno: 121 "Duplicate key on write or update") [alter table corr_condition_tagpair add constraint c_corr_condition_tagpair_1 foreign key (corr_conditionid) references corr_condition (corr_conditionid) on delete cascade]

  3729:20160917:133406.408 using configuration file: /etc/zabbix/zabbix_proxy.conf
  3729:20160917:133406.414 current database version (mandatory/optional): 03010003/03010003
  3729:20160917:133406.414 required mandatory version: 03020000
  3729:20160917:133406.414 starting automatic database upgrade
  3729:20160917:133406.414 [Z3005] query failed: [1060] Duplicate column name 'recovery_mode' [alter table triggers add recovery_mode integer default '0' not null]

Last but not least, here is one that worked:
works:

 32310:20160917:124218.124 current database version (mandatory/optional): 03000000/03000000
 32310:20160917:124218.125 required mandatory version: 03020000

Might have been other versions that worked but picked this one up at least



 Comments   
Comment by Antti Hurme [ 2016 Sep 17 ]

Probably can't be classified as critical after all, but can't change it anymore :/

Comment by richlv [ 2016 Sep 17 ]

another proxy db upgrade problem : ZBX-7108

Comment by Vladislavs Sokurenko [ 2016 Sep 20 ]

Reproduced on 3.0.4 to 3.2.0 upgrade

Issue occurs if proxy or server is shut down in moment which cannot be rolled back.
It is because CREATE INDEX is one of the statements that cause an implicit commit.
Info:

http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

So what happens is that we CREATE INDEX, crash, but don't update db version to new one.
Next time we enter DBpatch_3010018 we don't know that it was already performed and try to do it again resulting in a duplicate error.

Fix must be investigated, but I think what should be done is:
Upgrade database version
Then patch.

Patch will call COMMIT; so we either have upgraded db version + patched or nothing.

Steps to reproduce:
1. Change function below to simulate crash.

Index: src/libs/zbxdbupgrade/dbupgrade_3010.c
===================================================================
--- src/libs/zbxdbupgrade/dbupgrade_3010.c	(revision 62623)
+++ src/libs/zbxdbupgrade/dbupgrade_3010.c	(working copy)
@@ -181,7 +181,10 @@
 
 static int	DBpatch_3010018(void)
 {
-	return DBcreate_index("event_recovery", "event_recovery_1", "r_eventid", 0);
+	DBcreate_index("event_recovery", "event_recovery_1", "r_eventid", 0);
+	zabbix_log(LOG_LEVEL_CRIT, "simulated crash during alter table");
+	exit(0);
+	return 0;
 }

2. Now when proxy has exited launch it again and see:

7023:20160920:171826.707 [Z3005] query failed: [1061] Duplicate key name 'event_recovery_1' [create index event_recovery_1 on event_recovery (r_eventid)]
Comment by Alexander Vladishev [ 2016 Sep 20 ]

Closed as duplicate of ZBX-11203.

Generated at Mon Dec 23 18:02:34 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.