database import to another server

[alvaro1337]

Hello Guys,
It's my first post sorry for my english and stupid question (if they are).
In my copany I implemented zabbix some times ago. It was Zabbix 4.4.7. (appliance version if i good remember).
Due that it is old version i decide that I will creat new server 5.2 and i import database from old server.
Everything looked fine but... After import when i tried connect on my server i noticed error... ( look attachment).
I searched whole internet but i dont fount solution my problem.. I will be grateful if you help me.
Thank you for support.

[tim.mooney]

The Zabbix web interface (the "front end") is separate from the "zabbix-server". They can be hosted on separate servers, but they both need to talk to whatever database you've chosen as the database backend.

When you start up a new version of the zabbix-server process for the first time, it examines the database, and if the database reports that it is for an older version of Zabbix (like 4.4.x), then the first thing the zabbix_serverd process does is apply database changes to bring the database up to the required version. So, if you started up the 5.2.0 or 5.2.1 version of the zabbix-server process, it should have automatically updated your database to the "5020000" version.

Your web interface (front end) is saying that the database hasn't been updated. There are a few common reasons why that might be

1. some people don't realize that the 'zabbix-web' and the 'zabbix-server' components are separate and that it's the 'zabbix-server' that is responsible for updating the database (and collecting the items, and evaluating triggers, and issuing alerts, etc.), so when they install or update, they update only the zabbix-web packages and forget to update the 'zabbix-server' package. Make sure that both the zabbix-server and the zabbix-web packages are the same versions.
2. you might have updated the zabbix-server package, but the database updates don't happen until zabbix-server is started up. If you're looking at the web interface but forgot to start up zabbix-server, then it hasn't had a chance to do its database updates yet. Make sure you've started zabbix_serverd and it's running.
3. Sometimes when there's a move between systems, people forget to point both the web front end and the zabbix-server at the correct database location. The web interface does not read /etc/zabbix/zabbix_server.conf to find the database to connect to. It uses its own config in the conf/zabbix.conf.php file. Make sure that both the web front end and the zabbix-server are both pointed at the same database.
4. Sometimes everything else is configured correctly but the database changes can't be successfully applied by zabbix-server. If this happens, there should be messages in /var/log/zabbix/zabbix_sesrver.log, right after its startup messages, indicating a failure.

[alvaro1337]

Thank you for your feedback. But if i will be honest i don't understand everything what you wrote. Could you explain how to check your suggestions ?
I'm starting the adventure with zabbix. Below i paste content of log file:

root@zabbix:/var/log/zabbix# nano zabbix_server.log
\
GNU nano 3.2 zabbix_server.log Zmieniony

14526:20201112:202709.355 VMware monitoring: YES
14526:20201112:202709.355 SMTP authentication: YES
14526:20201112:202709.355 ODBC: YES
14526:20201112:202709.355 SSH support: YES
14526:20201112:202709.355 IPv6 support: YES
14526:20201112:202709.355 TLS support: YES
14526:20201112:202709.355 ******************************
14526:20201112:202709.355 using configuration file: /etc/zabbix/zabbix_server.conf
14526:20201112:202709.358 current database version (mandatory/optional): 04050015/04050015
14526:20201112:202709.358 required mandatory version: 05020000
14526:20201112:202709.358 starting automatic database upgrade
14526:20201112:202709.358 [Z3005] query failed: [1050] Table 'media_type_message' already exists [create table media_type_message (
`mediatype_messageid` bigint unsigned not null,
`mediatypeid` bigint unsigned not null,
`eventsource` integer not null,
`recovery` integer not null,
`subject` varchar(255) default '' not null,
`message` text not null,
primary key (mediatype_messageid)
) engine=innodb]
14526:20201112:202709.358 database upgrade failed
14531:20201112:202719.617 Starting Zabbix Server. Zabbix 5.2.0 (revision bcf99fb248).
14531:20201112:202719.618 ****** Enabled features ******
14531:20201112:202719.618 SNMP monitoring: YES
14531:20201112:202719.618 IPMI monitoring: YES
14531:20201112:202719.618 Web monitoring: YES
14531:20201112:202719.618 VMware monitoring: YES
14531:20201112:202719.618 SMTP authentication: YES
14531:20201112:202719.618 ODBC: YES
14531:20201112:202719.618 SSH support: YES
14531:20201112:202719.618 IPv6 support: YES
14531:20201112:202719.618 TLS support: YES
14531:20201112:202719.618 ******************************
14531:20201112:202719.618 using configuration file: /etc/zabbix/zabbix_server.conf
14531:20201112:202719.619 current database version (mandatory/optional): 04050015/04050015
14531:20201112:202719.619 required mandatory version: 05020000
14531:20201112:202719.619 starting automatic database upgrade
14531:20201112:202719.619 [Z3005] query failed: [1050] Table 'media_type_message' already exists [create table media_type_message (
`mediatype_messageid` bigint unsigned not null,
`mediatypeid` bigint unsigned not null,
`eventsource` integer not null,
`recovery` integer not null,
`subject` varchar(255) default '' not null,
`message` text not null,
primary key (mediatype_messageid)
) engine=innodb]
14531:20201112:202719.619 database upgrade failed

[tim.mooney]

14526:20201112:202709.358 starting automatic database upgrade
14526:20201112:202709.358 [Z3005] query failed: [1050] Table 'media_type_message' already exists [create table media_type_message (
`mediatype_messageid` bigint unsigned not null,
`mediatypeid` bigint unsigned not null,
`eventsource` integer not null,
`recovery` integer not null,
`subject` varchar(255) default '' not null,
`message` text not null,
primary key (mediatype_messageid)
) engine=innodb]
14526:20201112:202709.358 database upgrade failed

That says that zabbix-server tried to update your database ("starting automatic database upgrade") but the change it tried to perform to add a table named 'media_type_message' failed because that table was already present ("Table 'media_type_message' already exists"). It can't complete the database upgrade ("database upgrade failed").

You're left with a database in an unusable, partially-upgraded state.

I'm going to make a guess that when you installed 5.2, you also created a brand new database for 5.2 and you imported the database objects by using the "create.sql" script. That's what the documentation says you should do, but that's also assuming that it's a brand new install. It's not considering that you might move a database from an old version.

If my guess is correct, then the problem is that when you loaded the contents of the old 4.4.7 database, the MySQL import procedure only recreated the tables that existed under 4.4.7. It did not remove the new, empty tables that were created when you loaded the "create.sql".

If that's true, and I'm on the right trail, then what I would recommend you do is

1. stop the zabbix-server service. You don't want it running while you re-load the database.
2. connect to the database via the 'mysql' command line client, then 'use zabbix' to switch to the zabbix database, then remove every table. You can get a list of all the Zabbix database tables by issuing the "SHOW TABLES;" command at the mysql command prompt, and then you just have to issue a bunch of "DROP TABLE table_name_here;" commands.
3. Once you've completed step 2, you still have a zabbix database but it's empty. You also still have the 'zabbix' database user and that user has the necessary rights to things within the zabbix database.
4. Now that you've cleaned out all tables from the zabbix database, you go through whatever procedure you did previously to load your SQL backup from your Zabbix 4.4.7 install.
5. Now start up the zabbix-server service, and watch the /var/log/zabbix/zabbix_server.log file. It should upgrade your database to the version 5.2 format.

[alvaro1337]

Thank you Tim. Your tips was very helpfull. You had absolutlly true, in every detail. We can close topic. Thanks!