Ad Widget

**tim.mooney** · 08-07-2020, 23:42

Hi tizer001

My site is like yours: the zabbix database has been upgraded many times over the years. I didn't realize how much the upgraded database schema had diverged from a fresh zabbix schema of the same version, until I needed to debug another database problem (the collation change I needed to do as part of the 3.2.x release had some problems in the process (see https://support.zabbix.com/browse/ZBX-17357 )).

If your database is like mine, it turns out that even though the upgrades had been successful over the years, the database schema has accumulated some differences from what a fresh database would be for the exact same version.

You probably have two options: either import your existing data (only, not schema) into a freshly created schema for the version you're currently at (4.2.3) or identify the places where your existing schema is different from a pristine schema for your version, and then alter your existing schema to match what it should be.

I didn't want the down-time it would require for the export & load with a fresh schema, so what I ended up doing was the second option: fix my existing schema to be what it should be.

To identify all of the differences between existing and fresh, I created a new, empty zabbix database on a different host, using the create.sql.gz for the version I started with. In your case, you want the create.sql.gz from 4.2.3.

Once I had a new, pristine DB on a different host, I needed a way to compare the schema and find any differences. There is a 'mysqldiff' tool from Oracle that Zabbix support told me about, but I had already found a way to export just the schema in a standard format using the 'mysqldump' command, so I used that to export the two schemas. Then I used the GNU diff tool to find all the differences. One type of difference (constraints that were the same but listed in a slightly different order) could be ignored, but the other types I fixed.

Once I had identified the differences, the process was to figure out the SQL command I needed to apply to my schema to get it to match the expected schema. That involved a bunch of web searching and some scripting, so I didn't have to manually type all of the ALTER statements.

**tizer001** · 13-07-2020, 17:53

Thanks for the detailed reply Tim, very much appreciated.
I had a funny feeling it wasn't going to be a quick and simple fix.

I'll give the mysqldiff tool a try out of curiosity and see how things go.

Cheers

Ad Widget

Database upgrade fail - 4.2.3 to 4.4.10 or 5.0.1 - same result

Database upgrade fail - 4.2.3 to 4.4.10 or 5.0.1 - same result

Comment

Comment