I updated zabbix from version 2.2 (on centos 6.7 and myisam) to 3,0,5 (on Debian 7 and innodb). But I got a lot of problems related to the database structure. I tried to do a dump of the structure with newly installed zabbix 3.0.5 and import an existing database but got an error in start zabbix-server:
I tried to first import the structure and then dump the database. But I got the error:
If I delete a table application_prototype:
For example of my problem:
How I can fix structure of database?
can not use database" zabbix ": Zabbix server can not work with a Zabbix proxy database
[Z3005] query failed: [1050] Table 'application_prototype' already exists [create table application_prototype (
application_prototypeid bigint unsigned not null,
itemid bigint unsigned not null,
templateid bigint unsigned,
name varchar (255) default '' not null,
primary key (application_prototypeid)
) Engine = innodb]
application_prototypeid bigint unsigned not null,
itemid bigint unsigned not null,
templateid bigint unsigned,
name varchar (255) default '' not null,
primary key (application_prototypeid)
) Engine = innodb]
[Z3005] query failed: [1005] Can not create table 'zabbix # sql-468a_14e1.' (Errno: 121) [alter table application_prototype add constraint c_application_prototype_1 foreign key (itemid) references items (itemid) on delete cascade]
mysql> ALTER TABLE `acknowledges` ADD FOREIGN KEY ( `userid` ) REFERENCES `zabbix`.`users` (
-> `userid`
-> ) ON DELETE CASCADE ON UPDATE RESTRICT ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`#sql-468a_2580`, CONSTRAINT `#sql-468a_2580_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE)
-> `userid`
-> ) ON DELETE CASCADE ON UPDATE RESTRICT ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`#sql-468a_2580`, CONSTRAINT `#sql-468a_2580_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE)