database patch for trunk

[richlv]

users that follow trunk sometimes might wish to keep their database - but that's not possible if there are schema updates etc. no official patches are released, so i'd like to start this thread for that purpose.

and the first simple one:
revision 7207 changed schema to add per user configurable row count and dropdown options. if you had installed trunk, older than revision 7207 and you want to upgrade to trunk, revision 7207 or later, following patch might help:

alter table users add rows_per_page integer default '50' NOT NULL;
alter table config add dropdown_first_entry integer default '1' NOT NULL;
alter table config add dropdown_first_remember integer default '1' NOT NULL;

save this as some file, then simply do 'mysql zabbix < somefile'
i'd appreciate pointing out of mistakes and maybe posting patches for future changes here

[richlv]

few indexes to get up to revision 7266

CREATE INDEX services_1 on services (triggerid);
CREATE UNIQUE INDEX history_log_2 on history_log (itemid,id);
CREATE UNIQUE INDEX history_text_2 on history_text (itemid,id);

[richlv]

revision 7294 :

CREATE TABLE opmediatypes (
        opmediatypeid    bigint unsigned         DEFAULT '0'     NOT NULL,
        operationid      bigint unsigned         DEFAULT '0'     NOT NULL,
        mediatypeid      bigint unsigned         DEFAULT '0'     NOT NULL,
        PRIMARY KEY (opmediatypeid)
) type=InnoDB;

[richlv]

revision 7302 :

alter table history_log   add logeventid integer DEFAULT '0' NOT NULL;
alter table proxy_history add logeventid integer DEFAULT '0' NOT NULL;

[richlv]

revision 7330 :

alter table usrgrp add api_access integer DEFAULT '0' NOT NULL after gui_access;
update ids set nextid=nextid+1 where table_name='usrgrp' and field_name='usrgrpid';
INSERT INTO usrgrp (usrgrpid,name,gui_access,api_access,users_status) SELECT nextid,'API access','0','1','0' from ids where ids.table_name='usrgrp' and ids.field_name='usrgrpid';

revision 7335 :

alter table dservices add dcheckid bigint unsigned DEFAULT '0' NOT NULL;
alter table proxy_dhistory add dcheckid bigint unsigned DEFAULT '0' NOT NULL after druleid;
create index dchecks_1 on dchecks (druleid);
create index dhosts_1 on dhosts (druleid);
create index dservices_1 on dservices (dhostid);
create index dservices_2 on dservices (dcheckid);

[richlv]

revision 7351 :

alter table dchecks add snmpv3_securityname varchar(64) DEFAULT '' NOT NULL;
alter table dchecks add snmpv3_securitylevel integer DEFAULT '0' NOT NULL;
alter table dchecks add snmpv3_authpassphrase varchar(64) DEFAULT '' NOT NULL;
alter table dchecks add snmpv3_privpassphrase varchar(64) DEFAULT '' NOT NULL;

[richlv]

revision 7372 :

create index hosts_groups_groups_2 on hosts_groups (groupid);

[richlv]

revision 7463 :

alter table groups add internal integer default '0' NOT NULL;

[richlv]

REWRITE.
warning, sql versions pretty much untested.
revision 7465

sql versions 1 and 2 should have identical results if there is no existing group with id 5 and group nextid is 4 (because it's actually max current id...).

1. original version, with shell usage

this time a very ugly bash script...

#!/bin/bash

echo "alter table zabbix.config add discovery_groupid bigint unsigned DEFAULT '0' NOT NULL;" | mysql -N
echo "update zabbix.config set discovery_groupid='5' where configid='1';" | mysql -N

[[ "$(echo "select groupid from zabbix.groups where groupid='5';" | mysql -N)" ]] && {
        NEXTID=$[$(echo "select groupid from zabbix.groups order by groupid desc limit 1;" | mysql -N)+1]
        echo "update zabbix.groups set groupid=\"$NEXTID\" where groupid='5';" | mysql -N
        echo "update zabbix.ids set nextid=\"$NEXTID\" where table_name='groups';" | mysql -N
        echo "update zabbix.hosts_groups set groupid=\"$NEXTID\" where groupid='5';" | mysql -N
        echo "update zabbix.maintenances_groups set groupid=\"$NEXTID\" where groupid='5';" | mysql -N
        echo "update zabbix.scripts set groupid=\"$NEXTID\" where groupid='5';" | mysql -N
        echo "update zabbix.rights set id=\"$NEXTID\" where id='5';" | mysql -N
}

echo "insert into zabbix.groups VALUES (5,'Discovered Hosts',1);" | mysql -N

2. sql only version 1
making sure that discovery group gets groupid 5, to keep in line with default schema.

alter table config add discovery_groupid bigint unsigned DEFAULT '0' NOT NULL;
update zabbix.config set discovery_groupid='5' where configid='1';

update ids set nextid=nextid+1 where table_name='groups' and field_name='groupid';
update zabbix.groups set groupid=(select nextid from zabbix.ids where table_name='groups' and field_name='groupid') where groupid='5';
update zabbix.hosts_groups set groupid=(select nextid from zabbix.ids where table_name='groups' and field_name='groupid') where groupid='5';
update zabbix.maintenances_groups set groupid=(select nextid from zabbix.ids where table_name='groups' and field_name='groupid') where groupid='5';
update zabbix.scripts set groupid=(select nextid from zabbix.ids where table_name='groups' and field_name='groupid') where groupid='5';
update zabbix.rights set id=(select nextid from zabbix.ids where table_name='groups' and field_name='groupid') where groupid='5';
insert into zabbix.groups VALUES (5,'Discovered Hosts',1);

3. sql only version 2
adds discovered hosts group as the next group, leaving group id 5 alone

alter table config add discovery_groupid bigint unsigned DEFAULT '0' NOT NULL;
update ids set nextid=nextid+1 where table_name='groups' and field_name='groupid';

update zabbix.config set discovery_groupid=(select nextid from zabbix.ids where table_name='groups' and field_name='groupid') where configid='1';
insert into zabbix.groups (groupid,name,internal) select nextid,'Discovered Hosts','1' from zabbix.ids where table_name='groups' and field_name='groupid';

[richlv]

revision 7497:

alter table dhosts drop index dhosts_1;
create index dhosts_1 on dhosts (druleid,ip);
alter table dservices drop index dservices_1;
create index dservices_1 on dservices (dhostid,type,key_,port);
create index httptest_2 on httptest (name);
create index httptest_3 on httptest (status);
create index actions_1 on actions (eventsource,status);
create index escalations_2 on escalations (status,nextcheck);

[richlv]

revision 7529 :

create index rights_2 on rights (id);

[richlv]

revision 7547

alter table usrgrp add debug_mode integer default '0' NOT NULL;

revision 7553

CREATE TABLE user_history (
        userhistoryid   bigint unsigned         DEFAULT '0'     NOT NULL,
        userid          bigint unsigned         DEFAULT '0'     NOT NULL,
        title1          varchar(255)            DEFAULT ''      NOT NULL,
        url1            varchar(255)            DEFAULT ''      NOT NULL,
        title2          varchar(255)            DEFAULT ''      NOT NULL,
        url2            varchar(255)            DEFAULT ''      NOT NULL,
        title3          varchar(255)            DEFAULT ''      NOT NULL,
        url3            varchar(255)            DEFAULT ''      NOT NULL,
        title4          varchar(255)            DEFAULT ''      NOT NULL,
        url4            varchar(255)            DEFAULT ''      NOT NULL,
        title5          varchar(255)            DEFAULT ''      NOT NULL,
        url5            varchar(255)            DEFAULT ''      NOT NULL,
        PRIMARY KEY (userhistoryid)
) type=InnoDB;
CREATE UNIQUE INDEX user_history_1 on user_history (userid);

[richlv]

revision 7594

alter table config add max_in_table integer default '50' NOT NULL;

[richlv]

revision 7691 :

alter table config add search_limit integer default '1000' NOT NULL;

[richlv]

revision 7732 :

CREATE TABLE autoreg_host (
        autoreg_hostid          bigint unsigned         DEFAULT '0'     NOT NULL,
        proxy_hostid            bigint unsigned         DEFAULT '0'     NOT NULL,
        host            varchar(64)             DEFAULT ''      NOT NULL,
        PRIMARY KEY (autoreg_hostid)
) type=InnoDB;
CREATE UNIQUE INDEX autoreg_host_1 on autoreg_host (proxy_hostid,host);
CREATE TABLE proxy_autoreg_host (
        id              bigint unsigned                 NOT NULL        auto_increment unique,
        clock           integer         DEFAULT '0'     NOT NULL,
        host            varchar(64)             DEFAULT ''      NOT NULL,
        PRIMARY KEY (id)
) type=InnoDB;
CREATE INDEX proxy_autoreg_host_1 on proxy_autoreg_host (clock);