After 4.4 to 6.0 migration, Cannot update trigger - incorrect item key

[ekm]

Slackware Linux 15.0
MariaDB 10.5.15
Zabbix 6.0.2

Within a net.dns[] trigger expression, I'm making a hostname change only, and then cannot update the trigger. This is after migrating from Zabbix 4.4 to Zabbix 6.0. I was able to confirm the trigger expression is understood by the local Zabbix agent using zabbix_get:

# zabbix_get -s 127.0.0.1 -k 'net.dns[127.0.0.1,cco-20317,A,,,]'

Upon reading the thread below, I now want to confirm or rule out any schema issue caused by importing my 4.4 database and letting 6.0 server upgrade the schema automatically.

Incorrect item key - unable to create triggers - ZABBIX Forums

So I exported the live schema, exported a stock 6.0 schema, and compared, and I see some differences. But that's as far as my knowledge goes. I don't know if these differences are relevant to the problem I'm having, and I don't know how to safely correct the live database to match the stock database if that would help.

Here are the differences I found.

1. Disagreement about character set in all CHARSET and COLLATE expressions.
The live schema uses UTF8.
The stock 6.0 schema uses UTF8MB4.

2. Disagreement about definitions of four fields: trends.value_min, trends.value_avg, trends.value_max, and history.value.
The live schema uses 'double(16.4)' type.
The stock 6.0 schema uses 'double' type.

3. Disagreement about key declarations on six tables:

alerts
live schema: KEY `c_alerts_6` (`acknowledgeid`)
stock schema: absent

history, history_log, history_str, history_text, and history_uint
live schema:
KEY `history_1` (`itemid`,`clock`)
KEY `history_log_1` (`itemid`,`clock`)
KEY `history_str_1` (`itemid`,`clock`)
KEY `history_text_1` (`itemid`,`clock`)
KEY `history_uint_1` (`itemid`,`clock`)
stock schema:
PRIMARY KEY (`itemid`,`clock`,`ns`)
(on all five tables)

[tim.mooney]

First, just a minor point: you said "trigger" in a couple of places where I think you meant "item". For Zabbix, "item" is the data reading that is periodically collected, while trigger is the threshold logic that determines whether a particular item value is a problem. When you're running zabbix_get like you did, you tested whether you could collect a specific item, and what the value was that was returned. Now you may be trying to alter a trigger (and the trigger syntax changed significantly at Zabbix 5.4, so trigger expressions look much different at 6.0 than they did at 4.4), but most of what you've displayed has mainly been about the item, not the trigger. It's minor, but I wanted to be certain we were on the same page.

I'm not certain if your database schema differences from a stock schema are what's causing this particular issue or not. I kind of think not, but I'm really not certain.

Regarding your schema differences, I'll start with the easiest:

2) the "double" issue might not be anything at all (it could be just an esoteric display issue between the two schemas) but it's worth checking to see whether you manually applied the database patch that was needed when upgrading to 5.x to enable the extended range for history values: https://www.zabbix.com/documentation...rade_notes_500

Because you upgraded "through" 5.0 (and 5.2, and 5.4), the upgrade notes for those versions also apply to your upgrade from 4.4 to 6.0. I'm at least slightly suspicious that some of your schema differences might be because that step wasn't applied. Please verify that you applied the database/mysql/double.sql as outlined in the 5.0 upgrade notes.

If that step wasn't done, then it's also possible that at least some of the KEY vs. PRIMARY KEY issues with some of those tables might relate to that missed step.

1) the UTF8 (which in older MySQL/MariaDB is actually a 3-byte subset of UTF-8, not true UTF-8, hence the separate UTF8MB4 that is true utf-8) issue probably isn't the problem. I can't find the reference now, but I believe I read that both 3-byte and 4-byte UTF-8 on MySQL/MariaDB are supported. MySQL and MariaDB are both moving in the direction of fixing the long-standing issue with their "UTF-8" not really being UTF-8, so at some point there may be character set and collation conversion to be done, but I don't think you need to be too concerned about that right now.

3) Those differences might be because of the "Primary keys" note in the 6.0.0 upgrade notes: https://www.zabbix.com/documentation...rade_notes_600

That certainly matches your symptoms; a fresh schema gets primary keys automatically, but an upgraded schema needs a manual patch applied, just like with the extended range types at 5.0.0.

I would take a look at those manual database schema adjustments and make sure you've applied them, and then do another comparison of your schema vs. the stock/new schema, to see if a lot of the differences have disappeared.

[tim.mooney]

First, just a minor point: you said "trigger" in a couple of places where I think you meant "item". For Zabbix, "item" is the data reading that is periodically collected, while trigger is the threshold logic that determines whether a particular item value is a problem. When you're running zabbix_get like you did, you tested whether you could collect a specific item, and what the value was that was returned. Now you may be trying to alter a trigger (and the trigger syntax changed significantly at Zabbix 5.4, so trigger expressions look much different at 6.0 than they did at 4.4), but most of what you've displayed has mainly been about the item, not the trigger. It's minor, but I wanted to be certain we were on the same page.

I'm not certain if your database schema differences from a stock schema are what's causing this particular issue or not. I kind of think not, but I'm really not certain.

Regarding your schema differences, I'll start with the easiest:

2) the "double" issue might not be anything at all (it could be just an esoteric display issue between the two schemas) but it's worth checking to see whether you manually applied the database patch that was needed when upgrading to 5.x to enable the extended range for history values: https://www.zabbix.com/documentation...rade_notes_500

Because you upgraded "through" 5.0 (and 5.2, and 5.4), the upgrade notes for those versions also apply to your upgrade from 4.4 to 6.0. I'm at least slightly suspicious that some of your schema differences might be because that step wasn't applied. Please verify that you applied the database/mysql/double.sql as outlined in the 5.0 upgrade notes.

If that step wasn't done, then it's also possible that at least some of the KEY vs. PRIMARY KEY issues with some of those tables might relate to that missed step.

1) the UTF8 (which in older MySQL/MariaDB is actually a 3-byte subset of UTF-8, not true UTF-8, hence the separate UTF8MB4 that is true utf-8) issue probably isn't the problem. I can't find the reference now, but I believe I read that both 3-byte and 4-byte UTF-8 on MySQL/MariaDB are supported. MySQL and MariaDB are both moving in the direction of fixing the long-standing issue with their "UTF-8" not really being UTF-8, so at some point there may be character set and collation conversion to be done, but I don't think you need to be too concerned about that right now.

3) Those differences might be because of the "Primary keys" note in the 6.0.0 upgrade notes: https://www.zabbix.com/documentation...rade_notes_600

That certainly matches your symptoms; a fresh schema gets primary keys automatically, but an upgraded schema needs a manual patch applied, just like with the extended range types at 5.0.0.

I would take a look at those manual database schema adjustments and make sure you've applied them, and then do another comparison of your schema vs. the stock/new schema, to see if a lot of the differences have disappeared.