database upgrade from 1.8.10 to 2.0 fails

[avinash]

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

I ran the following query on the host_groups table:

select hostid, groupid, COUNT(*) AS dupes from hosts_groups group by hostid, groupid HAVING dupes > 1 ORDER BY dupes DESC;

It resulted in:


+--------+---------+-------+
| hostid | groupid | dupes |
+--------+---------+-------+
| 14370 | 1 | 129 |
+--------+---------+-------+

I now understand why creating the unique index is failing, but I don't understand why there are 129 duplicated entries for this groupid/hostid combination.

It all comes down to one template with id 14370 in the group named Templates with id 1. I'm not sure if simply dropping 128 of these entries (leaving me with one unique) will solve this issue and not bring up other issues in other tables

[groe0286]

It all comes down to one template with id 14370 in the group named Templates with id 1. I'm not sure if simply dropping 128 of these entries (leaving me with one unique) will solve this issue and not bring up other issues in other tables

I don't exactly know how this happened, but this table links hosts to host groups. Saying that host A belongs to group B 130 times is useless. I'd remove the duplicates by running a query like the one below. It will remove all duplicate entries in that table:

mysql> DELETE FROM hosts_groups WHERE hostgroupid IN (select hostgroupid from (select hostgroupid, hostid, groupid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1) a);

I HAVE NOT TESTED THIS QUERY. Please make sure you have a backup.
It may also spit out a syntax error, just let me know ;-)

[groe0286]

I don't exactly know how this happened, but this table links hosts to host groups. Saying that host A belongs to group B 130 times is useless. I'd remove the duplicates by running a query like the one below. It will remove all duplicate entries in that table:

mysql> DELETE FROM hosts_groups WHERE hostgroupid IN (select hostgroupid from (select hostgroupid, hostid, groupid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1) a);

I HAVE NOT TESTED THIS QUERY. Please make sure you have a backup.
It may also spit out a syntax error, just let me know ;-)

Hmm, wait, don't run that :-) if it encounters duplicates for say host A in group B, it will delete all of them, removing the host from the group. Just a second while I fix the query.

[groe0286]

I don't exactly know how this happened, but this table links hosts to host groups. Saying that host A belongs to group B 130 times is useless. I'd remove the duplicates by running a query like the one below. It will remove all duplicate entries in that table:

mysql> DELETE FROM hosts_groups WHERE hostgroupid IN (select hostgroupid from (select hostgroupid, hostid, groupid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1) a);

I HAVE NOT TESTED THIS QUERY. Please make sure you have a backup.
It may also spit out a syntax error, just let me know ;-)

Hmm, wait, don't run that :-) if it encounters duplicates for say host A in group B, it will delete all of them, removing the host from the group. Just a second while I fix the query.

Don't you just love running queries in your head
Running this query once will remove 1 duplicate from all hosts that have a duplicate until there's 1 row left actually. You don't want to run this 130 times. Just a sec more haha ;-)

[groe0286]

Don't you just love running queries in your head
Running this query once will remove 1 duplicate from all hosts that have a duplicate until there's 1 row left actually. You don't want to run this 130 times. Just a sec more haha ;-)

Right, so the correct query is:

mysql> delete from hosts_groups where hostgroupid IN (select hostgroupid from (select * from hosts_groups hg, (select hostgroupid as parenthostgroupid, hostid as hid, groupid as gid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1) hg_dupes where hg.hostid = hg_dupes.hid and hg.groupid = hg_dupes.gid) res where res.hostgroupid != parenthostgroupid);

This has been tested:
mysql> select * from hosts_groups;

+-----------------+-----------------+-----------------+
| hostgroupid | hostid | groupid |
+-----------------+-----------------+-----------------+
| 300300000025090 | 300300000006407 | 300300000000025 |
| 300300000025091 | 300300000006407 | 300300000000025 |
| 300300000025095 | 300300000006407 | 300300000000025 |
| 300300000025092 | 300300000006408 | 300300000000464 |
| 300300000025093 | 300300000006408 | 300300000000469 |
| 300300000025094 | 300300000006408 | 300300000000495 |
| 300300000025099 | 300300000006409 | 300300000000025 |
| 300300000025096 | 300300000006409 | 300300000000464 |
| 300300000025097 | 300300000006409 | 300300000000470 |
| 300300000025098 | 300300000006409 | 300300000000470 |
+-----------------+-----------------+-----------------+
10 rows in set (0.00 sec)

mysql> select hostgroupid, hostid, groupid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1;
+-----------------+-----------------+-----------------+-------+
| hostgroupid | hostid | groupid | dupes |
+-----------------+-----------------+-----------------+-------+
| 300300000025090 | 300300000006407 | 300300000000025 | 3 |
| 300300000025097 | 300300000006409 | 300300000000470 | 2 |
+-----------------+-----------------+-----------------+-------+
2 rows in set (0.00 sec)

mysql> delete from hosts_groups where hostgroupid IN (select hostgroupid from (select * from hosts_groups hg, (select hostgroupid as parenthostgroupid, hostid as hid, groupid as gid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1) hg_dupes where hg.hostid = hg_dupes.hid and hg.groupid = hg_dupes.gid) res where res.hostgroupid != parenthostgroupid);
Query OK, 3 rows affected (0.01 sec)

mysql> select * from hosts_groups;
+-----------------+-----------------+-----------------+
| hostgroupid | hostid | groupid |
+-----------------+-----------------+-----------------+
| 300300000025090 | 300300000006407 | 300300000000025 |
| 300300000025092 | 300300000006408 | 300300000000464 |
| 300300000025093 | 300300000006408 | 300300000000469 |
| 300300000025094 | 300300000006408 | 300300000000495 |
| 300300000025099 | 300300000006409 | 300300000000025 |
| 300300000025096 | 300300000006409 | 300300000000464 |
| 300300000025097 | 300300000006409 | 300300000000470 |
+-----------------+-----------------+-----------------+
7 rows in set (0.00 sec)

mysql> select hostgroupid, hostid, groupid, count(*) as dupes from hosts_groups group by hostid, groupid;
+-----------------+-----------------+-----------------+-------+
| hostgroupid | hostid | groupid | dupes |
+-----------------+-----------------+-----------------+-------+
| 300300000025090 | 300300000006407 | 300300000000025 | 1 |
| 300300000025092 | 300300000006408 | 300300000000464 | 1 |
| 300300000025093 | 300300000006408 | 300300000000469 | 1 |
| 300300000025094 | 300300000006408 | 300300000000495 | 1 |
| 300300000025099 | 300300000006409 | 300300000000025 | 1 |
| 300300000025096 | 300300000006409 | 300300000000464 | 1 |
| 300300000025097 | 300300000006409 | 300300000000470 | 1 |
+-----------------+-----------------+-----------------+-------+
7 rows in set (0.00 sec)

[groe0286]

You do this as follows:

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

Sorry, that option 2 is not the correct syntax. This is the correct syntax (just for your information):

2) alter table autoreg_host drop foreign key `c_autoreg_host_1`;

[avinash]

Right, so the correct query is:

mysql> delete from hosts_groups where hostgroupid IN (select hostgroupid from (select * from hosts_groups hg, (select hostgroupid as parenthostgroupid, hostid as hid, groupid as gid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1) hg_dupes where hg.hostid = hg_dupes.hid and hg.groupid = hg_dupes.gid) res where res.hostgroupid != parenthostgroupid);

This has been tested:
mysql> select * from hosts_groups;

+-----------------+-----------------+-----------------+
| hostgroupid | hostid | groupid |
+-----------------+-----------------+-----------------+
| 300300000025090 | 300300000006407 | 300300000000025 |
| 300300000025091 | 300300000006407 | 300300000000025 |
| 300300000025095 | 300300000006407 | 300300000000025 |
| 300300000025092 | 300300000006408 | 300300000000464 |
| 300300000025093 | 300300000006408 | 300300000000469 |
| 300300000025094 | 300300000006408 | 300300000000495 |
| 300300000025099 | 300300000006409 | 300300000000025 |
| 300300000025096 | 300300000006409 | 300300000000464 |
| 300300000025097 | 300300000006409 | 300300000000470 |
| 300300000025098 | 300300000006409 | 300300000000470 |
+-----------------+-----------------+-----------------+
10 rows in set (0.00 sec)

mysql> select hostgroupid, hostid, groupid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1;
+-----------------+-----------------+-----------------+-------+
| hostgroupid | hostid | groupid | dupes |
+-----------------+-----------------+-----------------+-------+
| 300300000025090 | 300300000006407 | 300300000000025 | 3 |
| 300300000025097 | 300300000006409 | 300300000000470 | 2 |
+-----------------+-----------------+-----------------+-------+
2 rows in set (0.00 sec)

mysql> delete from hosts_groups where hostgroupid IN (select hostgroupid from (select * from hosts_groups hg, (select hostgroupid as parenthostgroupid, hostid as hid, groupid as gid, count(*) as dupes from hosts_groups group by hostid, groupid having dupes > 1) hg_dupes where hg.hostid = hg_dupes.hid and hg.groupid = hg_dupes.gid) res where res.hostgroupid != parenthostgroupid);
Query OK, 3 rows affected (0.01 sec)

mysql> select * from hosts_groups;
+-----------------+-----------------+-----------------+
| hostgroupid | hostid | groupid |
+-----------------+-----------------+-----------------+
| 300300000025090 | 300300000006407 | 300300000000025 |
| 300300000025092 | 300300000006408 | 300300000000464 |
| 300300000025093 | 300300000006408 | 300300000000469 |
| 300300000025094 | 300300000006408 | 300300000000495 |
| 300300000025099 | 300300000006409 | 300300000000025 |
| 300300000025096 | 300300000006409 | 300300000000464 |
| 300300000025097 | 300300000006409 | 300300000000470 |
+-----------------+-----------------+-----------------+
7 rows in set (0.00 sec)

mysql> select hostgroupid, hostid, groupid, count(*) as dupes from hosts_groups group by hostid, groupid;
+-----------------+-----------------+-----------------+-------+
| hostgroupid | hostid | groupid | dupes |
+-----------------+-----------------+-----------------+-------+
| 300300000025090 | 300300000006407 | 300300000000025 | 1 |
| 300300000025092 | 300300000006408 | 300300000000464 | 1 |
| 300300000025093 | 300300000006408 | 300300000000469 | 1 |
| 300300000025094 | 300300000006408 | 300300000000495 | 1 |
| 300300000025099 | 300300000006409 | 300300000000025 | 1 |
| 300300000025096 | 300300000006409 | 300300000000464 | 1 |
| 300300000025097 | 300300000006409 | 300300000000470 | 1 |
+-----------------+-----------------+-----------------+-------+
7 rows in set (0.00 sec)

Thank you for the queries :-)

I gotta say I cheated; installed Navicat for MySQL, connected to the DB, lookedup the table and removed almost all those dup entries, leaving only one unique behind.

Ran the script again, came into the office this morning, saw that the db upgrade script finished. In the output (I piped to a file) it says:
Patching the database
... patching of the database took 11:53:56

On to the next step. I'll post my results once the total upgrade is finished.

Thanks again for your help and time groe0286!! Your solutions helped me throough the DB patching.

Avi

[groe0286]

Thank you for the queries :-)

I gotta say I cheated; installed Navicat for MySQL, connected to the DB, lookedup the table and removed almost all those dup entries, leaving only one unique behind.

You're welcome, and: whatever works ;-)

On to the next step. I'll post my results once the total upgrade is finished.

Thanks again for your help and time groe0286!! Your solutions helped me throough the DB patching.

Avi

Yes, please let me know what your results are!

And you're welcome again, I'm happy I'm able to contribute to the community!

[avinash]

You're welcome, and: whatever works ;-)



Yes, please let me know what your results are!

And you're welcome again, I'm happy I'm able to contribute to the community!

So, the DB patching procedure ran for 11:53 hours on a 64GB database, successfully. I had already upgraded zabbix-server, zabbix-agentd and zebbix-frontend.

Logged onto zabbix, no issues on a first glance. Need to look into that a bit more.

This is a backup/test machine, so I can't actually run zabbix-server. Now we need to decide if we can have zabbix down for at least 12 hours, or if we'll follow the "how to update major zabbix version with less downtime" procedure posted on this forum.

I'll probably start again, restore a fresh zabbix DB from production and follow the less downtime procedure. See how that works for us.

Cheers and let's keep contributing to the community; it works as designed

Avi

[groe0286]

So, the DB patching procedure ran for 11:53 hours on a 64GB database, successfully. I had already upgraded zabbix-server, zabbix-agentd and zebbix-frontend.

Logged onto zabbix, no issues on a first glance. Need to look into that a bit more.

This is a backup/test machine, so I can't actually run zabbix-server. Now we need to decide if we can have zabbix down for at least 12 hours, or if we'll follow the "how to update major zabbix version with less downtime" procedure posted on this forum.

I'll probably start again, restore a fresh zabbix DB from production and follow the less downtime procedure. See how that works for us.

Cheers and let's keep contributing to the community; it works as designed

Avi

I haven't read that full post, because there's many people given long lists of steps.

I would take a mysqldump of a certain time, import it, upgrade it, then switch to the new database.

You'll still receive your alerts during the upgrade, as the old system is still running, you'll just lose the 12 hours of data collection afterwards.

This is the easiest method in my opinion if you don't care about losing 12 hours of data once.

[pajakh]

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

Finally I found solution. I changed DB engine for graphs table
ALTER TABLE graphs engine = innodb

then I had to change for others tables like: host, images, triggers etc. after that I patched my DB successfully.

[groe0286]

Full instructions

Hi guys,

So I've been playing around with this some more, to give you more advice on the upgrade. I've noticed a common error, I haven't described here yet, this is very important.

In case the upgrade fails, it's not enough to just restore the database using something like this:

# mysql -uroot -p -Dzabbix < /root/backups/full_zbx_db_backup_fully_working.sql

Why not? Well, because this actually merges your broken database with the data in full_zbx_db_backup_fully_working.sql, unless you generated the .sql file with the option to drop and recreate the database first if it already exists.
A common problem that would appear if you do this, would be the following:

[root@aam-netmon-1 mysql]# ./upgrade zabbix
WARNING: backup your database before performing upgrade

This is an UNSUPPORTED Zabbix upgrade script from 1.8 to 2.0 for MySQL
It does the following things:
1. Updates indexes that might require changes;
2. Patches the database from 1.8 schema to 2.0 schema;
3. Adds 'Disabled' and 'Debug' usergroup if any missing;
4. Checks for hosts not belonging to any group and adds them to one if any found.

Usage: pass required MySQL parameters to this script (like database, user, password etc).

Continue ? (y/n) y
Patching the database
ERROR 1050 (42S01) at line 171: Table 'globalvars' already exists

This is because the upgrade script created this globalvars table, and then failed. Now, if you just restore that .sql file, the globalvars table WILL STILL BE THERE!

You need to do this:

mysql> DROP DATABASE zabbix;
Query OK, 104 rows affected (0.98 sec)

mysql> CREATE DATABASE zabbix CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

# mysql -uroot -p -Dzabbix < /root/backups/full_zbx_db_backup_fully_working.sql

And THEN you can try the upgrade again!

[groe0286]

Finally I found solution. I changed DB engine for graphs table
ALTER TABLE graphs engine = innodb

then I had to change for others tables like: host, images, triggers etc. after that I patched my DB successfully.

Yes, that's one of the things I forgot to post here, I advise you to switch from the MyISAM engine to InnoDB for increased concurrency. When working with sophisticated databases, it's almost always advisable to switch to InnoDB, MyISAM will only be faster for very simple databases with low concurrency. For Zabbix, go for InnoDB. The conversion will be relatively fast and painless.

You can convert your whole Zabbix database by running this command:

# mysql -Dzabbix -Ne "SHOW TABLES" | awk '{print "mysql -Dzabbix -Ne \"ALTER TABLE " $1 " ENGINE='\''InnoDB'\''\"";}' | bash

You're strongly advised to run this. Don't be afraid if your database is already running InnoDB, nothing will go wrong. In fact, your tables will be rebuilt, optimizing them.
During this, each table will be locked for a short while. You can always CTRL+C at any time, so don't worry, it's painless and hassle-free.

[groe0286]

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.

Right, so I've checked this out a bit more in detail, it would be advisable to run this for these problems:

mysql> update autoreg_hosts set proxy_hostid = NULL WHERE proxy_hostid = '0';

This takes over the behaviour of pointing to a dummy host in the host table. NULL is allowed to point to nothing. This way, you don't need to create a dummy host, nor do you need to delete all the autoreg_hosts that point to a host with hostid = '0'.

[pajakh]

Yes, that's one of the things I forgot to post here, I advise you to switch from the MyISAM engine to InnoDB for increased concurrency. When working with sophisticated databases, it's almost always advisable to switch to InnoDB, MyISAM will only be faster for very simple databases with low concurrency. For Zabbix, go for InnoDB. The conversion will be relatively fast and painless.

You can convert your whole Zabbix database by running this command:

# mysql -Dzabbix -Ne "SHOW TABLES" | awk '{print "mysql -Dzabbix -Ne \"ALTER TABLE " $1 " ENGINE='\''InnoDB'\''\"";}' | bash

I didn't know that we use MyISAM, I manage only zabbix gui. I was sure we use InnoDB.
I created test lab to prepare procedure to upgrade to Zabbix 2.0, set up file_per_table and then set up partitioning instead housekeeping. But I think I can do this only on InnoDB engine, so before I do this, I have to convert all tables to use InnoDB right?