-
-
Stuck with an error too. I was trying to do upgrade from 1.8 to 2.0.
when I run mysql -u xxx -pxxx --database=zabbix < patch.sql
ERROR 1091 (42000) at line 140: Can't DROP 'escalations_2'; check that column/key exists
I guess we stuck w/ version 1.8 forever. Anyway I'm happy with 1.8, it works fine
.
Comment
-
Hi.
I'm getting a similar issue, but in a different area :
ERROR 1452 (23000) at line 104: Cannot add or update a child row: a foreign key constraint fails (`zabbix-2-0-0`.<result 2 when explaining filename '#sql-693_5cc4'>, CONSTRAINT `c_config_2` FOREIGN KEY (`discovery_groupid`) REFERENCES `groups` (`groupid`))
Failed to patch Zabbix database. Restore from backup
Is there a tool we can run to validate the integrity of the data in the database before doing updates ?
Thank
GregComment
-
OK, let me help you guys out here, I'm a database expert.
I have tested the upgrade of my installation, which is HUGE by the way:
Number of hosts (monitored/not monitored/templates) 11495
Number of items (monitored/disabled/not supported) 591591
Number of triggers (enabled/disabled)[problem/unknown/ok] 179738
And I had a few problems with this script, but nothing major.
First of all, why are you trying to do this database upgrade, if you know nothing about databases? It can be tricky! If this is a company Zabbix install, ask for DBA help! I'm assuming MySQL here.
Anyway:
1) Make a backup before you run the upgrade script. It's not transactional, so if it fails, your database is in between states. Do what it says if it fails, restore a backup, fix the patch.sql file and try again.
2) I see some of you have this error: Can't create table './zabbix/#sql-4896_5.frm' (errno: 121) You didn't restore a backup when the backup script failed and it asked you to do so, did you? :-) That error is likely when you run the upgrade script again, without restoring a full backup of your 1.8 database.
3) Line 104 example: ALTER TABLE config ADD CONSTRAINT c_config_2 FOREIGN KEY (discovery_groupid) REFERENCES groups (groupid);
I got that error too on a few lines. It tries to add a foreign key constraint, but it can't. An example cause is that there's no matching row in the destination table.
You solve that, by editing the patch.sql script and change line 104 to 3 lines:
SET foreign_key_checks=0;
ALTER TABLE config ADD CONSTRAINT c_config_2 FOREIGN KEY
(discovery_groupid) REFERENCES groups (groupid);
SET foreign_key_checks=1;
Information on this: before the upgrade, the old 1.8 database may have created an inconsistent state. Maybe YOU have done so manually in fact, by messing around inside the database directly, maybe not, the GUI isn't always perfect. The upgrade script will add new constraints to make sure new errors cannot be made in version 2.0, even if the GUI would try to remove a row, the database would complain, so this is a good thing. But if foreign_key_checks is on (by default it MUST be on!) during the upgrade script, your existing 1.8 database must be in perfect state. If it's not, Zabbix usually still works, but you cannot add the constraint. This constraint is added to PREVENT new mistakes. That's why it's safe to turn the key checks off just for that line.
4) If you get an error: that says that it cannot create a table because it already exists, this is caused by the same thing as point 2). Restore a full backup of 1.8 before trying the upgrade again.
Any other error you get? Still not working?
Post them here, and I'll let you know how to fix the patch.sql...Comment
-
I've added those lines in the patch.sql file as you've proposed. And did a new db restore for a prod to the test server. The upgrade script is running but I see errors like:
ERROR 1091 (42000) at line 142: Can't DROP 'escalations_2'; check that column/key exists
Should I ignore this type of error?Comment
-
Comment
-
So by that statement anyone that drives a car should also be a mechanic :-) .
For us non DBA (not everyone can be a DBA, or have on on staff) that use Zabbix it would be nice if it gave a little more info as to where this issues is , ie the row that's cause the upgrade to fail.
No I don't touch the database backed , apart from running upgrade scripts provided, which until now have worked well for me :-) .Comment
-
I disagree, bad comparison.
It's like car mechanics that don't know anything about the engine. How are you going to help customers? Clean the car and say it's shiny again, but it's still not running properly? :-)
Or a hospital with doctors specialized in all kinds of things, but without neurosurgeons. Are you going to just try the brain surgery yourself, because you've seen one do it before? :-)Last edited by groe0286; 15-06-2012, 14:29.Comment
-
I get this:
--------------------------------------
Table
escalations
Create Table
CREATE TABLE `escalations` (
`escalationid` bigint(20) unsigned NOT NULL DEFAULT '0',
`actionid` bigint(20) unsigned NOT NULL DEFAULT '0',
`triggerid` bigint(20) unsigned NOT NULL DEFAULT '0',
`eventid` bigint(20) unsigned NOT NULL DEFAULT '0',
`r_eventid` bigint(20) unsigned NOT NULL DEFAULT '0',
`nextcheck` int(11) NOT NULL DEFAULT '0',
`esc_step` int(11) NOT NULL DEFAULT '0',
`status` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`escalationid`),
KEY `escalations_1` (`actionid`,`triggerid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--------------------------------------
Thanks for help!Comment
-
The key escalations_1 is present, but the key escalations_2 is missing.
I have this output:
CREATE TABLE `escalations` (
`escalationid` bigint(20) unsigned NOT NULL DEFAULT '0',
`actionid` bigint(20) unsigned NOT NULL DEFAULT '0',
`triggerid` bigint(20) unsigned NOT NULL DEFAULT '0',
`eventid` bigint(20) unsigned NOT NULL DEFAULT '0',
`r_eventid` bigint(20) unsigned NOT NULL DEFAULT '0',
`nextcheck` int(11) NOT NULL DEFAULT '0',
`esc_step` int(11) NOT NULL DEFAULT '0',
`status` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`escalationid`),
KEY `escalations_1` (`actionid`,`triggerid`),
KEY `escalations_2` (`status`,`nextcheck`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
So 2 questions:
1) Is this an 1.8 database you are trying to upgrade? If you are upgrading from 1.6, you need to first run the upgrade to 1.8.
2) Did the upgrade fail before and you didn't restore a full backup?
You can comment out line 142 in patch.sql, because the key it's trying to throw away is not present in your database, so this problem alone doesn't mean much, except that it worries me that you're not starting from a good 1.8 copy, meaning that you'd better not continue (many other problems may arise).Comment
-
Yep, This is a 1.8 to 2.0 upgrade. Well, I have a testing server, where I try to test things first. After your post I have restored again the prod zabbix database to a test server. And started an upgrade from scratch on a db that was untouched. I guess my prod. database doesn't have that key ether. Hmmm
Comment
-
In that case, comment out line 142 in patch.sqlYep, This is a 1.8 to 2.0 upgrade. Well, I have a testing server, where I try to test things first. After your post I have restored again the prod zabbix database to a test server. And started an upgrade from scratch on a db that was untouched. I guess my prod. database doesn't have that key ether. Hmmm
It'll probably get stuck on another line though, if that key is missing, others might be missing ;-)Comment
-
Thanks for your fix,
After 24h of patching database, it seems ok on the first hand.
I had only one error in error-log :
-----------------------------------------------------------
120615 12:36:22 InnoDB: ERROR: the age of the last checkpoint is 9433830,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
-----------------------------------------------------------
But on the second hand, none of all my hosts are responding successfully, I have an error for each host in /tmp/zabbix_server.log, for examples :
20783:20120618:114741.202 Sending list of active checks to [192.168.69.30] failed: host [PARC1 - Antispam] not found
20784:20120618:114741.746 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix/autoreg_host`, CONSTRAINT `c_autoreg_host_1` FOREIGN KEY (`proxy_hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE) [insert into autoreg_host (autoreg_hostid,proxy_hostid,host) values (453,0,'PARC2 - RENDERFARM01')]
20784:20120618:114741.747 Sending list of active checks to [192.168.17.11] failed: host [PARC2 - RENDERFARM01] not found
20788:20120618:114743.744 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix/autoreg_host`, CONSTRAINT `c_autoreg_host_1` FOREIGN KEY (`proxy_hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE) [insert into autoreg_host (autoreg_hostid,proxy_hostid,host) values (454,0,'PARC1 - Proxy')]
20788:20120618:114743.752 Sending list of active checks to [192.168.69.22] failed: host [PARC1 - Proxy] not found
20786:20120618:114743.882 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix/autoreg_host`, CONSTRAINT `c_autoreg_host_1` FOREIGN KEY (`proxy_hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE) [insert into autoreg_host (autoreg_hostid,proxy_hostid,host) values (455,0,'PARC2 - Proxy')]
This database update is really a pain... : /
Do you have any idea ?Last edited by sheen; 18-06-2012, 11:43.Comment
-
So, the error in error-log is simple, and easy to resolve, it's caused by your database log files being too small, you left the innodb_log_file_size=10M (if you are using InnoDB as your database engine, check by doing a:
mysql> SHOW VARIABLES LIKE 'storage_engine';
).
That's far too low. I have it set at 512M, but this value really depends on what your database is like.
So, what happens is, there is a new constraint on the database that says that a new row added to table "autoreg_host", has a pointer to a host row in table "hosts".
As you can see, the "proxy_hostid" in "autoreg_host" points to a hostid in "hosts". In this example, it tries to add the value "proxy_hostid = 0" into "autoreg_hosts", but that fails, because there is no row in "hosts" that has "hostid" set to 0.
Let me check if I have that row in hosts...
mysql> select * from hosts where hostid = '0';
Empty set (0.00 sec)
So there's something weird going on there. Maybe it's not normal it tries to insert a row in autoreg_hosts with proxy_hostid set to 0.
I'd try to investigate what it's trying to do...Comment
Comment