zabbix 2.2 db migration and upgrade, need help.

[vrolok]

Hi,

I'm trying to migrate of mysql to postgresql, at the same time upgrading zabbix 2.0.8 to 2.2. I ran into issues with the db upgrade.

I performed old database migration using py-mysql2pgsql tool. Checked and everything did look fine in GUI. My next step was to install zabbix 2.2 from the repository and let zabbix-server update a 'new' postgresql database. As soon as I did that I got this in the logs:

5516:20131125:200022.446 completed 6% of database upgrade
5516:20131125:200022.450 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: index "httptest_2" does not exist
[drop index httptest_2]
5516:20131125:200022.450 database upgrade failed

I'm not a db admin, dunno if this can be fixed somehow. Thanks!

[blastik]

I've had the same issue and I had to start all the upgrade process over again. I've to say I run Zabbix from a VM so I've made the upgrade test in a new VM so it was easy.

This usually happens when you stop the new version of Zabbix server process before it finishes upgrading the Zabbix database. The second time I've made the upgrade I checked the Zabbix server logs to see the DB upgrade process which is handy.

[pietro54]

Hi,
I got same problem on zabbix 2.2.1
Steps I do:
*Export db form old instantion.
*copy to new one.
*Import dump to zabbix database.
*start zabbix and tail logs

27888:20140205:224625.780 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: index "httptest_2" does not exist
[drop index httptest_2]
27888:20140205:224625.780 query [drop index httptest_2] failed, setting transaction as failed
27888:20140205:224625.780 query [txnlev:1] [rollback;]
27888:20140205:224625.780 database upgrade failed
27888:20140205:224625.780 End of DBcheck_version():FAIL

What does it mean?

[pietro54]

Hello,
any one can help?

I try to reimport this big database 10times, i try to drop httptest and recreate from script, that doesnt help much becouse another thing goes down:

13692:20140207:065844.365 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: ograniczenie "c_httptest_1" relation "httptest" [alter table httptest drop constraint c_httptest_1]
13692:20140207:065844.365 query [alter table httptest drop constraint c_httptest_1] failed, setting transaction as failed
13692:20140207:065844.365 query [txnlev:1] [rollback;]
13692:20140207:065844.365 database upgrade failed
13692:20140207:065844.365 End of DBcheck_version():FAIL

Im in dead point, maybe any one can help?

[dale]

Be aware that the db migration script is probably not going to work 100% out of the box.

I would separate this and perform the Zabbix upgrade on your existing database, make sure that is proper, and then do your MySQL to PostgreSQL migration.

FWIW here is the process I used for 1.8 Mysql to 2.0 PostgreSQL.

https://www.zabbix.org/wiki/Docs/howto/upgrade/Upgrade_Zabbix_1.8_to_2.0_and_Migrate_Mysql_to_Postgresql

Unfortunately I decided to leave my history behind during the upgrade. YMMV

[pietro54]

I use postgres, this is migration from postgres to postgres, not from mysql to postgres.

In zabbix 2.2 db upgrade is automated, and as I believe i can`t interfere in to this process.
If im wrong, maybe you can tell me what scripts and in what order I should run them?

[nerijus]

I updated 2.0 to 2.2, no db change. Server was unable to start. From the log:

Starting Zabbix Server. Zabbix 2.2.4 (revision 46772).
...
current database version (mandatory/optional): 02010000/02010000
required mandatory version: 02020000
optional patches were found
starting automatic database upgrade
completed 0% of database upgrade
completed 1% of database upgrade
completed 2% of database upgrade
completed 3% of database upgrade
completed 4% of database upgrade
completed 5% of database upgrade
completed 6% of database upgrade
[Z3005] query failed: [1091] Can't DROP 'httptest_2'; check that column/key exists [drop index httptest_2 on httptest]
database upgrade failed

I was able to start zabbix server and successfully upgrade database by issuing the following mysql command:
CREATE INDEX httptest_2 on httptest (name);

It would be nice if database upgrade did not fail if index which is going to be dropped does not exist.

[tpramos]

Hi, I fix this error creating a index.

create index httptest_2 on httptest (name);