zabbix db export from 2.4, import to 5.0 Row size too large error

[[email protected]]

Wondering if someone can assist w/ this problem. Background: Current instance of zabbix 2.4 w/ mysql db. Installed new instance of zabbix 5.0 w/ mariadb on debian 9. New install is working fine but I want to take the data from the 2.4 instance and load it into the 5.0 instance so I tried the following.

1 Tested mysql dump & restore basic process using the essentially empty new 5.0 install zabbix db. These were the steps I folllowed: a - stop zabbix server & agent on 5.0 system. b - used mysqldump to dump the 5.0 zabbix db to a file, dropped the zabbix database, then recreated and then usedmysql to restore the 5.0 data as a test. c - restarted zabbix server & agent. d - tested zabbix 5.0. As far as I could tell, there are no issues.

2 - Tried the same procedure using the 2.4 data imported into the 5.0 instance. a - dump 2.4 zabbix db to file, transfer to zabbix 5.0 server, b - stopped zabbix server/agent, c - imported zabbix 2.4 dump file.

At this point the import reported an error:


ERROR 1118 (42000) at line 1750: 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.

Google of this error yields a number of discussions on the work around or fix for this; all of them are fairly technical for a non-DBA. Some examples:

https://stackoverflow.com/questions/...to-te/33655143
https://community.centminmod.com/thr...ge-8126.16129/

A common suggestion is to set innodb_strict_mode = 0 but other people indicate this is not safe. Also it seems like to set this I need to re-create the mysql instance completely, not simply restart the process. Again, I'm not a DBA so these details are sort of beyond me. I'm wondering if anyone in the zabbix community have encountered this error and has simple suggestions to fix.

Thanks,

[tim.mooney]

What version of MySQL are you using on the system running your new 5.0.x install?

Have you read the Known Issues document , especially the section about "Upgrade with MariaDB 10.2.1 and before"? I know that it says it applies to MariaDB, but I believe that's incorrect. I think it can apply to some versions of MySQL too.

Disabling innodb_strict_mode is a workaround, but it's not the best solution. What is better is to ensure that your database tables have their "row_format" converted to support larger row lengths (and larger index sizes). That process is discussed in the ZBX issue linked from the known issues document.

It's my personal opinion that the ZBX issue linked doesn't go far enough: rather than just upgrading the row_format for one table, I think you will have better results in the future if you make certain that all your tables in your zabbix database have been upgraded in the same way.

[[email protected]]

apparently I can't comment directly on your post, maybe because I haven't passed the 2 posts approved by moderators yet...

mariadb Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

in which case I don't think the secion w/ mariadb 10.2.1. and before applies but you did give me an idea. If I understand mariadb and mysql docs (which I probably don't, not being a dba), the version of mariadb I'm using has row_format=dynamic by default, whereas the older versions of mysql and mariadb have it as compact. So maybe the mysqldump from the 2.4 instance is explicitly setting the row_format to compact? I did run a grep -i row_format against the 2.4 instance mysql dump file but it didn't return anything. I'm not trying modifying the original 2.4 instance db host table row_format to dynamic, dumping the db and then importing that dump into the 5.0 / maria db instance. I'll let you know what happens.

[tim.mooney]

mariadb Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

in which case I don't think the secion w/ mariadb 10.2.1. and before applies but you did give me an idea.

Since you're doing an SQL dump and reload and loading the dump into a MariaDB version later than 10.2.1, it shouldn't apply to you. I don't believe the 'row_format' is something that's preserved in the mysqldump output (nor should it be, really). The note in the Known issues really applies to the binary database files that have been originally created with an older version of MySQL or MariaDB and then the database server version has been upgraded. The row_format doesn't automatically switch from COMPACT to DYNAMIC under an in-place upgrade scenario.

If I understand mariadb and mysql docs (which I probably don't, not being a dba), the version of mariadb I'm using has row_format=dynamic by default

That should be true for 10.3.x, yes. It's possible to override that with a configuration setting, though, so to verify that your Linux distro hasn't don't something odd, you may want to connect to your MariaDB server via the 'mysql' command line client and run

show variables like '%row%';

That will output a few matches, but you should see 'dynamic' for 'innodb_default_row_format'. If you don't, then your server won't create new rows (such as when you load your SQL dump from your old server) using the dynamic format, which is needed for longer record length and index support in the recent version.

So maybe the mysqldump from the 2.4 instance is explicitly setting the row_format to compact? I did run a grep -i row_format against the 2.4 instance mysql dump file but it didn't return anything. I'm not trying modifying the original 2.4 instance db host table row_format to dynamic, dumping the db and then importing that dump into the 5.0 / maria db instance. I'll let you know what happens.

I don't think the old mysqldump would be. I've never seen row_format mentioned in any of the mysqldump files I've generated over the years.

Keep in mind that all of this applies only to InnoDB tables. Your old 2.4 install used InnoDB for all its tables, correct?