Zabbix 5.4.4 New Database Upgrade failure on MariaDB 10.5.10

[ShenLong]

Hi guys,

I encounter some error where i cant find the reason for on my system yet.

21339:20211122:014758.414 Starting Zabbix Server. Zabbix 5.4.4 (revision 1765c4f1bc).
21339:20211122:014758.415 ****** Enabled features ******
21339:20211122:014758.415 SNMP monitoring:           YES
21339:20211122:014758.415 IPMI monitoring:            NO
21339:20211122:014758.415 Web monitoring:            YES
21339:20211122:014758.415 VMware monitoring:          NO
21339:20211122:014758.415 SMTP authentication:       YES
21339:20211122:014758.415 ODBC:                       NO
21339:20211122:014758.415 SSH support:               YES
21339:20211122:014758.415 IPv6 support:              YES
21339:20211122:014758.415 TLS support:               YES
21339:20211122:014758.415 ******************************
21339:20211122:014758.415 using configuration file: /etc/zabbix/zabbix_server.conf
21339:20211122:014758.426 current database version (mandatory/optional): 05030002/05030002
21339:20211122:014758.427 required mandatory version: 05040000
21339:20211122:014758.427 starting automatic database upgrade
21339:20211122:014758.434 [Z3005] query failed: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '''',3)' at line 1 [insert into profiles (profileid,userid,idx,idx2,value_id,value_int,value_str,source,type) values (941,2,'web.templates.dashbrd.list.sort',0,0,0,'name',''',3)]
21339:20211122:014758.438 database upgrade failed

The description of this db table:

MariaDB [zabbix]> describe profiles;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| profileid | bigint(20) unsigned | NO   | PRI | NULL    |       |
| userid    | bigint(20) unsigned | NO   | MUL | NULL    |       |
| idx       | varchar(96)         | NO   |     | '       |       |
| idx2      | bigint(20) unsigned | NO   |     | 0       |       |
| value_id  | bigint(20) unsigned | NO   |     | 0       |       |
| value_int | int(11)             | NO   |     | 0       |       |
| value_str | text                | NO   |     | NULL    |       |
| source    | varchar(96)         | NO   |     | '       |       |
| type      | int(11)             | NO   |     | 0       |       |
+-----------+---------------------+------+-----+---------+-------+
9 rows in set (0.002 sec)

Can you guide me to a solution? Maybe another mariadb version that can handle with it?

[tim.mooney]

Assuming your cut-and-past is correct, it's trying to use a value of three single quotes in a row. SQL quoting is a bit weird, but I doubt that's valid.

Do you have (or did you have) a particular SQL_MODE set when you created the Zabbix database and imported the schema?

Here's why I'm asking. If you look at the CREATE TABLE statement from "schema.sql" for Zabbix 5.4 (I'm looking at the one for 5.4.5, since that's what I have downloaded, but it's probably the same for 5.4.4), you'll see that the "profiles" table looks like this:

CREATE TABLE `profiles` (
`profileid` bigint unsigned NOT NULL,
`userid` bigint unsigned NOT NULL,
`idx` varchar(96) DEFAULT '' NOT NULL,
`idx2` bigint unsigned DEFAULT '0' NOT NULL,
`value_id` bigint unsigned DEFAULT '0' NOT NULL,
`value_int` integer DEFAULT '0' NOT NULL,
`value_str` text NOT NULL,
`source` varchar(96) DEFAULT '' NOT NULL,
`type` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (profileid)
) ENGINE=InnoDB;

Here's the thing to understand: in many dialects of SQL, you "escape" a single quote by doubling it up.

Notice that the Zabbix developers are using a default value for the "idx" and "source" columns that's just two single quotes together? In most versions of MySQL & MariaDB, that would be treated as a quoted, empty string. It's not a NULL value, it's a string, but it contains no characters.

Your schema, however, ended up with a single quote as the default value for idx and source. That means that your MariaDB is treating two quotes together not like how a traditional MySQL & MariaDB would, but instead it's treating it like some other databases would.

That would typically happen because the SQL_MODE setting has been changed to make MariaDB more able to accept SQL syntax that's for some other variant or dialect of SQL.

My first guess would be you've made some non-default settings for your server, probably involving SQL_MODE.

If that's not the case, though, and you're using stock MariaDB settings, then it's possible that one of the defaults changed at 10.5 and it's causing this. That seems unlikely, but it's not impossible. I don't see it mentioned in the MariaDB release notes, but if you determine that your SQL settings are all the standard settings, you may want to dig deeper into what's new with MariaDB 10.5, to see if something is causing this.

[ShenLong]

Assuming your cut-and-past is correct, it's trying to use a value of three single quotes in a row. SQL quoting is a bit weird, but I doubt that's valid.

Do you have (or did you have) a particular SQL_MODE set when you created the Zabbix database and imported the schema?

Not that i'm aware of, but i know that this setup gone through several mariadb versions over time and i did see that the SQL_Mode Defaults have changed from 10.1 to 10.2, which happened in 2019... This could not be the problem. But maybe there are some default-changes from 10.4 to 10.5 which happened in March... since i last rebooted the machine. I have to look it up.

Here's why I'm asking. If you look at the CREATE TABLE statement from "schema.sql" for Zabbix 5.4 (I'm looking at the one for 5.4.5, since that's what I have downloaded, but it's probably the same for 5.4.4), you'll see that the "profiles" table looks like this:

I also did see that but couldn't figure out why this was happening. that's why i was asking in the first place.

If that's not the case, though, and you're using stock MariaDB settings, then it's possible that one of the defaults changed at 10.5 and it's causing this. That seems unlikely, but it's not impossible. I don't see it mentioned in the MariaDB release notes, but if you determine that your SQL settings are all the standard settings, you may want to dig deeper into what's new with MariaDB 10.5, to see if something is causing this.

Thanks again. As mentioned, i have to look it up, i'll come back if i found something missing in all these years (The initial Setup was 2018 on mariadb-10.1 and zabbix-3.4.. i did regular updates on the machine but may be sloppy with some of the mariadb-stuff on the way.

It's on Gentoo, so rolling releases may have been influenced some defaults over time which i was not fully aware of.

[ShenLong]

Now it works.

I checked with a ubuntu server that i spun up if there are some weird variations of the defaults on gentoo. Which i dont find anything.
After that i dump the db again and worked with sed to fix all the empty Default Strings and empty strings from the entrys. After reimport the upgrade worked flawless.

To be frank. I didnt check if there should somewhere be a lonely ' in one of the table-cells. But after the upgrade could complete without errors it doesnt seems to be so.

If you dont say anything, i seem this problem to be fixed.
Thanks again for your Help.

Greetings.