Time to upgrade database from 1.6 to 1.8?

[grash]

Hi Guys !!

I am upgrading Zabbix from 1.6 to 1.8. In the process of database upgrading I've run the script "zabbix-1.8/upgrades/dbpatches/1.8/mysql/upgrade".

It is running now near 1 hour, with the messages:
Dropping indexes that might need re-creation
Converting database to UTF-8

The database size is near 3 GB.

In the instructions says "Database upgrade should take around 10-15 minutes, for PostgreSQL it may take several hours or more because of conversion of existing historical data. It is recommended to test the upgrade procedure in a non-production environment."

Is this time normal? How long time can upgrade take?

Regards,


Olaf Marcos

[grash]

Hi again !!

After several hours...

| 74118 | root | localhost | zabbix | Query | 22778 | copy to tmp table | ALTER TABLE history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

22778 seg to convert history table??? 6 hours and half... ??

Estimated time?

Any ideas ???

Regards,


Olaf Marcos

[grash]

After several hours it finished with these messages...

Dropping indexes that might need re-creation
Converting database to UTF-8
Patching the database
ERROR 1050 (42S01) at line 2: Table 'auditlog_details' already exists
Failed to patch Zabbix database. Restore from backup

I have launched the script before and stopped cause I thought it was hanged.

Is it updated correctly?

And if not, how to update it?

Thanks in advance,


Olaf Marcos

[simonc]

I think you should restore from backup and rerun the script to be sure.

[grash]

Thanks Simonc.

The problem is that the script take 20 hours to finish and I don't want to waste 20 hours to be at the same point.

Now it has to be a solution to apply the sql patch without restore the entire database, convert to UTF-8 and so on...

Any advice???

I have seen some similiar problems... http://www.zabbix.com/forum/showthread.php?p=55570

Best regards,

Olaf Marcos

[simonc]

Maybe you should look in the sql patch and do each step one by one to verify that your database is properly patched ?
I don't think it takes too much time, the patch isn't very big.

[antani]

Thanks Simonc.

The problem is that the script take 20 hours to finish and I don't want to waste 20 hours to be at the same point.

Now it has to be a solution to apply the sql patch without restore the entire database, convert to UTF-8 and so on...

Any advice???

Restore seems the best way. Instead of using the upgrade script, try to execute commands one by one.
Make a backup after utf-8 conversion so you can restore to any step if needed.

If you are sure that the conversion is correct than you can look for the latest patch instruction that has been executed and delete every row before that.

[Firm]

It took about 15 minutes to perform the upgrade from 1.6 to 1.8 on 13Gb postgresql db (being running in xen virtual env). But it was already in utf8.

Try to dump db, drop it, create with -E UTF8 option, restore db and run upgrade script.

[grash]

It took about 15 minutes to perform the upgrade from 1.6 to 1.8 on 13Gb postgresql db (being running in xen virtual env). But it was already in utf8.

Try to dump db, drop it, create with -E UTF8 option, restore db and run upgrade script.

Thanks Firm !!!

I created the DB with "CHARACTER SET utf8 COLLATE utf8_general_ci", restore and rerruning the script went smoothly...

Thanks to all...

Best regards,


Olaf

[alixen]

Why convert to utf8 tables that contain only numeric data ?

Hi,

It took a long time (around 2 hours) to upgrade from 1.6 to 1.8 a 6 Gb MySQL database.

However, tables that seem to have taken most of time are history and history_uint. Since these tables contain only numeric data, I don't see why they need to be converted to utf8.

Is it possible to change upgrade script so that these tables are not converted ?

Regards,
Alixen