upgrade 1.1.6 -> 1.4.6 not working(error in patch.sql)

[tdomhan]

I have an zabbix 1.1.6 system which i would like to see upgraded to the newest version.

in the first step I wanted to upgrade to 1.4.6. therefore I dumped the current mysql database. imported the dump on some other machine and tried to apply zabbix-1.4.6/upgrades/dbpatches/1.4/mysql/patch.sql.

this takes some while...after 5 hours I get the following error:
"ERROR 1050 (42S01) at line 618: Table 'node_cksum' already exists"


the table node_cksum that already exists looks like the following:
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| cksumid | bigint(20) unsigned | NO | PRI | 0 | |
| nodeid | bigint(20) unsigned | NO | MUL | 0 | |
| tablename | varchar(64) | NO | | | |
| fieldname | varchar(64) | NO | | | |
| recordid | bigint(20) unsigned | NO | | 0 | |
| cksumtype | int(11) | NO | | 0 | |
| cksum | char(32) | NO | | | |
+-----------+---------------------+------+-----+---------+-------+

and the one the script'd like to create:
CREATE TABLE node_cksum (
smtp_helo varchar(255) DEFAULT '' NOT NULL,
smtp_email varchar(255) DEFAULT '' NOT NULL,
exec_path varchar(255) DEFAULT '' NOT NULL,
gsm_modem varchar(255) DEFAULT '' NOT NULL,
username varchar(255) DEFAULT '' NOT NULL,
passwd varchar(255) DEFAULT '' NOT NULL,
PRIMARY KEY (mediatypeid)
) ENGINE=InnoDB;

after dropping the table I get the same problem with "node_configlog" and "nodes" and "escalations", the latter one during 1.4->1.6 upgrade.
I dropped the tables as the where all empty.

is this the way it should be?


for the rest of the upgrade there weren't any more errors. the zabbix_server runs without any problems. but the webinterface makes some problems.
this is how it looks like:
http://img256.imageshack.us/img256/3438/zabbixerror.th.png (http://img256.imageshack.us/i/zabbixerror.png/)
I can successfully log in, though after that I can do nothing.

I can manually go to /charts.php but there will not be shown any graphs. .... any idea?

[richlv]

upgrade process should be pretty smooth.
are you sure that's a clean 1.1 database ? :)
i'm asking because there are no node* tables in 1.1 schema at all

oh, btw, that screenshot is so small it's useless

[tdomhan]

upgrade process should be pretty smooth.
are you sure that's a clean 1.1 database ?
i'm asking because there are no node* tables in 1.1 schema at all
no. it was not me who installed the zabbixserver in the first place.
though the tables where all empty. so I dropped them. The rest went smooth, beside the fact that the web frontend is not working now :/


oh, btw, that screenshot is so small it's useless
oh sorry, that was not the intended size.
http://img256.imageshack.us/img256/3438/zabbixerror.th.png (http://img256.imageshack.us/i/zabbixerror.png/)

[richlv]

glad to hear about the upgrade.
as for the frontend, i suspect that your user simply doesn't have correct user type/permissions. as you, hehe, carefull blanked out username, i can't give you exact query, but try something like this in database client :

update zabbix.users set type='3' where alias='your_blanked_username';

that will set your user to be superadmin and basically have full permissions on zabbix.
oh, and always have a backup handy before editing the db :)

[tdomhan]

ok. great advice, thank you :D

it works for me now. though other users have the same problem.

the type of all other users is 1. they all get the same screen, as shown on the screenshot.

in 1.1.x there was no attribute type?
the permissions system has change from 1.1.x -> 1.6? is there a way to migrate the permissions=

[tdomhan]

mh in the patch.sql of 1.4.6:
the import of the current user rights is commented out:

CREATE TABLE rights_tmp (
rightid bigint unsigned DEFAULT '0' NOT NULL,
groupid bigint unsigned DEFAULT '0' NOT NULL,
type integer DEFAULT '0' NOT NULL,
permission integer DEFAULT '0' NOT NULL,
id bigint unsigned ,
PRIMARY KEY (rightid)
) ENGINE=InnoDB;
CREATE INDEX rights_1 on rights_tmp (groupid);

-- insert into rights_tmp select * from rights;
drop table rights;
alter table rights_tmp rename rights;

is this intended?

[richlv]

upgrade to 1.4 is setting user type to 3 for user 'admin'.
you shouldn't set type to 3 for other users, unless they all ar "zabbix super admins" :)
permission scheme... i think it changed from 1.1 -> 1.4 - now permissions can only be assigned to host groups per user groups.
to be honest, i don't really remember what kind of upgrade/migration was provided at that point, although i'd imagine that to be quite hard, given the old per-element permission scheme

[tdomhan]

ok another problem. the values of "keep trends(in days)"(table: items.trends) are incredible high after the upgrade.
here an excerpt from the DB:

+------------+
| trends |
+------------+
| 1211354897 |
| 1207833318 |
| 1205841499 |
| 1201261042 |
| 1228385903 |
| 1207834104 |
| 1230483986 |
| 1207833147 |
| 1204550248 |
| 1204636889 |
| 1210859552 |
| 1210859558 |
| 1210859559 |
| 1210859560 |
| 1210859501 |
| 1210859502 |
| 1210859563 |
| 1201261064 |
| 1204636905 |
| 1206435946 |
| 1206436367 |
the column data type seems to be changed from int(4) to int(11). though the data type of items.history changed too. but the values of items.history look sane.


ok it seems like "nextcheck" is mapped into the trends column. why am I the only one with this problem??

[tdomhan]

I created a bugreport:
https://support.zabbix.com/browse/ZBX-1017

didn't anyone else have this problem before?