SQL statement execution has failed "INSERT INTO auditlog

[frater]

After I upgraded to Zabbix 6.4.3 I noticed that it didn't support the $1 in item name.
I wanted to return to my previous version of Zabbix 5.4.7

So I made a new backup using Plesk...
And then restored a 2 week old back-up and it didn't work

so I thought I would be up and running quickly doing the fresh back-up I previously made.

I got this

"SQL statement execution has failed "INSERT INTO auditlog (auditid,userid,username,clock,ip,action,resourcetype,resourceid,resourcename,recordsetid,details) VALUES ....

It turns out that I do not have the table auditlog in my database zabbix anymore since the restore from an mysqldump
To know what happened I tried to do a manual restore of the database using

mysql -uadmin -p`cat /etc/psa/.psa.shadow` zabbix < backup_sqldump_2306200016
ERROR 1005 (HY000) at line 220: Can't create table `zabbix`.`auditlog` (errno: 150 "Foreign key constraint is incorrectly formed")

It does this with the command CREATE TABLE `auditlog`

I also tried to create the table using the command that's in schema.sql and it throws me the same error. (this was because I didn't drop the database first)


I'm now suspecting it has something to do with the order the tables are created in the mysqldump.
Is this true? (EDIT: yes, it was the reason!!!!)

In the dump the tables are created in alphabetical order...

[frater]

I have found a way of solving it.

The database dump is quite huge, so editing it with vi is either difficult or impossible (I killed it after 5 minutes of loading).
Anyone knows another approach??

I dropped the database and created it again

Destroy and create Zabbix 6.4 database

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

mysql> drop database zabbix;
​mysql> create database zabbix character set utf8mb4 collate utf8mb4_bin;
​mysql> grant all privileges on zabbix.* to 'zabbix'@'localhost';
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
mysql> quit;

I imported the schema.sql from the zabbix distribution

mysql -uadmin -p`cat /etc/psa/.psa.shadow` zabbix </opt/zabbix-6.4.3/database/mysql/schema.sql

Then I used sed to change the commands in my dump

sed -i 's/^DROP TABLE IF EXISTS.*/\/\* & \*\//g' backup_sqldump_2306200016
sed -i 's/^CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' backup_sqldump_2306200016

This way it isn't dropping and creating the tables.

DROP TABLE is commented out
CREATE TABLE is replaced with CREATE TABLE IF NOT EXISTS

Import DUMP

mysql -uadmin -p`cat /etc/psa/.psa.shadow` zabbix <backup_sqldump_2306200016

RESET TRUST

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
mysql> quit;

This was of course also the problem with my 5.4.7 installation.
When this works I will be doing a restore of that database again and a recompile of 5.4.7 and never upgrade again.

I haven't been getting any new features I want for 5 years and I barely recovered after they killed the live events...


================================================== ================================================
Going back to 5.4.7 with a database dump
================================================== ================================================

Destroy and create Zabbix 5.4 database (note the different create command)

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

mysql> drop database zabbix;
​mysql> create database zabbix character set utf8 collate utf8_bin;
​mysql> grant all privileges on zabbix.* to 'zabbix'@'localhost';
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
mysql> quit;

Import the schema.sql from the zabbix distribution (5.4.7)

mysql -uadmin -p`cat /etc/psa/.psa.shadow` zabbix </opt/zabbix-5.4.7/database/mysql/schema.sql

Extract dump from Plesk

tar -xvf backup_sqldump_2306122209.tzst --use-compress-program pzstd

Patch DUMP

sed 's/^DROP TABLE IF EXISTS.*/\/\* & \*\//g';s/^CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' backup_sqldump_2306122209 >backup_sqldump_2306122209.patched.sql

Check DUMP

egrep '(DROP|CREATE) TABLE' backup_sqldump_2306122209.patched.sql | head -n6

/* DROP TABLE IF EXISTS `acknowledges`; */
CREATE TABLE IF NOT EXISTS `acknowledges` (
/* DROP TABLE IF EXISTS `actions`; */
CREATE TABLE IF NOT EXISTS `actions` (
/* DROP TABLE IF EXISTS `alerts`; */
CREATE TABLE IF NOT EXISTS `alerts` (



Check size difference between original SQL-dump and patched one (I had a botched try)

echo $((`stat -c %s backup_sqldump_2306122209.patched.sql` - `stat -c %s backup_sqldump_2306122209` ))

3320



Import DUMP

mysql -uadmin -p`cat /etc/psa/.psa.shadow` zabbix <backup_sqldump_2306122209.patched.sql

RESET TRUST

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
mysql> quit;

Recompile Zabbix and Restore WebIF

[frater]

I had this problem for the first time and the thing I don't get is.... Why could I hardly find anything about it on the net?
As I see it, the mysqldump shouldn't create an SQL-file where the creation of databases are dependent of others?

By creating all the databases in an alphabetic order it is prone to mess this up.


Can someone please explain?
Even though I solved it by creating all the databases first in the order which was set by the developers and then patching the SQL-file, I have a feeling it can be done much easier....

I also tried to use a parameter which tells mysql (SET FOREIGN_KEY_CHECKS=OFF;), but it didn't work

Which party is at fault that it doesn't work out of the box??

Plesk -> because it created a dump on alphabetic order?
Zabbix -> because they didn't use that alphabetic order to make sure a restore would be in the correct order...
MariadB -> because it doesn't take care of this by itself?