database upgrade from 1.8.10 to 2.0 fails

[groe0286]

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?

Regardless of whether you want to use table partioning or not, you have to use InnoDB. Well OK, you don't HAVE to, but seriously, MyISAM is not an option.

It does not support transactions, so the following (pseudo-code, not real MySQL syntax):

START TRANSACTION;
ADD HOST 1;
ADD HOST 2;
ADD HOST 3;
ADD HOST 1;
ADD HOST 4;
COMMIT TRANSACTION;

If the second addition of host 1 would fail (because of any error, such as host already exists as we've already created it), the transaction will have been executed half-way when you use MyISAM, meaning hosts 1, 2 and 3 would exist, host 4 would not.

If this happens on InnoDB, none of the hosts will have been added, as the transaction is an atomic operation, so restarting it is safe.

I'm sure you can see why this can seriously mess up the database, it's probably why so many people have issues.

[dougbee]

Thanks for your help in this thread, groe0286. Based on my original problem (post 9 of this thread) I had to modify line 1717 of patch.sql:

SET foreign_key_checks=0;
ALTER TABLE sysmaps ADD CONSTRAINT c_sysmaps_1 FOREIGN KEY (backgroundid) REFERENCES images (imageid);
SET foreign_key_checks=1;

One thing that's missing is the new 2.0 images icons. The upgrade docs don't seem to cover the installation. And running images.sql gives this error:

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

That seems to be expected since it's not a new installation. Any thoughts? I have custom images in my 1.8 installation, I don't want to lose those.

[groe0286]

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

That seems to be expected since it's not a new installation. Any thoughts? I have custom images in my 1.8 installation, I don't want to lose those.

That would be no problem. The trick is to increase the imageid's in the import file to be higher than the ones you currently have.

Do a:

mysql> select max(imageid) from images;
+-----------------+
| max(imageid) |
+-----------------+
| 200 |
+-----------------+
1 row in set (0.00 sec)

OK, so remember this number, in my case it's 200.

Let's now do the images import in an alternative database. Do this:

mysql> CREATE DATABASE zabbix_temp;
[root@test]# mysql -Dzabbix_temp < images.sql

Your images should now have been loaded into table images in database zabbix_temp. Now increase the values of imageid by the value 200, that we've retrieved earlier:

mysql> USE zabbix_temp;
mysql> ALTER TABLE images DROP PRIMARY KEY; (you need to drop the key, as it's unique, and if you add 200, this may conflict if there already is a row 201, but don't worry, it's only a temp table with data, in your original table, the key will be retained).
mysql> UPDATE images SET imageid = imageid + 200;
mysql> RENAME TABLE zabbix_temp.images TO zabbix.images_temp; (put the table in the original database)
mysql> INSERT INTO images (SELECT * FROM images_temp); (add the rows to your images table)
mysql> DROP DATABASE zabbix_temp;

And you're done :-)

[dougbee]

Thanks groe0286! That worked, with a couple of modifications. I had to create the images table for zabbix_temp, since images.sql only inserts the data. I pulled this from schema.sql:

CREATE TABLE `images` (
`imageid` bigint unsigned NOT NULL,
`imagetype` integer DEFAULT '0' NOT NULL,
`name` varchar(64) DEFAULT '0' NOT NULL,
`image` longblob NOT NULL,
PRIMARY KEY (imageid)
) ENGINE=InnoDB;

And then before inserting the new images I had to "use zabbix" to switch the database. So it went something like this (my max imageid was 26)

mysql> use zabbix_temp;
mysql> ALTER TABLE images DROP PRIMARY KEY;
mysql> UPDATE images SET imageid = imageid + 26;
mysql> RENAME TABLE zabbix_temp.images TO zabbix.images_temp;
mysql> use zabbix;
mysql> INSERT INTO images (SELECT * FROM images_temp);
mysql> DROP TABLE images_temp;
mysql> DROP DATABASE zabbix_temp;

Dougbee

[groe0286]

Thanks groe0286! That worked, with a couple of modifications. I had to create the images table for zabbix_temp, since images.sql only inserts the data. I pulled this from schema.sql:

Ah, yes, my mistake. I actually didn't try this, it's coming from my head lol.

And then before inserting the new images I had to "use zabbix" to switch the database.

Ugh, yeah, forgot that one too ;-)

Thanks groe0286! That worked, with a couple of modifications.

Glad I could be of help. Zabbix really is great software :-D I'm a big fan!

[kalleinz]

Upgrade successfull

Hello

All I had to do was to import the mysqldump of the old database.

The zabbix_server.conf is now in /usr/local/etc/ instead of /etc/zabbix/.

The rest was more or less straightforward.

I did not need the patch.sql and upgrade.sql scripts.

Hope this helps.

Regards,
Kalleinz