Ad Widget

**tim.mooney** · 11-06-2020, 19:24

If you search the forums for that particular error message, you will see that several other people have encountered the same issue, when upgrading an existing database as part of the 5.0.x Zabbix upgrade.

What version of MySQL or MariaDB or Percona are you using for the database?

If you look at the "Known Issues" document that's part of the upgrade notes: https://www.zabbix.com/documentation...n/known_issues , there's a section on "Upgrade with MariaDB 10.2.1 and before" and it links to ZBX-17690 . My theory is the same underlying problem (older versions used row_format = COMPACT, which limited some sizes and Zabbix 5.x is now running into those limits) in that support issue is also what's causing the index issue that you and other people have run into.

How to fix the problem depends (I think) partially on which MySQL/MariaDB/Percona you're using and what version.

**Alfista** · 12-06-2020, 07:58

I have version: Server version: 5.6.48 MySQL Community Server (GPL)

**tim.mooney** · 12-06-2020, 22:08

Ok, thanks for providing that information.

The short answer is that you probably need to convert (upgrade?) your database tables to row_format=DYNAMIC and then set innodb_large_prefix=1 and then redo the zabbix upgrade to 5.x.

The long answer:

MySQL and MariaDB (and I assume, Percona) support multiple different file formats and row formats when using InnoDB. The MySQL 5.6 documentation has a good overview of this here: https://dev.mysql.com/doc/refman/5.6...ow-format.html

If you look at the first chart on that page, you can see that there's a column for "Large Index Key Prefix Support". That relates to how large an index can be. The important thing to realize from that chart is that it's not just dependent on the MySQL version you're using, it also depends on what "row format" your database is using (and it depends on a MySQL setting too, but the setting only has any effect for certain row formats).

Older versions of MySQL (and MariaDB before about 10.2.1) defaulted to using row_format=COMPACT with InnoDB. So, if your zabbix database was created with MySQL 5.1 or 5.5 and then upgraded to MySQL 5.6, the database is probably still using row_format=COMPACT. You can tell for certain if you connect to your MySQL database using the 'mysql' command line client and issue the command:

Code:

SHOW TABLE STATUS FROM zabbix;

(assuming your database for the Zabbix install is named 'zabbix'). That will output a lot of information, but the 4th column in the output will tell you the "Row_format" that each table in the database is using. If it says "Compact" then your InnoDB database tables are still using the 'COMPACT' format. You can read more about what that means in the MySQL documentation URL above.

Newer versions of MySQL and MariaDB are now defaulting to 'row_format=DYNAMIC' for new databases, but older databases aren't auto-converted to this format. So, if you were to create a brand new 'zabbix_new' database and create some tables in it, your MySQL version would probably (I'm not certain, I haven't looked up what 5.6.x uses by default) default to row_format=DYNAMIC.

With row_format=COMPACT tables, you can't have an index larger than 767 bytes, andZabbix 5.x is now requiring larger indexes for some tables. That means that the table needs to be converted from row_format=COMPACT to one of the row_formats that supports large indexes. Since row_format=DYNAMIC is the new default, it probably makes sense to use that. You've encountered one table (items) that requires a large index, but there may be more. I haven't looked at the new schema, so I don't know specifically which tables must be converted.

For my workplace, I'm using MariaDB, which is extremely similar to MySQL. We've been using Zabbix for a long time and the database has been through many upgrades. Because it has been upgraded many times, it has accumulated some oddities that wouldn't exist with a brand new install. In the past few weeks, to prepare for eventually upgrading to Zabbix 5.x, I've done the following database maintenance:

Upgrade the MariaDB software version from 5.5.x to 10.0, then 10.1, and finally 10.2. I'm currently using 10.2.22
Because my site was using the wrong collation (utf8_general_ci) prior to Zabbix 3.2.x, when I upgraded from 3.0.x to 3.2.x and then 4.2.x, I had to ALTER TABLE on all our tables, to change the collation to the required 'utf8_bin' (see the upgrade notes for Zabbix 3.2.x). Unfortunately, the procedure that was provided to alter the tables had a bug in it where it lost the default value for many columns. See ZBX-17357 for more info on that. I had to identify all the columns that were missing default values (there were a lot) and ALTER TABLE to re-apply the default value.
While examining my schema for the collation issue, I also discovered that compared to a brand new schema for the same database version, my database had dozens of INDEXes that should no longer be present. They were all things that didn't get cleaned up properly as part of past Zabbix upgrade. I identified all of them and removed them, to get my schema to match what a brand new database schema would be.
The Zabbix "Known issues" document for the 5.x upgrade ( https://www.zabbix.com/documentation...n/known_issues) already mentioned having to convert the "hosts" table to row_format=DYNAMIC, see ZBX-17690 for additional details. Since that issue was present and other people were posting on the forums about the index issue you've run into, I started researching the row_format and decided that for my site, the best idea was to upgrade all our tables to row_format=DYNAMIC. I should finish that project today. Most of the tables could be converted online, but a couple of the tables (history, history_uint) were too large for my system's tmpdir, so I've had to restart MariaDB with different settings to be able to complete those tables.

That's what I'm doing for my site, to prepare for the 5.x upgrade.

**Alfista** · 15-06-2020, 08:52

Hi Tim,

thanks for answer. I have these version of MySQL longer but you have right I have there row_format=COMPACT.
I have used the double script as described but it will not change the row format. It has only done hogher precision on stored numbers. So I have tried thois command:

Code:

ALTER TABLE 'zabbix'  ROW_FORMAT=DYNAMIC;

but I only get this error:

Code:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''zabbix' ROW_FORMAT=DYNAMIC' at line 1

I have also check the database name:

Code:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| zabbix |
+--------------------+

but it looks OK.

I forgote to tell you that I'm logged as a root to the MySQL, to perform all changes.

Please is possible to help me how to convert the database that IO can use it without loosing the data and how I set the row format in default to dynamic?

Add the output from your command.

Thanks.

Attached Files

Zabbix-MySQL.zip (3.2 KB, 77 views)

**tim.mooney** · 15-06-2020, 19:55

The mistake is that you provided the database name, not one of the table names within that database. When you're converting a table's row_format, you have to specify the table name. You have to do this for each table that you want to convert.

So, doing something like:

Code:

$mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 108444
Server version: 10.2.22-MariaDB-log MariaDB Server

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)]> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [zabbix]> ALTER TABLE `hosts` row_format = dynamic;

Query OK, 0 rows affected (1.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [zabbix]> ALTER TABLE `items` row_format = dynamic;

Query OK, 0 rows affected (1.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

Keep in mind that the "MariaDB [zabbix]>" is the prompt, and the query results are shown too, so if you were going to convert just the 'items' and 'hosts' tables, you would type just the line shown on the prompt.

**Alfista** · 16-06-2020, 07:58

Hi Tim,

OK thanks, I will do it.
Is possible to convert all rows in all tables in the whole database at once?
And how I can chcekc and tell that all other databases will have row_format dynamic?

Thanks.

Attached Files

MySQL.zip (10.8 KB, 65 views)

**tim.mooney** · 16-06-2020, 23:02

I ran into a similar problem when converting a couple of tables (history and history_uint). My error messages were slightly different, even between the two tables, but the underlying cause was the same: lack of temp space when converting the largest tables.

MySQL/MariaDB/Percona use the 'tmpdir' setting as temporary space when doing certain types of ALTER TABLE operations. It's not clear to me if every type of ALTER TABLE requires a temporary copy or if only certain types of table alterations do, but at least in this case, where you're converting the row_format for the entire table, MySQL will create a full copy of the table in whatever your tmpdir is set to.

That means that whatever filesystem or volume the MySQL tmpdir is pointing to needs to be at least as large as your largest table that you are going to alter. For safety, and because the DYNAMIC tables sometimes seem to take more space than COMPACT tables, I would recommend that you have at least 20% more free space in whatever tmpdir is than the size of your largest table.

In my case, I could do every table except 2 of them with Zabbix online. After 'history' and 'history_uint' failed their conversion (after like 45 minutes), what I had to do was

shut down zabbix_server (and zabbix_agent on the server)
shut down MariaDB. The 'tmpdir' setting is not dynamic, so you cannot change it on the fly.
modify the 'my.cnf' in the [mysqld] section, change 'tmpdir = /tmp' to some filesystem that had lots more space and that the mysql user could write to. I chose 'tmpdir = /var/tmp'
restart MariaDB
re-run the "ALTER TABLE" statements for the 2 largest tables. This took hours to complete and as part of the final process of putting the new altered table into place, it used about 2.2 to 2.5 times the size of the table in the volume containing the zabbix database. So you don't just need extra space in tmpdir, you also need it (temporarily) on the filesystem with your database.
once the alteration was complete, shut down MariaDB again and revert the 'tmpdir' setting back to '/tmp'

Keep in mind that the Zabbix docs do not say that converting 'history' or 'history_uint' to row_format=DYNAMIC is required. They actually only mention one table, the 'hosts' table, but as you and many other have discovered, that's not enough. At least the 'items' table also needs to be converted. I don't know what other tables would fail the Zabbix 5.x upgrade, but I chose to proactively convert all my tables to DYNAMIC, to get the benefits of the new default format, and to avoid any "surprises".

**Alfista** · 17-06-2020, 08:09

Hi Tim,

I dont have set the 'tmpdir' in the my.cnf but in variables I have seen that is directed to the /tmp folder.
If the /tmp folder is on root partition (/) then I should have there about 22G free space if I see correct the 'history_uint' table have 16G. I thing it should be enough.

This is my.cnf:

Code:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLE S

tmp_table_size=256M
max_heap_table_size=256M

[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#[mysqld]
#log_error=/var/log/mysql/mysql_error.log

#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

This are my storages:

Code:

[root@ZabbixServer ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 5.8G 0 5.8G 0% /dev
tmpfs 5.8G 0 5.8G 0% /dev/shm
tmpfs 5.8G 57M 5.8G 1% /run
tmpfs 5.8G 0 5.8G 0% /sys/fs/cgroup
/dev/mapper/centos-root 50G 29G 22G 58% /
/dev/sda1 1014M 888M 127M 88% /boot
/dev/mapper/centos-home 66G 33M 66G 1% /home
tmpfs 1.2G 0 1.2G 0% /run/user/0

You thing, I should move it temporarili to the /home partition, where is more space?

And what the warnings by converting the tables? Where I can find them?

Thanks.

**Alfista** · 18-06-2020, 15:20

Hi Tim,

thanks for all.
I have found that the warnings was misunderstanding, while it is needed to have the Barracuda. When I have set it manualy to Barracuda, it started to convert correctly.

Thanks.

Ad Widget

Upgrade database failed after ugrade to Zabbix 5...

Upgrade database failed after ugrade to Zabbix 5...

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment