Database schema problems 1.3.1

[popek90]

They are problems with adding multiple values to service_alarms, services_time tables. I received error 'Duplicate entry '1' for key 1 [1062]'.
To resolve problem add auto_increment to primary keys in tables.
I've made changes on schema exported using mysqldump utility.
Use carefuly! schema exported using mysql deletes table before inserting a new one.


DROP TABLE IF EXISTS `service_alarms`;
CREATE TABLE `service_alarms` (
- `servicealarmid` bigint(20) unsigned NOT NULL default '0',
+ `servicealarmid` bigint(20) unsigned NOT NULL auto_increment,
`serviceid` bigint(20) unsigned NOT NULL default '0',
`clock` int(11) NOT NULL default '0',
`value` int(11) NOT NULL default '0',
@@ -748,8 +748,8 @@

DROP TABLE IF EXISTS `services_times`;
CREATE TABLE `services_times` (
- `timeid` bigint(20) unsigned NOT NULL default '0',
+ `timeid` bigint(20) unsigned NOT NULL auto_increment,
`serviceid` bigint(20) unsigned NOT NULL default '0',
`type` int(11) NOT NULL default '0',
`ts_from` int(11) NOT NULL default '0',
`ts_to` int(11) NOT NULL default '0'

[Alexei]

Thanks for reporting this! Note that the patch is not correct. While I expect no problems running standalone ZABBIX server, it cannot be used in distributed setup.

[atrice]

Eventid and alertid auto-increment

I am running zabbix 1.3.1 on RHEL4, x86_64, with MySQL-server-standard-5.0.27-0.rhel4 from the rpm provided by MySQL. I had query fail errors in the server log about duplicate keys in the events and alerts. I think there is a problem with mysql 5 that prevents auto_increment from being set. To fix it, I used alter_table as shown here. Basically drop the eventid column and recreate it with auto_increment set. Do the same thing for alerts, but drop and recreate the alertid column.

mysql> ALTER TABLE events DROP COLUMN eventid;
mysql> ALTER TABLE events ADD eventid INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (eventid);