[ZBX-16757] MariaDB 10.4.8, 10.2.26, 10.3.17 unsafe "Row size too large"() issue with MDEV-19292, MDEV-20194 Created: 2019 Oct 11 Updated: 2024 Jun 27 Resolved: 2020 Feb 17 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | Documentation (D) |
| Affects Version/s: | 4.0.13 |
| Fix Version/s: | 5.0 (plan) |
| Type: | Documentation task | Priority: | Trivial |
| Reporter: | Edgar Akhmetshin | Assignee: | Vladislavs Sokurenko |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | Database, MDEV, MariaDB, MySQL, schema | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||
| Team: |  Team A |
||||||||||||||||||||
| Sprint: | Sprint 57 (Oct 2019), Sprint 58 (Nov 2019), Sprint 59 (Dec 2019), Sprint 60 (Jan 2020), Sprint 61 (Feb 2020) | ||||||||||||||||||||
| Description |
|
Steps to reproduce: Result: 2019-10-08 10:34:13 14 [Warning] InnoDB: Cannot add field `proxy_address` in table `zabbix`.`hosts` because after adding it, the row size is 8494 which is greater than maximum allowed size (8126) for a record on index leaf page. 2019-10-08 10:34:13 14 [Warning] InnoDB: Cannot add field `location` in table `zabbix`.`host_inventory` because after adding it, the row size is 8772 which is greater than maximum allowed size (8126) for a record on index leaf page. 2019-10-08 10:34:13 14 [Warning] InnoDB: Cannot add field `error` in table `zabbix`.`items` because after adding it, the row size is 8562 which is greater than maximum allowed size (8126) for a record on index leaf page. Error 2: strict mode on, trying to load data first time: ERROR 1118 (42000): 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 Disabling strict mode is not a solution (inserts/updates may be broken), also setting innodb_page_size=65536 will not take effect until data will be unloaded and loaded back (database should be recreated). Expected: Some workaround for affected tables: On active setup: set global innodb_strict_mode='OFF'; # on affected tables ALTER TABLE table_name ROW_FORMAT=DYNAMIC; # host_inventory should upgrade field size ALTER TABLE host_inventory MODIFY name varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT ''; ALTER TABLE host_inventory MODIFY alias varchar(128) COLLATE utf8_bin NOT NULL DEFAULT ''; ALTER TABLE host_inventory MODIFY os varchar(128) COLLATE utf8_bin NOT NULL DEFAULT ''; ALTER TABLE host_inventory MODIFY os_short varchar(128) COLLATE utf8_bin NOT NULL DEFAULT ''; set global innodb_strict_mode='ON'; On new setups add to the configuration file: innodb-strict-mode=1 #this will take effect only on new setups innodb_page_size=65536 innodb_default_row_format=dynamic Regards, |
| Comments |
| Comment by Vladislavs Sokurenko [ 2019 Nov 04 ] |
|
(1) [D] Zabbix server version prior to 4.4 cannot work correctly with newer MariaDB versions out of the box. Prior to MariaDB 10.2.26, 10.3.17, and 10.4.7, MariaDB didn't properly calculate the row sizes while executing DDL, so "unsafe" tables could be created, even with innodb_strict_mode=ON set. This was fixed by MDEV-19292. As a side effect, tables that could be created in previous versions may get rejected after the latest releases. Suggested database changes for existing 4.0 installations: shell> mysql -uroot -p<password> mysql> alter table `host_inventory` modify `name` varchar(128) default '' not null, modify `alias` varchar(128) default '' not null, modify `os` varchar(128) default '' not null, modify `os_short` varchar(128) default '' not null; Suggested database changes for new 4.0 installations
shell> mysql -uroot -p<password>
mysql> set global innodb_strict_mode='OFF';
Perform database creation as usual and fix schema after: shell> mysql -uroot -p<password> mysql> alter table `host_inventory` modify `name` varchar(128) default '' not null, modify `alias` varchar(128) default '' not null, modify `os` varchar(128) default '' not null, modify `os_short` varchar(128) default '' not null; mysql> set global innodb_strict_mode='ON'; <mgeneralova > wiper , described the issue in Installation ---> Known issues for Zabbix 3.0 and 4.0 linking to this task for a workaround. vso REOPENED, please include suggested message starting with "Prior" word and ending with <mgeneralova> vso, RESOLVED vso please also add to Database creation perhaps link to known issues just before or inside this message:
<mgeneralova> RESOLVED vso CLOSED, looks good, thank you ! Documentation updates: |
| Comment by Markku Leiniö [ 2020 Jan 01 ] |
|
What is the list of tables that need to be modified in existing Zabbix setup to prevent warnings with current MariaDB versions? Are those shown above all that need the row_format change?
Plus those four field changes in host_inventory table I understand (name, alias, os, os_short). |
| Comment by dimir [ 2024 Jun 27 ] |
|
For the victims coming from google: 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 |
