Unable to restore 20gig database backup to newly installed zabbix

[ptera]

Trying to move our running zabbix 3.4.15 installation to a new server three times now
Installed a fresh copy of Zabbix 3.4.15 on Ubuntu Linux 18.04
Shut down the original zabbix server
Exported the database
Imported the database to the new server (takes a really long time)
Start the new zabbix server and
zabbix_server [19765]: ERROR [file:dbconfig.c,line:1999] Something impossible has just happened.
19765:20200102:090603.728 === Backtrace: ===
19765:20200102:090603.729 7: /usr/sbin/zabbix_server(zbx_backtrace+0x44) [0x56490859cd9b]
19765:20200102:090603.729 6: /usr/sbin/zabbix_server(+0xca0f7) [0x56490856f0f7]
19765:20200102:090603.729 5: /usr/sbin/zabbix_server(DCsync_configuration+0x724) [0x5649085757a0]
19765:20200102:090603.729 4: /usr/sbin/zabbix_server(MAIN_ZABBIX_ENTRY+0x5f4) [0x5649084e60a6]
19765:20200102:090603.729 3: /usr/sbin/zabbix_server(daemon_start+0x31f) [0x56490859c4df]
19765:20200102:090603.729 2: /usr/sbin/zabbix_server(main+0x305) [0x5649084e5a9c]
19765:20200102:090603.729 1: /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xe7) [0x7f93af7a9b97]
19765:20200102:090603.729 0: /usr/sbin/zabbix_server(_start+0x2a) [0x5649084d90fa]

Which from what I read means the database is corrupted.
How do restore the database with out it becoming corrupted?

[Markku]

Hi, I believe you could start by clarifying what you actually did, like how did you exactly export the database, and how did you import it. Maybe we can see some immediate problem in the process.

Also, to rule out a problem with the database handling in the new server in general, you could create a new Zabbix database just like you would when creating a new Zabbix installation, and then see that the Zabbix server works normally with it. Preferably use some other database name in testing, you can then drop that after seeing that everything works fine with it.

Markku

[ptera]

I backed up the database using Webmin MySQL Database Server Backup database.
I imported it using the same Module except Execute SQL RZun SQL from file and selected the exported file using default character set.
Yes the server did work with the default database which I dropped before importing.
My database has years of history and over 1000 host entries on 47 screens and 56 maps with customized templates.
If there is some special mysql command I should run from command line I will try to look for it.
Thanks

[Markku]

I don't know Webmin, but according to my memos I've previously backed up the Zabbix database with:

mysqldump -h db_server_ip -u zabbix -p --extended-insert --single-transaction zabbix | gzip > zabbix.gz

and restored successfully on the new database server with:

gunzip -c zabbix.gz | mysql -u zabbix -p zabbix

Both will use database name of "zabbix" and ask for the password for user "zabbix" in the database server.

Markku

[Markku]

Btw, about the long restore time, I just read someone posting about a very long restore, and he had forgotten to adjust the innodb_buffer_pool_size configuration on the server. For MariaDB, see https://mariadb.com/kb/en/innodb-buffer-pool/ for more information, or search for the similar configuration details for MySQL if using it.

Markku

[ptera]

Cool - Thanks
I will check and give it a go.

[ptera]

Hmmm tried this and the mariadb server would not start :-(

--innodb-buffer-pool-size=33554432
--innodb-buffer-pool-instances=32

[ptera]

Going to copy my setting from the old server.

[ptera]

mariadb loaded yeah!

[ptera]

So while I am still waiting for the restore - I have seen some where where the server attempts to reload when it is down - if this is the case could that be messing with the restore of the database?

[ptera]

Export command -
mysqldump -h localhost -u zabbix -p --extended-insert --single-transaction zabbix | gzip > /remotebackup/Zabbix3.4/zabbix.gz

After copying to local drive from remote

Import command -
gunzip -c /home/inception/zabbix.gz | mysql -u zabbix -p$$$$$ zabbix

No luck - same problem
zabbix_server [1305]: ERROR [file:dbconfig.c,line:1999] Something impossible has just happened.
1305:20200106:170419.323 === Backtrace: ===
1305:20200106:170419.323 7: /usr/sbin/zabbix_server(zbx_backtrace+0x44) [0x55dc48f29d9b]
1305:20200106:170419.323 6: /usr/sbin/zabbix_server(+0xca0f7) [0x55dc48efc0f7]
1305:20200106:170419.323 5: /usr/sbin/zabbix_server(DCsync_configuration+0x724) [0x55dc48f027a0]
1305:20200106:170419.323 4: /usr/sbin/zabbix_server(MAIN_ZABBIX_ENTRY+0x5f4) [0x55dc48e730a6]
1305:20200106:170419.323 3: /usr/sbin/zabbix_server(daemon_start+0x31f) [0x55dc48f294df]
1305:20200106:170419.323 2: /usr/sbin/zabbix_server(main+0x305) [0x55dc48e72a9c]
1305:20200106:170419.323 1: /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xe7) [0x7fc0720f3b97]
1305:20200106:170419.323 0: /usr/sbin/zabbix_server(_start+0x2a) [0x55dc48e660fa]

[Markku]

Have you checked the available search results like ("zabbix dbconfig.c impossible happened")

https://support.zabbix.com/browse/ZBX-12657

https://support.zabbix.com/browse/ZBX-14445

Do they provide any hints what could be wrong in the database schema?

Markku

[ptera]

Yes I looked at those but since I am not a database pro it does not make any sense to me.

select * from interface where hostid not in (select hostid from hosts);
That query returned 230 rows but all the entries looked OK to me. Not sure what I am looking at or even what to do about it.

[ptera]

Looks like the problem is too big a version difference in the mariaDB database from 5 to 10
Thanks for your help

[Markku]

Ok, copying data between different MariaDB major versions is something that I haven't yet done. I would have assumed that you got some errors though if the import was not successful.

Markku