[ZBX-7108] Unsuccessful database migration for Proxies Created: 2013 Oct 07  Updated: 2017 May 30  Resolved: 2016 Sep 23

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Installation (I)
Affects Version/s: 2.0.8
Fix Version/s: 2.0.19rc1, 2.2.15rc1, 3.0.5rc1, 3.2.1rc1, 3.4.0alpha1

Type: Incident report Priority: Major
Reporter: Marc Schoechlin Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: database, patch
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

According to https://www.zabbix.com/documentation/2.0/manual/installation/upgrade_notes#upgrade_notes_for_proxies we tried to perform a upgrade of our zabbix proxies.
(Upgraded procedure for server runs for two hours and we do not want to lose the data collected while upgraded in the schema of the main server)

While performing a upgrade at the proxy we get this:

--------------
SET @graphthemeid = (SELECT MAX(graphthemeid) FROM graph_theme)
--------------

--------------
INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview)
VALUES
(@graphthemeid + 1, 'Dark orange', 'darkorange', '333333', '0A0A0A', '888888', '222222', '4F4F4F', 'EFEFEF', 'DFDFDF', 'FF5500', 'FF5500', 'FF1111', '1F1F1F', 1, 1),
(@graphthemeid + 2, 'Classic', 'classic', 'F0F0F0', 'FFFFFF', '333333', 'CCCCCC', 'AAAAAA', '000000', '222222', 'AA4444', '11CC11', 'CC1111', 'E0E0E0', 1, 1)
--------------

ERROR 1062 (23000) at line 223: Duplicate entry '0' for key 'PRIMARY'

After adding the following hack to the database the upgrade is sucessful:

# diff -u patch.sql patch-fixed.sql
--- patch.sql	2013-10-07 12:40:18.340144674 +0200
+++ patch-fixed.sql	2013-10-07 12:40:08.292000626 +0200
@@ -220,6 +220,7 @@
 
 -- Insert new graph theme
 SET @graphthemeid = (SELECT MAX(graphthemeid) FROM graph_theme);
+SET @graphthemeid =(SELECT IFNULL(@graphthemeid,0));
 INSERT INTO graph_theme (graphthemeid, description, theme, backgroundcolor, graphcolor, graphbordercolor, gridcolor, maingridcolor, gridbordercolor, textcolor, highlightcolor, leftpercentilecolor, rightpercentilecolor, nonworktimecolor, gridview, legendview)
 VALUES
 (@graphthemeid + 1, 'Dark orange', 'darkorange', '333333', '0A0A0A', '888888', '222222', '4F4F4F', 'EFEFEF', 'DFDFDF', 'FF5500', 'FF5500', 'FF1111', '1F1F1F', 1, 1),


 Comments   
Comment by richlv [ 2013 Oct 07 ]

although this shows that we should have a separate patch for proxy db, graph themes do not make much sense there...

Comment by Javier Barroso [ 2015 Aug 25 ]

Seems like with mysql 5.6 it is necessary the patch proposed in this ticket.

I was updating from zabbix 1.8.10 to 2.0 => 2.4.6

I would suggest updating 2.0.x patch.mysql with the patch

Thank you

Comment by richlv [ 2016 Sep 17 ]

another proxy db upgrade problem : ZBX-11224

Comment by Vladislavs Sokurenko [ 2016 Sep 19 ]

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-7108
Now graph_theme data will only be added if data for it exists ( server database )

Comment by Alexander Vladishev [ 2016 Sep 20 ]

Successfully tested. Have a look at my changes in r62626 before a merge.

Comment by Vladislavs Sokurenko [ 2016 Sep 22 ]

merged in:

  • 2.0.19rc1 r62669
  • 2.2.15rc1 r62671
  • 3.0.5rc1 r62672
  • 3.2.1rc1 r62673
  • 3.3.0 (trunk) r62675
Generated at Fri Apr 26 01:08:21 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.