Ad Widget

**_SAmiC** · 14-12-2020, 19:54

... just adding some additional info...

Since the "zcat ... " is referencing line 1278 of the .gz file, it seems to be failing when creating the 'host_inventory' TABLE.
So, is that "Row size" (from "...Row size too large (> 8126)") something that can be increased?

_sam

**_SAmiC** · 14-12-2020, 21:36

... ok, i'm back to waiting for assistance ... after doing some Google-search, I've attempted to use the Barracuda format.
Same error results.

Any help will be surely appreciated.

_sam

+++++++
DETAILS
+++++++

did 2 things:

(1) modifed the 50-server.cnf file in

_sam@ubu1804dd:~$ diff 50-server.cnf.ORIG 50-server.cnf
42a43,45
> innodb_large_prefix=1
> innodb_file_per_table=1
> innodb_file_format = Barracuda
_sam@ubu1804dd:~$

(2) changed definition of the 'host_inventory' table by unpacking and then re-zipping the .file being zcat'd

...
81778:CREATE TABLE `host_inventory` (
81810- `hostid` bigint unsigned NOT NULL,
...
87426- PRIMARY KEY (hostid)
87448-) ENGINE=InnoDB
87464: ROW_FORMAT=COMPRESSED
87488- KEY_BLOCK_SIZE=8;
...

added the ROW_FORMAT= and KEY_BLOCK_SIZE= qualifiers.

+++

_sam@ubu1804dd:~$ sudo mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show global variables like 'innodb_large%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | 1 |
+---------------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> show global variables like 'innodb_file%';
+-----------------------+-----------+
| Variable_name | Value |
+-----------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_per_table | ON |
+-----------------------+-----------+
2 rows in set (0.001 sec)

MariaDB [(none)]> show global variables like 'innodb_page%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
+----------------------+-------+
2 rows in set (0.001 sec)

MariaDB [(none)]> create database zabbix character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> create user zabbix@localhost identified by 'XXXXXXX';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant all privileges on zabbix.* to zabbix@localhost;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit;
Bye
_sam@ubu1804dd:~$ 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.
_sam@ubu1804dd:~$

**tim.mooney** · 14-12-2020, 21:52

TL;DR: When the Zabbix 4.0 LTS was released, most sites that were using MariaDB were using older versions of MariaDB than what you're starting with. You might have an easier time if you pair the Zabbix 5.0 LTS with MariaDB 10.3, as that's a combination that's probably been tested more recently and more widely.

The long version:

The problem you're running into isn't anything you've done wrong, and it's not even really something the Zabbix developers have done wrong. MariaDB is a great database, but as time has advanced from the point where it and MySQL forked, some very subtle complexities and in a few cases incompatibilities have arisen. This is one of those cases where most software that says "MySQL or MariaDB" would never tickle this particular compatibility issue, but Zabbix just happens to. It's also complicated for the Zabbix developers to even spell out, because it seems like this particular issue differs based on which 10.x version you're using. I'm not sure I even understand all the subtleties, and I've looked at this issue extensively, both because of Zabbix and because of another software package (Moodle) my workplace uses that has also run into the same issue.

First, I don't care how many web sites you find that say "I just ran 'SET INNODB_STRICT_MODE = 0;' and then it worked", I don't recommend doing that.

Next, I'm not surprised that "innodb_large_prefix" is empty at MariaDB 10.3, because the MariaDB developers (mistakenly!) removed it at 10.3.x after too-short a deprecation period, only to reinstate it at 10.4.x and then again eventually remove it. See: https://mariadb.com/kb/en/innodb-sys...b_large_prefix The setting is basically the default after 10.2.1 as long as you're using InnoDB for the file format and you're using one of the newer row formats (see below).

I'm also not surprised that "innodb_file_format' is empty. Older versions of MariaDB (and MySQL) supported a couple of possible file formats that were both "InnoDB" but that had different capabilities. Again, the developers deprecated the setting in early 10.x (because everything was going to be Barracuda) and then removed the setting in 10.3.x, only to reinstate it for a while and then ultimately finally remove it. See: https://mariadb.com/kb/en/innodb-sys...db_file_format

With all of that background in place, what I suspect the issue might be is the row format. For a good overview on the possible row formats that InnoDB supports, see: https://mariadb.com/kb/en/innodb-row-formats-overview/

The older "COMPACT" row format is a problem (so was "REDUNDANT", but that's even older). The newer "DYNAMIC" format is what you want to be using. DYNAMIC was only ever available if you were using the Barracuda file format, which is why you often see "innodb_file_format" mentioned, but everything is Barracuda with 10.2.x and later, so that's not an issue.

First thing you should do is use the procedure from the row formats overview to check what row format your Zabbix tables are using: https://mariadb.com/kb/en/innodb-row...les-row-format . We need to know if they are DYNAMIC, or something else.

Based on the error message you're receiving and the fact that you said this was a new install, I'm guessing that they are in DYNAMIC format. DYNAMIC should be the default for a freshly created table at 10.3.x, unless your distro has (unwisely) set innodb_default_row_format to something else. Even then, it would probably be COMPRESSED, which should also be fine (but not what I would use on a Zabbix install).

Since your error message specifically mentions "< 8126", that also implies that your install is using the default value of 16K for the innodb_page_size. That's good too, but know that setting a larger system-wide page size, combined with a DYNAMIC row format, can allow you longer row limits. I'm not suggesting you do that (yet), but it is one option.

The other common thing that influences how much data gets stored in a row is what character set your tables are using. I can explain if necessary (ask in a follow-up if the explanation from https://mariadb.com/kb/en/troublesho...archar-columns isn't clear), but for now, you should verify that you're using the recommended character set and collation when creating your database.

Finally, the MariaDB developers fixed a serious, long-standing bug (it's present in MySQL too, I haven't looked to see when it was fixed there, presumably around the same time) with row length calculation at MariaDB 10.3.17. Prior to that, tables could be created with column data that technically couldn't be accessed. It means that MariaDB 10.3.17 and later became more safe by being more strict about table creation. See https://jira.mariadb.org/browse/MDEV-19292 and notice that one of the sites that ran into the issue was using Zabbix 4.2. Notice too in the example that Geoff lists in the 2nd comment that many of the columns are VARCHAR(64). As previously mentioned, with certain character sets (latin1, utf8) that would count against the row length, but with CHARACTER SET 'utf8mb4', it doesn't.

Considering Zabbix 5.0 LTS is out, fresh installs of it with recent MariaDB have probably gotten a lot more testing than a fresh install of Zabbix 4.0 LTS with recent MariaDB. My site is using Zabbix 4.4.x with MariaDB 10.2, but that's not a fresh install; it's been updated many times, and in particular when I updated to 4.0.x, 4.2.x, and then 4.4.x, we were using MariaDB 5.5.x. It's possible that if I tried a fresh install of 4.4.x, I would run into the same issue you are seeing. Since you're just starting out, though, it might be beneficial for you to start with 5.0 LTS.

**_SAmiC** · 14-12-2020, 22:34

thanks so much... your analysis and detailed explanation are extremely helpful.
I will take your closing advice and simply create a new installation with ZABBIX v5 LTS.

_sam

**_SAmiC** · 15-12-2020, 02:03

... ZABBIX v5 LTS install successful.
Thanks again!

_sam

Ad Widget

error 1118 during copy of host import initial schema and data.

error 1118 during copy of host import initial schema and data.

Comment

Comment

Comment

Comment

Comment