database upgrade from 1.8.10 to 2.0 fails

[0siris]

it has been reported as ZBX-5125

[coudy]

to repair databases try this steps

https://support.zabbix.com/browse/ZBX-4729

[0siris]

to repair databases try this steps
https://support.zabbix.com/browse/ZBX-4729

Seems like an other error to me?

[coudy]

Seems like an other error to me?

last two comments:

I was able to self repair my db, patch it to 2.0 and upgrade zabbix to 1.9.9 beta.
I can confirm, that Availability reports are now working much much faster as in 1.8.10. Report for 1 year for 212 hosts took several seconds.
I'm writing my howto, when it will be available, I'll post link here.

here is my howto

http://chovan.net/2012/03/how-to-repair-corrupted-zabbix-database/

[sheen]

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...

Thanks for the explanation, now I think I've understand what is the problem but I'm unable to resolve it, I'm not DBA (yet ^^).

last two comments:

I was able to self repair my db, patch it to 2.0 and upgrade zabbix to 1.9.9 beta.
I can confirm, that Availability reports are now working much much faster as in 1.8.10. Report for 1 year for 212 hosts took several seconds.
I'm writing my howto, when it will be available, I'll post link here.

here is my howto
http://chovan.net/2012/03/how-to-rep...bbix-database/

I've followed entirelly your howto thinking it will clean my database and solve my problem, but even with this new fresh database I have exactly the same issues than before in zabbix_server.log :

--------------------------------------------------------------
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')]
--------------------------------------------------------------






I guess I'm stuck with 1.8 for a while.
Whatever, 1.8 is a great release, I can wait.

[groe0286]

Thanks for the explanation, now I think I've understand what is the problem but I'm unable to resolve it, I'm not DBA (yet ^^).

Well, working with problems like this one is how you become one quickly. ;-)

I've followed entirelly your howto thinking it will clean my database and solve my problem, but even with this new fresh database I have exactly the same issues than before in zabbix_server.log :

--------------------------------------------------------------
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')]
--------------------------------------------------------------






I guess I'm stuck with 1.8 for a while.
Whatever, 1.8 is a great release, I can wait.

No, never give up. There are 2 solutions:

1) You add a row to the hosts table that has a hostid set to 0. Like a dummy row that does nothing. This is the best solution.
2) You drop the constraint. This is not recommended, as the constraint is there for a reason.

You do this as follows:

1) insert into hosts () values ();
2) alter table autoreg_host drop constraint `c_autoreg_host_1`;

As I've stated before, go for option 1.

This will resolve your issues, but will create 1 empty host.

[pajakh]

Hi
When I tried upgrade I saw massage " Can't create table 'zabbix.#sql-56f_8' (errno: 150)

Then when I checked "show innodb status;"
I saw:
Error in foreign key constraint of table zabbix/#sql-56f_8: FOREIGN KEY (graphid) REFERENCES graphs (graphid) ON DELETE CASCADE:
Cannot reolve table name close to:
(graphid) ON DELETE CASCADE

I don't know how to resolve this

[groe0286]

Hi
When I tried upgrade I saw massage " Can't create table 'zabbix.#sql-56f_8' (errno: 150)

Then when I checked "show innodb status;"
I saw:
Error in foreign key constraint of table zabbix/#sql-56f_8: FOREIGN KEY (graphid) REFERENCES graphs (graphid) ON DELETE CASCADE:
Cannot reolve table name close to:
(graphid) ON DELETE CASCADE

I don't know how to resolve this

This is typical when you try to do an upgrade twice, without restoring a backup. You HAVE to restore a backup before trying to upgrade again.

[pajakh]

I dumped database
dump database zabbix;
Created new one
zreate database zabbix;
And restored database from production server backup, but I received again same message.

[groe0286]

I dumped database
dump database zabbix;
Created new one
zreate database zabbix;
And restored database from production server backup, but I received again same message.

dump database zabbix; is not valid MySQL syntax.

[root@vm ~]# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Your database is messed up. Most likely cause is still that you didn't restore a backup. Maybe you're trying to upgrade after you restored a backup of the database that you took after you did the upgrade. Try to figure out what the problem is by doing SHOW ENGINE InnoDB STATUS; and look for foreign key problems.

[pajakh]

Of course drop not dump
drop database zabbix;
then
create database zabix;
upgrade -u root -ppass zabbix < zabbix_20120603.sql

I got:
ERROR 1005 (HY000) at line 184: Can't create table ;zabbix.#sql-c0e_2' (errno: 150)
Failed to patch database Restore from backup.

I don't know what I'm doing wrong
I am sure it is good backup
when I tried patch again a received:
ERROR 1060 (42S21) at line 54: Duplicate column name 'listen_ip'

SHOW ENGINE InnoDB STATUS;

LATEST FOREIGN KEY ERROR
Error in foreign key constraint of table zabbix/#sql-c0e_2:
FOREIGN KEY (graphid) REFERENCES graphs (graphid) ON DELETE CASCADE:
Cannot resolve table name close to:
(graphid) ON DELETE CASCADE

[groe0286]

Of course drop not dump
drop database zabbix;
then
create database zabix;
upgrade -u root -ppass zabbix < zabbix_20120603.sql

That doesn't look right to me. Import the database first:

mysql> DROP DATABASE zabbix;
mysql> CREATE DATABASE zabbix CHARACTER SET utf8;
[root@test ~]# mysql -uroot -p -Dzabbix < zabbix_20120603.sql

then perform the upgrade:
[root@test ~]# ./upgrade -u root -ppass zabbix

[groe0286]

LATEST FOREIGN KEY ERROR
Error in foreign key constraint of table zabbix/#sql-c0e_2:
FOREIGN KEY (graphid) REFERENCES graphs (graphid) ON DELETE CASCADE:
Cannot resolve table name close to:
(graphid) ON DELETE CASCADE

Now that I looked a bit more carefully at your error:

Can you do a:
mysql> SHOW CREATE TABLE graphs;
just AFTER you restored the database.

It does not seem to find that table in your backup.

[pajakh]

SHOW CREATE TABLE graphs;

| graphs | CREATE TABLE 'graphs' (
'graphid' bigint(20) unsigned NOT NULL DEFAULT '0',
'name' varchar(128) NOT NULL DEFAULT '',
'width' int(11) NOT NULL DEFAULT '0',
'height' int(11) NOT NULL DEFAULT '0',
'yaxismin' double(16,4) NOT NULL DEFAULT '0.000',
'yaxismax' double(16,4) NOT NULL DEFAULT '0.000',
'templateid' bigint(20) unsigned NOT NULL DEFAULT '0',
'show_work_period' int(11) NOT NULL DEFAULT '1',
'show_triggers' int(11) NOT NULL DEFAULT '1',
'graphtype' int(11) NOT NULL DEFAULT '0',
'show_legend' int(11) NOT NULL DEFAULT '0',
'show_3d' int(11) NOT NULL DEFAULT '0',
'percent_left' double(16,4) NOT NULL DEFAULT '0.000',
'percent_right' double(16,4) NOT NULL DEFAULT '0.000',
'ymin_type' int(11) NOT NULL DEFAULT '0',
'ymax_type' int(11) NOT NULL DEFAULT '0',
'ymin_itemid' bigint(20) unsigned NOT NULL DEFAULT '0',
'ymax_itemid' bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY ('graphid'),
KEY graphs_graphs_1' ('name')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

at line 184 in patch.sql is:
ALTER TABLE graph_discovery ADD CONSTRAINT c_graph_discovery_1 FOREIGN KEY (graphid) REFERENCES graphs (graphid) ON DELETE CASCADE;
I am not DB exprert
I am using test server with test mysql server but backup is from production server
Is it possible that problem is caused by engine mismatch or something similar?

[avinash]

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.

Any other error you get? Still not working?
Post them here, and I'll let you know how to fix the patch.sql...

Hi, thanks for this fix, it helped me a lot, untill I ran into this error:

ERROR 1062 (23000) at line 699: Duplicate entry '14370-1' for key 2

Line 699 in patch.sql is: CREATE UNIQUE INDEX hosts_groups_1 ON hosts_groups (hostid,groupid);

I found out that 14370 is a template we named Template_Blade_Centre_8677WEB in group Template with id 1

So I queried the table hosts_groups for '14370-1' and found 130 rows of


+-------------+--------+---------+
| hostgroupid | hostid | groupid |
+-------------+--------+---------+
| 11078 | 14370 | 1 |
<output ommited>

I'm not a DBA or MySQL expert, I'm an avarage MySQL user. I have no idea how to interpret this error or how to move from here.

Can you help me out ? If you need any additional info, please let me know.

Thanks in advance, Avi