Import Data From Zabbix 2.2.7 to 2.4.1

[Viewer]

For whom it may concern... I solved the problem.

I have this problem.
Please , write step by step instruction

[Shurman]

I will not create step-by-step instructions, since I do not have that database handy anymore. However - what is confusing you in my solution?

What is your storage engine? Do you have other mysql databases on this server, or is zabbix DB the only one?

[Viewer]

What is your storage engine? Do you have other mysql databases on this server, or is zabbix DB the only one?

I have zabbix 2.2.5 , Opensuse 11 32bit, and want upgrade.
I create new server - Centos7 64bit + Zabbix2.4.4(rpm from repo.zabbix.com)
I backup database on old server, and restore on new( mysqldump)
start zabbix-server
zabbix_server.log:

 starting automatic database upgrade
[Z3005] query failed: [1215] Cannot add foreign key constraint [alter table trigger_discovery add constraint c_trigger_discovery_1 foreign key (triggerid) references triggers (triggerid) on delete cascade]
database upgrade failed

On old server:

>  select engine from information_schema.tables where table_name='trigger_discovery';
+--------+
| engine |
+--------+
| MyISAM |
+--------+

On new server after restore database from old server:

> select table_name,engine from information_schema.tables;
...
| trends_uint                                         | MyISAM  
| trigger_depends                                     | MyISAM   
| trigger_discovery                                   | InnoDB    
| trigger_discovery_tmp                               | InnoDB  
| triggers                                            | MyISAM  
....

Other tables - MyISAM

[Shurman]

The easiest way for you would be:
1. Drop everything on the new server
2. Disable InnoDB on the new server completely in my.cnf
3. Restore DB from your dump, upgrade and enjoy. :-)

If you need InnoDB... then you need to re-read my original post with the solution and try to understand what exactly is causing the problem - this will lead you to the clear way to solve.

[Viewer]

The easiest way for you would be:
1. Drop everything on the new server
2. Disable InnoDB on the new server completely in my.cnf
3. Restore DB from your dump, upgrade and enjoy. :-)

I add to my.cnf

default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM
innodb=OFF

Drop database 'zabbix', create new and restore from dump(on old server - all tables MyISAM).
And I again get error

[Z3005] query failed: [1286] [B]Unknown storage engine 'InnoDB'[/B] [alter table trigger_discovery add constraint c_trigger_discovery_1 foreign key (triggerid) references triggers (triggerid) on delete cascade]
 database upgrade failed

and warning in mysqld.log

[Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

[Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

[Shurman]

Drop everything again on the target server.

Before importing your dump from old server, do

sed 's/InnoDB/MyISAM/g' -i zabbix.sql

Ofcourse implying your dump is in zabbix.sql in currect directory (or specify full/correct path)

[Shurman]

I add to my.cnf

default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM
innodb=OFF

I have just

skip-innodb

in my.cnf

I am not sure if your innodb=OFF worked... check if you have it really disabled.

[Viewer]

Drop everything again on the target server.

Before importing your dump from old server, do

sed 's/InnoDB/MyISAM/g' -i zabbix.sql

I do it , drop database ,and restore again.
In dump all engine parameters is MyISAM.
In my.cnf add skip-innodb , but is "deprecated (though still currently works)".
I get error(Debuglevel=4):

query failed: [1286] Unknown storage engine 'innodb' [create table trigger_discovery_tmp (triggerid bigint unsigned not null, parent_triggerid bigint unsigned not null) engine=innodb]
query [create table trigger_discovery_tmp (triggerid bigint unsigned not null, parent_triggerid bigint unsigned not null) engine=innodb] failed, setting transaction as failed
 query [txnlev:1] [rollback;]

Why i get error 'innodb' ?

"Drop everything" is "drop database zabbix and create again"?

[Shurman]

Ok then.

I looked into sources, it is hardcoded in /src/libs/zbxdbupgrade/dbupgrade.c

Line 35:

#ifdef HAVE_MYSQL
#       define ZBX_DB_TABLE_OPTIONS     " engine=innodb"
#       define ZBX_DROP_FK              " drop foreign key"

Obviously, you have two options:

1. change this to engine=myisam and recompile

2. disable myisam on the new server, make sure that all tables in dump are innodb (sed 's/MyISAM/InnoDB/g' -i zabbix.sql), perform import and upgrade.

The root of the problem, as I already wrote, is that you have both myisam and innodb tables in one database. Get rid of this, upgrade will pass on. However keep in mind that innodb is hardcoded in the upgrade process.

[Viewer]

both myisam and innodb tables in one database

Thank you for your help!
I solved problem.

> ALTER TABLE trigger_discovery DROP FOREIGN KEY c_trigger_discovery_1;
> ALTER TABLE trigger_discovery DROP FOREIGN KEY c_trigger_discovery_2;
> ALTER TABLE trigger_discovery ENGINE=MyISAM;

and also with table graph_discovery.
Result - 2 tables myISAM , other - innoDB.