DB Upgrade error 6.0 -> 6.2

[hexhex]

2480:20230315:233408.155 starting automatic database upgrade
2480:20230315:233408.166 [Z3005] query failed: [1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs [alter table `media_type` add `provider` integer default '0' not null]




DB: MariaDB 10.6.12-MariaDB-1:10.6.12+maria~ubu2004

[welaish]

2480:20230315:233408.155 starting automatic database upgrade
2480:20230315:233408.166 [Z3005] query failed: [1118] Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs [alter table `media_type` add `provider` integer default '0' not null]
DB: MariaDB 10.6.12-MariaDB-1:10.6.12+maria~ubu2004

same error also no solution anywhere?

[Markku]

ZBX-16757 ? (shortlink)

shell> mysql -uroot -p<password>
mysql> set global innodb_strict_mode='OFF';
mysql> \q
shell> sudo systemctl restart zabbix_server
shell> mysql -uroot -p<password>
mysql> set global innodb_strict_mode='ON';
mysql> \q

Markku

[tim.mooney]

same error also no solution anywhere?

You've not given us any infromation about your environment, except that you're currently running MariaDB 10.6.12.

What version of zabbix are you upgrading from and what version of Zabbix are you upgrading to?

What does the following command report?

SHOW TABLE STATUS FROM zabbix LIKE 'media_type' \G

[welaish]

You've not given us any infromation about your environment, except that you're currently running MariaDB 10.6.12.

What version of zabbix are you upgrading from and what version of Zabbix are you upgrading to?

What does the following command report?

SHOW TABLE STATUS FROM zabbix LIKE 'media_type' \G

upgrading from 6 to 7
command report:

*************************** 1. row ***************************
Name: media_type
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2020-07-01 12:27:59
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.001 sec)

[tim.mooney]

upgrading from 6 to 7
command report:

*************************** 1. row ***************************
Name: media_type
Engine: InnoDB
Version: 10
Row_format: Compact

There's your problem: Row_format: Compact

Check the section of "Known Issues" with the heading "Upgrade with MariaDB 10.2.1 and before" and follow the link there. https://www.zabbix.com/documentation...n/known_issues

The problem is that when your Zabbix database was originally created, you were using an older version of MariaDB. That older version defaulted to using Row_format 'COMPACT' for rows in an InnoDB table.

Versions of MariaDB after 10.2.1 now default to Row_format 'DYNAMIC' for all newly created tables, but even though you've upgraded the version of MariaDB you're using and even if you ran 'mysql_upgrade' as part of your upgrades of the database software, the Row_format for you existing tables was never upgraded. It's still using the old default.

There are different properties for the different table formats and row formats supported by MySQL/MariaDB/Percona. Some of the differences are explained in this MariaDB article: https://mariadb.com/kb/en/innodb-row-formats-overview/

The MySQL documentation has a better chart and does a little bit better job explaining some of the critical differences between the current Row_format setting, so you can supplement your understanding of Row_format using this: https://dev.mysql.com/doc/refman/8.4...ow-format.html

It's not just Zabbix that has run into problems because of tables created under old versions of MySQL/MariaDB. Once you know about the Row_format issue and the smaller limits on some things (index size, row length) that older formats had, you can find people running into problems with row length or index length on various non-Zabbix web sites like stackoverflow, etc.