[#ZBX-16465] Not possible to insert create.sql.gz on MariaDB 10.3.17

[ZBX-16465] Not possible to insert create.sql.gz on MariaDB 10.3.17 Created: 2019 Aug 05 Updated: 2024 Apr 10 Resolved: 2019 Oct 02
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	API (A), Frontend (F), Proxy (P), Server (S)
Affects Version/s:	4.0.11
Fix Version/s:	4.4.0rc1, 4.4 (plan)

Type:

Problem report

Priority:

Major

Reporter:

Aigars Kadikis

Assignee:

Vladislavs Sokurenko

Resolution:

Fixed

Votes:

Labels:

None

Remaining Estimate:

Not Specified

Time Spent:

Not Specified

Original Estimate:

Not Specified

Environment:

MariaDB 10.2.26, 10.3.17, 10.4.7, 10.3.18, 10.4.8

Issue Links:

Causes
Duplicate
is duplicated by	~~ZBX-16573~~	Row size too large (> 8126) error du...	Closed
Sub-task
depends on	~~ZBX-16757~~	MariaDB 10.4.8, 10.2.26, 10.3.17 unsa...	Closed

Team:

Team A

Sprint:

Sprint 56 (Sep 2019), Sprint 55 (Aug 2019), Sprint 57 (Oct 2019)

Story Points:

Description

Hi Team,

While it may not be a direct Zabbix issue I will still state it here:

Inserting a plain schema on MariaDB server 10.3.16 works.

On 10.3.17 server it states:

zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p zabbix
Enter password:
ERROR 1118 (42000) at line 1278: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

The majority of procedures I follow from:

https://www.zabbix.com/download?zabbix=4.0&os_distribution=centos&os_version=7&db=mysql

Steps to reproduce:

cat <<'EOF'> /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.3 CentOS repository list - created 2018-05-31 08:48 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

rpm -ivh https://repo.zabbix.com/zabbix/4.2/rhel/7/x86_64/zabbix-release-4.2-1.el7.noarch.rpm

yum makecache fast

yum -y install MariaDB-server MariaDB-client

systemctl start mariadb

mysql <<< 'create database zabbix character set utf8 collate utf8_bin;'
mysql <<< 'grant all privileges on zabbix.* to "zabbix"@"localhost" identified by "zabbix";'

yum -y install zabbix-server-mysql

zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -pzabbix zabbix

Comments

Comment by Aigars Kadikis [ 2019 Aug 05 ]

https://mariadb.com/kb/en/library/mariadb-10317-release-notes/

Comment by DaneT [ 2019 Aug 05 ]

Present on

mysql Ver 15.1 Distrib 10.4.7-MariaDB, for Linux (x86_64) using readline 5.1

although MDEV-19292 claims it is fixed in 10.4.7

Comment by Logan [ 2019 Aug 09 ]

See https://jira.mariadb.org/browse/MDEV-19292?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&focusedCommentId=132239#comment-132239 for suggestions from MariaDB about how Zabbix can fix its default schema.

Comment by Logan [ 2019 Aug 09 ]

I am currently experiencing this bug on the latest MariaDB 10.2, 10.3, and 10.4 releases using either Zabbix 4.0 or 4.2.

Comment by Geoff Montee [ 2019 Aug 09 ]

The problem here is that InnoDB has a maximum row size in MariaDB and MySQL that is roughly equivalent to half of innodb_page_size. InnoDB's row formats work around this problem by storing certain kinds of variable-length columns on overflow pages. However, different row formats can store different types of data on overflow pages. The DYNAMIC row format is the default row format in MariaDB 10.2 and later. For varchar columns, this row format can only store them on overflow pages if they are 256 bytes or longer:

All InnoDB row formats can store certain kinds of data in overflow pages. This allows for the maximum row size of an InnoDB table to be larger than the maximum amount of data that can be stored in the row's main data page. See Maximum Row Size for more information about the other factors that can contribute to the maximum row size for InnoDB tables.

In the DYNAMIC row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, can be completely stored in overflow pages.

InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size.

For BLOB and TEXT columns, only values longer than 40 bytes are considered for storage on overflow pages. For VARBINARY and VARCHAR columns, only values longer than 255 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards these limits. These limits are only based on the length of the actual column's data.

These limits differ from the limits for the COMPACT row format, where the limit is 767 bytes for all types.

Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size. Even though a column using the CHAR data type can hold at most 255 characters, a CHAR column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the character set is utf8mb4.

If a column is chosen to be stored on overflow pages, then the entire value of the column is stored on overflow pages, and only a 20-byte pointer to the column's first overflow page is stored on the main page. Each overflow page is the size of innodb_page_size. If a column is too large to be stored on a single overflow page, then it is stored on multiple overflow pages. Each overflow page contains part of the data and a 20-byte pointer to the next overflow page, if a next page exists.

This behavior differs from the behavior of the COMPACT row format, which always stores the column prefix on the main page. This allows tables using the DYNAMIC row format to contain a high number of columns using the VARBINARY, VARCHAR, BLOB and TEXT data types.

https://mariadb.com/kb/en/library/innodb-dynamic-row-format/#overflow-pages-with-the-dynamic-row-format

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.

You have two options here:

1.) Disable InnoDB strict mode. i.e. this succeeds in MariaDB 10.2.26:

SET SESSION innodb_strict_mode=OFF;

CREATE TABLE `host_inventory` (
	`hostid`                 bigint unsigned                           NOT NULL,
	`inventory_mode`         integer         DEFAULT '0'               NOT NULL,
	`type`                   varchar(64)     DEFAULT ''                NOT NULL,
	`type_full`              varchar(64)     DEFAULT ''                NOT NULL,
	`name`                   varchar(64)     DEFAULT ''                NOT NULL,
	`alias`                  varchar(64)     DEFAULT ''                NOT NULL,
	`os`                     varchar(64)     DEFAULT ''                NOT NULL,
	`os_full`                varchar(255)    DEFAULT ''                NOT NULL,
	`os_short`               varchar(64)     DEFAULT ''                NOT NULL,
	`serialno_a`             varchar(64)     DEFAULT ''                NOT NULL,
	`serialno_b`             varchar(64)     DEFAULT ''                NOT NULL,
	`tag`                    varchar(64)     DEFAULT ''                NOT NULL,
	`asset_tag`              varchar(64)     DEFAULT ''                NOT NULL,
	`macaddress_a`           varchar(64)     DEFAULT ''                NOT NULL,
	`macaddress_b`           varchar(64)     DEFAULT ''                NOT NULL,
	`hardware`               varchar(255)    DEFAULT ''                NOT NULL,
	`hardware_full`          text                                      NOT NULL,
	`software`               varchar(255)    DEFAULT ''                NOT NULL,
	`software_full`          text                                      NOT NULL,
	`software_app_a`         varchar(64)     DEFAULT ''                NOT NULL,
	`software_app_b`         varchar(64)     DEFAULT ''                NOT NULL,
	`software_app_c`         varchar(64)     DEFAULT ''                NOT NULL,
	`software_app_d`         varchar(64)     DEFAULT ''                NOT NULL,
	`software_app_e`         varchar(64)     DEFAULT ''                NOT NULL,
	`contact`                text                                      NOT NULL,
	`location`               text                                      NOT NULL,
	`location_lat`           varchar(16)     DEFAULT ''                NOT NULL,
	`location_lon`           varchar(16)     DEFAULT ''                NOT NULL,
	`notes`                  text                                      NOT NULL,
	`chassis`                varchar(64)     DEFAULT ''                NOT NULL,
	`model`                  varchar(64)     DEFAULT ''                NOT NULL,
	`hw_arch`                varchar(32)     DEFAULT ''                NOT NULL,
	`vendor`                 varchar(64)     DEFAULT ''                NOT NULL,
	`contract_number`        varchar(64)     DEFAULT ''                NOT NULL,
	`installer_name`         varchar(64)     DEFAULT ''                NOT NULL,
	`deployment_status`      varchar(64)     DEFAULT ''                NOT NULL,
	`url_a`                  varchar(255)    DEFAULT ''                NOT NULL,
	`url_b`                  varchar(255)    DEFAULT ''                NOT NULL,
	`url_c`                  varchar(255)    DEFAULT ''                NOT NULL,
	`host_networks`          text                                      NOT NULL,
	`host_netmask`           varchar(39)     DEFAULT ''                NOT NULL,
	`host_router`            varchar(39)     DEFAULT ''                NOT NULL,
	`oob_ip`                 varchar(39)     DEFAULT ''                NOT NULL,
	`oob_netmask`            varchar(39)     DEFAULT ''                NOT NULL,
	`oob_router`             varchar(39)     DEFAULT ''                NOT NULL,
	`date_hw_purchase`       varchar(64)     DEFAULT ''                NOT NULL,
	`date_hw_install`        varchar(64)     DEFAULT ''                NOT NULL,
	`date_hw_expiry`         varchar(64)     DEFAULT ''                NOT NULL,
	`date_hw_decomm`         varchar(64)     DEFAULT ''                NOT NULL,
	`site_address_a`         varchar(128)    DEFAULT ''                NOT NULL,
	`site_address_b`         varchar(128)    DEFAULT ''                NOT NULL,
	`site_address_c`         varchar(128)    DEFAULT ''                NOT NULL,
	`site_city`              varchar(128)    DEFAULT ''                NOT NULL,
	`site_state`             varchar(64)     DEFAULT ''                NOT NULL,
	`site_country`           varchar(64)     DEFAULT ''                NOT NULL,
	`site_zip`               varchar(64)     DEFAULT ''                NOT NULL,
	`site_rack`              varchar(128)    DEFAULT ''                NOT NULL,
	`site_notes`             text                                      NOT NULL,
	`poc_1_name`             varchar(128)    DEFAULT ''                NOT NULL,
	`poc_1_email`            varchar(128)    DEFAULT ''                NOT NULL,
	`poc_1_phone_a`          varchar(64)     DEFAULT ''                NOT NULL,
	`poc_1_phone_b`          varchar(64)     DEFAULT ''                NOT NULL,
	`poc_1_cell`             varchar(64)     DEFAULT ''                NOT NULL,
	`poc_1_screen`           varchar(64)     DEFAULT ''                NOT NULL,
	`poc_1_notes`            text                                      NOT NULL,
	`poc_2_name`             varchar(128)    DEFAULT ''                NOT NULL,
	`poc_2_email`            varchar(128)    DEFAULT ''                NOT NULL,
	`poc_2_phone_a`          varchar(64)     DEFAULT ''                NOT NULL,
	`poc_2_phone_b`          varchar(64)     DEFAULT ''                NOT NULL,
	`poc_2_cell`             varchar(64)     DEFAULT ''                NOT NULL,
	`poc_2_screen`           varchar(64)     DEFAULT ''                NOT NULL,
	`poc_2_notes`            text                                      NOT NULL,
	PRIMARY KEY (hostid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

2.) Keep InnoDB strict mode enabled, but change some of those varchar(N) columns to varchar(N >= 256 bytes). The specific fix would depend on the table's character set.

For example, if the table's default character set is utf8mb4, then that means that N would have to be 64 or more. i.e. this also succeeds in MariaDB 10.2.26:

SET SESSION innodb_strict_mode=ON;

CREATE TABLE `host_inventory` (
	`hostid`                 bigint unsigned                           NOT NULL,
	`inventory_mode`         integer         DEFAULT '0'               NOT NULL,
	`type`                   varchar(64)     DEFAULT ''                NOT NULL,
	`type_full`              varchar(64)     DEFAULT ''                NOT NULL,
	`name`                   varchar(64)     DEFAULT ''                NOT NULL,
	`alias`                  varchar(64)     DEFAULT ''                NOT NULL,
	`os`                     varchar(64)     DEFAULT ''                NOT NULL,
	`os_full`                varchar(255)    DEFAULT ''                NOT NULL,
	`os_short`               varchar(64)     DEFAULT ''                NOT NULL,
	`serialno_a`             varchar(64)     DEFAULT ''                NOT NULL,
	`serialno_b`             varchar(64)     DEFAULT ''                NOT NULL,
	`tag`                    varchar(64)     DEFAULT ''                NOT NULL,
	`asset_tag`              varchar(64)     DEFAULT ''                NOT NULL,
	`macaddress_a`           varchar(64)     DEFAULT ''                NOT NULL,
	`macaddress_b`           varchar(64)     DEFAULT ''                NOT NULL,
	`hardware`               varchar(255)    DEFAULT ''                NOT NULL,
	`hardware_full`          text                                      NOT NULL,
	`software`               varchar(255)    DEFAULT ''                NOT NULL,
	`software_full`          text                                      NOT NULL,
	`software_app_a`         varchar(64)     DEFAULT ''                NOT NULL,
	`software_app_b`         varchar(64)     DEFAULT ''                NOT NULL,
	`software_app_c`         varchar(64)     DEFAULT ''                NOT NULL,
	`software_app_d`         varchar(64)     DEFAULT ''                NOT NULL,
	`software_app_e`         varchar(64)     DEFAULT ''                NOT NULL,
	`contact`                text                                      NOT NULL,
	`location`               text                                      NOT NULL,
	`location_lat`           varchar(64)     DEFAULT ''                NOT NULL,
	`location_lon`           varchar(64)     DEFAULT ''                NOT NULL,
	`notes`                  text                                      NOT NULL,
	`chassis`                varchar(64)     DEFAULT ''                NOT NULL,
	`model`                  varchar(64)     DEFAULT ''                NOT NULL,
	`hw_arch`                varchar(64)     DEFAULT ''                NOT NULL,
	`vendor`                 varchar(64)     DEFAULT ''                NOT NULL,
	`contract_number`        varchar(64)     DEFAULT ''                NOT NULL,
	`installer_name`         varchar(64)     DEFAULT ''                NOT NULL,
	`deployment_status`      varchar(64)     DEFAULT ''                NOT NULL,
	`url_a`                  varchar(255)    DEFAULT ''                NOT NULL,
	`url_b`                  varchar(255)    DEFAULT ''                NOT NULL,
	`url_c`                  varchar(255)    DEFAULT ''                NOT NULL,
	`host_networks`          text                                      NOT NULL,
	`host_netmask`           varchar(64)     DEFAULT ''                NOT NULL,
	`host_router`            varchar(64)     DEFAULT ''                NOT NULL,
	`oob_ip`                 varchar(64)     DEFAULT ''                NOT NULL,
	`oob_netmask`            varchar(64)     DEFAULT ''                NOT NULL,
	`oob_router`             varchar(64)     DEFAULT ''                NOT NULL,
	`date_hw_purchase`       varchar(64)     DEFAULT ''                NOT NULL,
	`date_hw_install`        varchar(64)     DEFAULT ''                NOT NULL,
	`date_hw_expiry`         varchar(64)     DEFAULT ''                NOT NULL,
	`date_hw_decomm`         varchar(64)     DEFAULT ''                NOT NULL,
	`site_address_a`         varchar(128)    DEFAULT ''                NOT NULL,
	`site_address_b`         varchar(128)    DEFAULT ''                NOT NULL,
	`site_address_c`         varchar(128)    DEFAULT ''                NOT NULL,
	`site_city`              varchar(128)    DEFAULT ''                NOT NULL,
	`site_state`             varchar(64)     DEFAULT ''                NOT NULL,
	`site_country`           varchar(64)     DEFAULT ''                NOT NULL,
	`site_zip`               varchar(64)     DEFAULT ''                NOT NULL,
	`site_rack`              varchar(128)    DEFAULT ''                NOT NULL,
	`site_notes`             text                                      NOT NULL,
	`poc_1_name`             varchar(128)    DEFAULT ''                NOT NULL,
	`poc_1_email`            varchar(128)    DEFAULT ''                NOT NULL,
	`poc_1_phone_a`          varchar(64)     DEFAULT ''                NOT NULL,
	`poc_1_phone_b`          varchar(64)     DEFAULT ''                NOT NULL,
	`poc_1_cell`             varchar(64)     DEFAULT ''                NOT NULL,
	`poc_1_screen`           varchar(64)     DEFAULT ''                NOT NULL,
	`poc_1_notes`            text                                      NOT NULL,
	`poc_2_name`             varchar(128)    DEFAULT ''                NOT NULL,
	`poc_2_email`            varchar(128)    DEFAULT ''                NOT NULL,
	`poc_2_phone_a`          varchar(64)     DEFAULT ''                NOT NULL,
	`poc_2_phone_b`          varchar(64)     DEFAULT ''                NOT NULL,
	`poc_2_cell`             varchar(64)     DEFAULT ''                NOT NULL,
	`poc_2_screen`           varchar(64)     DEFAULT ''                NOT NULL,
	`poc_2_notes`            text                                      NOT NULL,
	PRIMARY KEY (hostid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

But if the table's default character set is latin1, then that means that N would have to be 256 or more. i.e. this also succeeds in MariaDB 10.2.26:

SET SESSION innodb_strict_mode=ON;

CREATE TABLE `host_inventory` (
	`hostid`                 bigint unsigned                           NOT NULL,
	`inventory_mode`         integer         DEFAULT '0'               NOT NULL,
	`type`                   varchar(256)     DEFAULT ''                NOT NULL,
	`type_full`              varchar(256)     DEFAULT ''                NOT NULL,
	`name`                   varchar(256)     DEFAULT ''                NOT NULL,
	`alias`                  varchar(256)     DEFAULT ''                NOT NULL,
	`os`                     varchar(256)     DEFAULT ''                NOT NULL,
	`os_full`                varchar(256)    DEFAULT ''                NOT NULL,
	`os_short`               varchar(256)     DEFAULT ''                NOT NULL,
	`serialno_a`             varchar(256)     DEFAULT ''                NOT NULL,
	`serialno_b`             varchar(256)     DEFAULT ''                NOT NULL,
	`tag`                    varchar(256)     DEFAULT ''                NOT NULL,
	`asset_tag`              varchar(256)     DEFAULT ''                NOT NULL,
	`macaddress_a`           varchar(256)     DEFAULT ''                NOT NULL,
	`macaddress_b`           varchar(256)     DEFAULT ''                NOT NULL,
	`hardware`               varchar(256)    DEFAULT ''                NOT NULL,
	`hardware_full`          text                                      NOT NULL,
	`software`               varchar(256)    DEFAULT ''                NOT NULL,
	`software_full`          text                                      NOT NULL,
	`software_app_a`         varchar(256)     DEFAULT ''                NOT NULL,
	`software_app_b`         varchar(256)     DEFAULT ''                NOT NULL,
	`software_app_c`         varchar(256)     DEFAULT ''                NOT NULL,
	`software_app_d`         varchar(256)     DEFAULT ''                NOT NULL,
	`software_app_e`         varchar(256)     DEFAULT ''                NOT NULL,
	`contact`                text                                      NOT NULL,
	`location`               text                                      NOT NULL,
	`location_lat`           varchar(256)     DEFAULT ''                NOT NULL,
	`location_lon`           varchar(256)     DEFAULT ''                NOT NULL,
	`notes`                  text                                      NOT NULL,
	`chassis`                varchar(256)     DEFAULT ''                NOT NULL,
	`model`                  varchar(256)     DEFAULT ''                NOT NULL,
	`hw_arch`                varchar(256)     DEFAULT ''                NOT NULL,
	`vendor`                 varchar(256)     DEFAULT ''                NOT NULL,
	`contract_number`        varchar(256)     DEFAULT ''                NOT NULL,
	`installer_name`         varchar(256)     DEFAULT ''                NOT NULL,
	`deployment_status`      varchar(256)     DEFAULT ''                NOT NULL,
	`url_a`                  varchar(256)    DEFAULT ''                NOT NULL,
	`url_b`                  varchar(256)    DEFAULT ''                NOT NULL,
	`url_c`                  varchar(256)    DEFAULT ''                NOT NULL,
	`host_networks`          text                                      NOT NULL,
	`host_netmask`           varchar(256)     DEFAULT ''                NOT NULL,
	`host_router`            varchar(256)     DEFAULT ''                NOT NULL,
	`oob_ip`                 varchar(256)     DEFAULT ''                NOT NULL,
	`oob_netmask`            varchar(256)     DEFAULT ''                NOT NULL,
	`oob_router`             varchar(256)     DEFAULT ''                NOT NULL,
	`date_hw_purchase`       varchar(256)     DEFAULT ''                NOT NULL,
	`date_hw_install`        varchar(256)     DEFAULT ''                NOT NULL,
	`date_hw_expiry`         varchar(256)     DEFAULT ''                NOT NULL,
	`date_hw_decomm`         varchar(256)     DEFAULT ''                NOT NULL,
	`site_address_a`         varchar(256)    DEFAULT ''                NOT NULL,
	`site_address_b`         varchar(256)    DEFAULT ''                NOT NULL,
	`site_address_c`         varchar(256)    DEFAULT ''                NOT NULL,
	`site_city`              varchar(256)    DEFAULT ''                NOT NULL,
	`site_state`             varchar(256)     DEFAULT ''                NOT NULL,
	`site_country`           varchar(256)     DEFAULT ''                NOT NULL,
	`site_zip`               varchar(256)     DEFAULT ''                NOT NULL,
	`site_rack`              varchar(256)    DEFAULT ''                NOT NULL,
	`site_notes`             text                                      NOT NULL,
	`poc_1_name`             varchar(256)    DEFAULT ''                NOT NULL,
	`poc_1_email`            varchar(256)    DEFAULT ''                NOT NULL,
	`poc_1_phone_a`          varchar(256)     DEFAULT ''                NOT NULL,
	`poc_1_phone_b`          varchar(256)     DEFAULT ''                NOT NULL,
	`poc_1_cell`             varchar(256)     DEFAULT ''                NOT NULL,
	`poc_1_screen`           varchar(256)     DEFAULT ''                NOT NULL,
	`poc_1_notes`            text                                      NOT NULL,
	`poc_2_name`             varchar(256)    DEFAULT ''                NOT NULL,
	`poc_2_email`            varchar(256)    DEFAULT ''                NOT NULL,
	`poc_2_phone_a`          varchar(256)     DEFAULT ''                NOT NULL,
	`poc_2_phone_b`          varchar(256)     DEFAULT ''                NOT NULL,
	`poc_2_cell`             varchar(256)     DEFAULT ''                NOT NULL,
	`poc_2_screen`           varchar(256)     DEFAULT ''                NOT NULL,
	`poc_2_notes`            text                                      NOT NULL,
	PRIMARY KEY (hostid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

If Zabbix wants to fix this in the general case, then they should probably implement the fix for 1-byte character sets, such as latin1. That same fix will work for larger character sets as well.

https://mariadb.com/kb/en/library/troubleshooting-row-size-too-large-errors-with-innodb/

Comment by Gutsycat [ 2019 Sep 03 ]

I also updated to MariaDB 10.3.17 and then after some time I roll back to 10.3.16. Now I got

[ERROR] Got error 128 when reading table './zabbix/history'
[ERROR] Got error 128 when reading table './zabbix/history_uint'

Is it relevant to this bug?

Comment by Geoff Montee [ 2019 Sep 03 ]

@Gutsycat,

It's difficult to say. Would you be able to provide more of the MariaDB error log?

Comment by John Deviney [ 2019 Sep 04 ]

I am new to Zabbix. I am installing Zabbix 4.2.5 as docker containers with a co-located MariaDB 10.4.7 docker container. I ran into the same above issue. First I tried disabling innodb-strict-mode and that at least allowed the database to build. Then I looked for another solution. The following mariadb options worked for me on a clean installation.

--innodb-strict-mode=1 \
--innodb_page_size=65536 \
--innodb_log_buffer_size=33554432 \
--innodb_buffer_pool_size=536870912 \
--innodb_default_row_format=dynamic \

Comment by Gutsycat [ 2019 Sep 05 ]

>It's difficult to say. Would you be able to provide more of the MariaDB error log?

What log information are you interested in? These lines are last and no new ones appears for 3 days.

Comment by Geoff Montee [ 2019 Sep 05 ]

@Gutsycat,

OK, thanks for checking. I did some analysis of these error messages:

[ERROR] Got error 128 when reading table './zabbix/history'
[ERROR] Got error 128 when reading table './zabbix/history_uint'

It looks like "error 128" is referring to MariaDB's error code 128, which is the value of HA_ERR_OUT_OF_MEM, and it means that the storage engine ran out of memory. See here:

$ perror 128
OS error code 128:  Key has been revoked
MariaDB error code 128: Out of memory in engine

For InnoDB, it looks like there are a lot of ways that HA_ERR_OUT_OF_MEM can be raised. The root cause is most likely to be low memory in most cases. You may want to ensure that your database server has more than enough memory.

Comment by Vladislavs Sokurenko [ 2019 Sep 20 ]

Fixed in pull request

Comment by Geoff Montee [ 2019 Sep 20 ]

@Vladislavs Sokurenko,

The pull request you referenced changes a couple columns from varchar(64) to varchar(128). For users who still use a 1-byte character set (i.e. latin1), that will not solve the issue. In that case, the columns would need to be changed to varchar(256). I updated my previous comment to include more details about how the table's character set effects the fix.

Comment by dimir [ 2019 Sep 20 ]

If I understand it correctly, we do not officially support non-UTF8 configured database and tables for Zabbix.

Comment by Vladislavs Sokurenko [ 2019 Sep 20 ]

Yes Geoff Montee , as dimir already mentioned UTF-8 is required for Zabbix to work correctly as it is the only supported encoding, it is documented and if someone uses non UTF-8 he will run into other issues.
That is why it is enough to have utf8mb4 (for example latest mysql8 has no such issue as it is default there) Or to change few columns because with utf8mb3 we are very close to the limit. Thank you for your comments, they are very helpful.

By the way, do you know why issue is not present in MySQL 5.7.27 version ?

Comment by Geoff Montee [ 2019 Sep 21 ]

If I understand it correctly, we do not officially support non-UTF8 configured database and tables for Zabbix.

Ah, OK.

By the way, do you know why issue is not present in MySQL 5.7.27 version ?

In MariaDB 10.2 and later, MariaDB recently fixed some buggy row size calculations that were used to validate whether an InnoDB table could safely fit on a page in all cases. See the following bug report and documentation section:

https://jira.mariadb.org/browse/MDEV-19292

https://mariadb.com/kb/en/library/innodb-row-formats-overview/#upgrading-causes-row-size-too-large-errors

As far as I know, MySQL 5.7 still has the original buggy row size calculations, so it can reject "safe" InnoDB tables and allow "unsafe" InnoDB tables with innodb_strict_mode enabled. For example, see this bug report:

https://bugs.mysql.com/bug.php?id=79941

I haven't personally tested MySQL 8.0, so I am not sure how that version behaves.

Comment by Frantisek [ 2019 Sep 22 ]

Hi team, this problem is very important for DEBIAN USERS. I have new fresh install DEBIAN BUSTER for server + repo ZABBIX and I havejust this problem.

LOG MARIADB: - error 1118 (42000) at line 1278: row size too large (> 8126). changing some columns to text or blob may help. in current row format, blob prefix of 0 bytes is stored inline.

Thank you for your time and I hope, the problem can be solved.

Comment by Gustavo Guido [ 2019 Sep 26 ]

Hi everyone

I have the same issue with

centos 6
zabbix_server (Zabbix) 4.2.6
Revision d3bb18bbc4 26 August 2019, compilation time: Aug 26 2019 12:25:14
mysql Ver 15.1 Distrib 10.4.8-MariaDB, for Linux (x86_64) using readline 5.1

Comment by Craig Hopkins [ 2019 Sep 29 ]

Still experiencing this in

Debian 10
Zabbix Server 4.4.0~beta1-1+buster from repo
Maria DB 10.3.17-0+deb10u1

Comment by Vladislavs Sokurenko [ 2019 Sep 29 ]

I am sorry this version is not released yet chopkins

Comment by Frantisek [ 2019 Sep 29 ]

Please, can you test fix for:

DEBIAN 10
Maria DB 10.3.17-0+deb10u1
ZABBIX PROXY (and server too) 4.0.12

New servers with DEBIAN 10 not working with ZABBIX. Thank you for your response.

Comment by Craig Hopkins [ 2019 Sep 30 ]

When is a new beta due out? I can't roll back because I can't see earlier versions in the repo

Comment by dimir [ 2019 Sep 30 ]

beta1 is out. You should be able to apt-get update && apt-get upgrade.

Comment by Craig Hopkins [ 2019 Sep 30 ]

beta1 is what caused the problem

Comment by Vladislavs Sokurenko [ 2019 Sep 30 ]

Could you please be so kind and show output of:

./sbin/zabbix_server -V

Also could you please describe in more detail the issue that you experience ?

Comment by Craig Hopkins [ 2019 Sep 30 ]

# /usr/sbin/zabbix_server -V
zabbix_server (Zabbix) 4.4.0beta1
Revision 52cc5f0786 27 September 2019, compilation time: Sep 24 2019 08:03:24

Copyright (C) 2019 Zabbix SIA
License GPLv2+: GNU GPL version 2 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it according to
the license. There is NO WARRANTY, to the extent permitted by law.

This product includes software developed by the OpenSSL Project
for use in the OpenSSL Toolkit (http://www.openssl.org/).

Compiled with OpenSSL 1.1.1c 28 May 2019
Running with OpenSSL 1.1.1c 28 May 2019

And the problem is:

# /etc/init.d/zabbix-server start
(big pause where nothing happens)
Starting zabbix-server (via systemctl): zabbix-server.service
^C

# tail zabbix_server.log
18672:20190929:161012.411 Starting Zabbix Server. Zabbix 4.4.0beta1 (revision 52cc5f0786).
18672:20190929:161012.411 ****** Enabled features ******
18672:20190929:161012.411 SNMP monitoring: YES
18672:20190929:161012.411 IPMI monitoring: YES
18672:20190929:161012.411 Web monitoring: YES
18672:20190929:161012.411 VMware monitoring: YES
18672:20190929:161012.411 SMTP authentication: YES
18672:20190929:161012.411 ODBC: YES
18672:20190929:161012.411 SSH2 support: YES
18672:20190929:161012.411 IPv6 support: YES
18672:20190929:161012.411 TLS support: YES
18672:20190929:161012.411 ******************************
18672:20190929:161012.411 using configuration file: /etc/zabbix/zabbix_server.conf
18672:20190929:161012.415 current database version (mandatory/optional): 04030031/04030031
18672:20190929:161012.415 required mandatory version: 04030035
18672:20190929:161012.415 starting automatic database upgrade
18672:20190929:161012.429 [Z3005] query failed: [1118] Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. [alter table `host_inventory` modify `name` varchar(128) default '' not null]
18672:20190929:161012.429 database upgrade failed

No matter how long I leave the start script running, it never gets past that prompt

Comment by dimir [ 2019 Sep 30 ]

So it appeared that the upgrade patch that we delivered in beta1 is not working. Fresh installations of beta1 will not have that issue. We will need to fix the upgrade patch somehow, or (if it's not possible) provide users the instructions on how to fix this particular issue.

Comment by dimir [ 2019 Sep 30 ]

The fix will be available in the next release, rc1, that is currently planned on Wednesday.

Comment by Wessel [ 2019 Oct 02 ]

I fixed this by customizing the original docker:

Dockerfile:

FROM zabbix/zabbix-appliance:alpine-4.2-latest
COPY mariadb-server.cnf /etc/my.cnf.d/mariadb-server.cnf

where mariadb-server.cnf is the one from the original docker but with

innodb-strict-mode=1
innodb_page_size=65536
innodb_log_buffer_size=33554432
innodb_buffer_pool_size=536870912
innodb_default_row_format=dynamic

added in the [mysqld] section

Comment by Geoff Montee [ 2019 Oct 02 ]

FYI, for anyone else who wants to work around this by increasing innodb_page_size like John Deviney and Wessel described above:

The "maximum row size" can be increased by increasing innodb_page_size, but innodb_page_size can only be increased if you are starting with an uninitialized datadir. From the MariaDB documentation:

innodb_page_size
Description: Specifies the page size in bytes for all InnoDB tablespaces. The default, 16k, is suitable for most uses.

A larger InnoDB page size can provide a larger maximum row size.
...

This system variable's value cannot be changed after the datadir has been initialized. InnoDB's page size is set when a MariaDB instance starts, and it remains constant afterwards.

https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_page_size

Comment by Vladislavs Sokurenko [ 2019 Oct 02 ]

For a workaround it is enough to disable strict mode before the import or the upgrade of Zabbix schema:

set global innodb_strict_mode='OFF';

After database is imported or upgraded it is safe to enable strict mode again:

set global innodb_strict_mode='ON';

Comment by Craig Hopkins [ 2019 Oct 02 ]

I'd rather not touch my mysql settings. I'l await the release later today

Comment by Vladislavs Sokurenko [ 2019 Oct 02 ]

Fixed in:

pre-4.4.0rc1 (trunk) 2d014b24fd4

Comment by Craig Hopkins [ 2019 Oct 02 ]

When will we see this in the repo?

Comment by dimir [ 2019 Oct 02 ]

Currently planned for tomorrow.

Comment by dimir [ 2019 Oct 04 ]

4.4.0rc1 packages are available.

Comment by Craig Hopkins [ 2019 Oct 04 ]

Installed and running. Much obliged.

Comment by Vladislavs Sokurenko [ 2019 Oct 04 ]

Thank you for your report chopkins

Comment by carlgan [ 2019 Oct 14 ]

same problem met with mariadb 10.4

Comment by Naighel [ 2019 Nov 05 ]

Stumbled upon this issue when installing zabbix 4.0 in a test setup.
Since 4.0 is still supported (LTS), any chance this fix can be ported to 4.0?

Comment by Vladislavs Sokurenko [ 2019 Nov 05 ]

You can look into ~~ZBX-16757~~ for a workaround, unfortunately we don't change schema between minor releases

Comment by Nikita [ 2020 May 05 ]

gmork123 added a comment - 2019 Oct 02 00:35

comment was very helpful for my problem with big database restore (>90 Gb) restore to new server and database!

Thank U!

Generated at Fri Apr 26 22:10:16 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.

[ZBX-16465] Not possible to insert create.sql.gz on MariaDB 10.3.17 Created: 2019 Aug 05 Updated: 2024 Apr 10 Resolved: 2019 Oct 02