Problem upgrading from Zabbix 1.1 to 1.4 postgres DB schema wrong

[dan.searle]

I have followed the instructions in the Zabbix 1.4 manual for upgrading from a zabbix 1.1 install. However, when I try to start the zabbix_server, I get the following error in the server log and the server dies....

22355:20070618:110049 Query::select eventid,value,clock from events where source=0 and object=0 and objectid=13058 order by clock desc limit 2
22355:20070618:110049 Query failed:PGRES_FATAL_ERROR:ERROR: column "object" does not exist

I have checked the schema in zabbix-1.4/upgrades/dbpatches/1.4/postgresql and the entry for table "events" does not mention an "object" or "objectid" column.

What's wrong here? Is the upgrade schema for postgresql broken?

Regards, Dan...

[Alexei]

Yes, it seems that the patch is broken for table 'events'. I fixed it:

CREATE TABLE events (
		eventid		 bigint		  DEFAULT '0'	 NOT NULL,
		source		  integer		 DEFAULT '0'	 NOT NULL,
		object		  integer		 DEFAULT '0'	 NOT NULL,
		objectid				bigint		  DEFAULT '0'	 NOT NULL,
		clock		   integer		 DEFAULT '0'	 NOT NULL,
		value		   integer		 DEFAULT '0'	 NOT NULL,
		acknowledged			integer		 DEFAULT '0'	 NOT NULL,
		PRIMARY KEY (eventid)
) with OIDS;
CREATE INDEX events_1 on events (object,objectid,clock);
CREATE INDEX events_2 on events (clock);

insert into events select alarmid,0,0,triggerid,clock,value,acknowledged from alarms;
drop table alarms;

[dan.searle]

It appears there is another problem with the postgres schema, when I try to run the zabbix_server 1.4 with an upgraded database from 1.1.x I get the following error in the server log file....

 18029:20070629:103925 Query::select actionid,evaltype,status,eventsource from actions where status=0 and eventsource=0 and actionid>=100000000000000*0 and ac
tionid<=(100000000000000*0+99999999999999)
 18029:20070629:103925 Query failed:PGRES_FATAL_ERROR:ERROR:  relation "actions" does not exist

 17987:20070629:103925 One child process died. Exiting ...
 17987:20070629:103927 ZABBIX Server stopped

Can someone please fix this postgres schema problem!

[Alexei]

I clearly see creation of the table in PostgreSQL patch. Something is wrong on your side I think.

[dan.searle]

There is what looks like a typo in the file:

zabbix-1.4/upgrades/dbpatches/1.4/postgresql/patch.sql line 17:

        actionid                bigintd         DEFAULT '0'     NOT NULL,

Should this read:

        actionid                bigint         DEFAULT '0'     NOT NULL,

?????

[dan.searle]

I've debugged the script and found a few errors in it now, here's a diff...

--- zabbix-1.4/upgrades/dbpatches/1.4/postgresql/patch.sql      2007-05-29 13:53:30.000000000 +0100
+++ zabbix-1.4-new/upgrades/dbpatches/1.4/postgresql/patch.sql  2007-06-29 11:36:59.000000000 +0100
@@ -14,7 +14,7 @@
 drop table acknowledges;
 alter table acknowledges_tmp rename to acknowledges;
 CREATE TABLE actions_tmp (
-        actionid                bigintd         DEFAULT '0'     NOT NULL,
+        actionid                bigint         DEFAULT '0'     NOT NULL,
         name                    varchar(255)            DEFAULT ''      NOT NULL,
         eventsource             integer         DEFAULT '0'     NOT NULL,
         evaltype                integer         DEFAULT '0'     NOT NULL,
@@ -42,7 +42,7 @@
 alter table operations drop scripts_tmp;

 drop table actions;
-alter table actions_tmp rename actions;
+alter table actions_tmp rename to actions;
 CREATE TABLE alerts_tmp (
        alertid         bigint  DEFAULT '0'     NOT NULL,
        actionid        bigint  DEFAULT '0'     NOT NULL,
@@ -167,17 +167,19 @@
 CREATE TABLE events (
        eventid         bigint  DEFAULT '0'     NOT NULL,
        source          integer DEFAULT '0'     NOT NULL,
-       sourceid        bigint  DEFAULT '0'     NOT NULL,
+               object            integer                DEFAULT '0'     NOT NULL,
+               objectid                                bigint            DEFAULT '0'    NOT NULL,
        clock           integer DEFAULT '0'     NOT NULL,
        value           integer DEFAULT '0'     NOT NULL,
        acknowledged    integer         DEFAULT '0'     NOT NULL,
        PRIMARY KEY (eventid)
-);
+) with OIDS;
 CREATE INDEX events_1 on events (object,objectid,clock);
 CREATE INDEX events_2 on events (clock);

-insert into events select alarmid,0,triggerid,clock,value,acknowledged from alarms;
+insert into events select alarmid,0,0,triggerid,clock,value,acknowledged from alarms;
 drop table alarms;
+
 CREATE TABLE functions_tmp (
        functionid      bigint  DEFAULT '0'     NOT NULL,
        itemid          bigint  DEFAULT '0'     NOT NULL,
@@ -343,7 +345,7 @@

 insert into hosts_groups_tmp select NULL,hostid,groupid from hosts_groups;
 drop table hosts_groups;
-alter table hosts_groups_tmp rename hosts_groups;
+alter table hosts_groups_tmp rename to hosts_groups;


 CREATE TABLE hosts_groups_tmp (

There are a few more not-null constraint errors when the patch is applied, but I'm choosing to ignore these, let me know if you want more detail....

Dan...

[dan.searle]

For your information the following other errors occurred while patching the old 1.1 database...

...
ERROR:  null value in column "hostgroupid" violates not-null constraint
...
ERROR:  relation "hosts_groups_groups_1" already exists
...
ERROR:  relation "hosts_templates_1" already exists
...
ERROR:  relation "items_applications_1" already exists
...
ERROR:  null value in column "profileid" violates not-null constraint
...
ERROR:  relation "profiles_1" already exists
...
ERROR:  null value in column "triggerdepid" violates not-null constraint
...
ERROR:  relation "trigger_depends_1" already exists
ERROR:  relation "trigger_depends_2" already exists
...
ERROR:  relation "users_groups_1" already exists

I'm not sure of the implications of these errors, or if they are spurious.

[Alexei]

All reported issues are fixed. It is already in SVN code.