2.4 to 3.4 database upgrade failed

[ptera]

So I wanted to test this without messing with our server in production. I created a new Centos 7 virtual 500 gig HD, 8 cores and 16gig ram.

I backed up the zabbix database to my nas server as SQL file.

I then restored the backup to the new system.

Installed zabbix 3.4 with the packages.

But here is the end result and I am not able to find any solution if there is one.

14744:20171031:192710.761 Starting Zabbix Server. Zabbix 3.4.3 (revision 73588).
 14744:20171031:192710.761 ****** Enabled features ******
 14744:20171031:192710.761 SNMP monitoring:           YES
 14744:20171031:192710.761 IPMI monitoring:           YES
 14744:20171031:192710.761 Web monitoring:            YES
 14744:20171031:192710.761 VMware monitoring:         YES
 14744:20171031:192710.761 SMTP authentication:       YES
 14744:20171031:192710.761 Jabber notifications:      YES
 14744:20171031:192710.761 Ez Texting notifications:  YES
 14744:20171031:192710.761 ODBC:                      YES
 14744:20171031:192710.761 SSH2 support:              YES
 14744:20171031:192710.761 IPv6 support:              YES
 14744:20171031:192710.761 TLS support:               YES
 14744:20171031:192710.761 ******************************
 14744:20171031:192710.761 using configuration file: /etc/zabbix/zabbix_server.conf
 14744:20171031:192710.769 current database version (mandatory/optional): 02050032/02050032
 14744:20171031:192710.769 required mandatory version: 03040000
 14744:20171031:192710.769 optional patches were found
 14744:20171031:192710.769 starting automatic database upgrade
 14744:20171031:192710.779 [Z3005] query failed: [1005] Can't create table 'zabbix.#sql-394d_6' (errno: 150) [alter table application_prototype add constraint c_application_prototype_1 foreign key (itemid) references items (itemid) on delete cascade]
 14744:20171031:192710.780 database upgrade failed

Is it not possible to upgrade from 2.4 to 3.4?

[kaspars.mednis]

Hello,

zabbix documentation says that

Direct upgrade to Zabbix 3.4 is possible from Zabbix 3.2.x, 3.0.x, 2.4.x, 2.2.x and 2.0.x. For upgrading from earlier versions consult Zabbix documentation for 2.0 and earlier.

There is some mysql-related issue. Are all your tables using innodb engine ?
you can check it with following sql query

SELECT engine, count(*) as TABLES FROM information_schema.TABLES WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema') GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;

Regards,
Kaspars

[ptera]

MariaDB [(none)]> SELECT engine, count(*) as TABLES FROM information_schema.TABLES WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema') GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;
+--------+--------+
| engine | TABLES |
+--------+--------+
| MyISAM |    103 |
| InnoDB |      1 |
+--------+--------+
2 rows in set (0.03 sec)

What? I do not know what to do with this...
Do I drop this database - switch the Default table storage to InnoDB and then restore the database again?

[kaspars.mednis]

Not necessary to drop all tables and restore...

you can convert all your tables to InnoDB online

SET @DB_NAME = 'your_database';
 
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DB_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

This code is from here http://www.speedemy.com/mysql/17-key...torage_engine/

and switch to InnoDB is highly recomended, because MyISAM is not the best MySQL engine imho

P.S
this site has a really good tutorial to start tuning yor mysql DB

http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/

at least, set your INNODB_BUFFER_POOL_SIZE right, it will give huge performance boost, the default mysql settings are just not good enough for production environment


Good luck with that and feel free to ask more questions if needed,
Kaspars

[ptera]

OK converting now...

[ptera]

Here is my my.cnf

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

symbolic-links=0
big-tables
max_connections = 512

innodb_buffer_pool_size = 13G
innodb_file_per_table = 1
default-storage-engine = InnoDB


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




!includedir /etc/my.cnf.d

[kaspars.mednis]

If your server is dedicated mysql server with at least 16G of ram, this config seems good. First - avoid swapping, its better to have a little bit smaller mysql pool size than have bigger and part of that go into swap... the main idea is to give as much ram as possible to mysql, but avoid swapping. In perfect scenario all your db will fit into RAM, but remember that some server memory is needed free for other needs - os caches, processes etc.

Regards,
Kaspars

[ptera]

MariaDB [zabbix]> SELECT engine, count(*) as TABLES FROM information_schema.TABLES WHERE table_schema in ('zabbix') GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;                                                             +--------+--------+
| engine | TABLES |
+--------+--------+
| InnoDB |    104 |
+--------+--------+
1 row in set (0.15 sec)

Now starting the zabbix server...

19461:20171102:112831.709 Starting Zabbix Server. Zabbix 3.4.3 (revision 73588).
 19461:20171102:112831.710 ****** Enabled features ******
 19461:20171102:112831.710 SNMP monitoring:           YES
 19461:20171102:112831.710 IPMI monitoring:           YES
 19461:20171102:112831.710 Web monitoring:            YES
 19461:20171102:112831.710 VMware monitoring:         YES
 19461:20171102:112831.710 SMTP authentication:       YES
 19461:20171102:112831.710 Jabber notifications:      YES
 19461:20171102:112831.710 Ez Texting notifications:  YES
 19461:20171102:112831.710 ODBC:                      YES
 19461:20171102:112831.710 SSH2 support:              YES
 19461:20171102:112831.710 IPv6 support:              YES
 19461:20171102:112831.710 TLS support:               YES
 19461:20171102:112831.710 ******************************
 19461:20171102:112831.710 using configuration file: /etc/zabbix/zabbix_server.conf
 19461:20171102:112831.716 current database version (mandatory/optional): 03030004/03030004
 19461:20171102:112831.716 required mandatory version: 03040000
 19461:20171102:112831.716 optional patches were found
 19461:20171102:112831.716 starting automatic database upgrade
 19461:20171102:112831.751 [Z3005] query failed: [1025] Error on rename of './zabbix/dservices' to './zabbix/#sql2-6eb-28' (errno: 152) [alter table dservices drop foreign key c_dservices_2]
 19461:20171102:112831.752 database upgrade failed

[ptera]

Looks like it tried but failed again right after converting to INNODB

 19336:20171102:112713.933 Starting Zabbix Server. Zabbix 3.4.3 (revision 73588).
 19336:20171102:112713.933 ****** Enabled features ******
 19336:20171102:112713.933 SNMP monitoring:           YES
 19336:20171102:112713.933 IPMI monitoring:           YES
 19336:20171102:112713.933 Web monitoring:            YES
 19336:20171102:112713.933 VMware monitoring:         YES
 19336:20171102:112713.933 SMTP authentication:       YES
 19336:20171102:112713.933 Jabber notifications:      YES
 19336:20171102:112713.934 Ez Texting notifications:  YES
 19336:20171102:112713.934 ODBC:                      YES
 19336:20171102:112713.934 SSH2 support:              YES
 19336:20171102:112713.934 IPv6 support:              YES
 19336:20171102:112713.934 TLS support:               YES
 19336:20171102:112713.934 ******************************
 19336:20171102:112713.934 using configuration file: /etc/zabbix/zabbix_server.conf
 19336:20171102:112713.946 current database version (mandatory/optional): 02050032/02050032
 19336:20171102:112713.946 required mandatory version: 03040000
 19336:20171102:112713.946 optional patches were found
 19336:20171102:112713.947 starting automatic database upgrade
 19336:20171102:112714.004 completed 0% of database upgrade
 19336:20171102:112714.073 completed 1% of database upgrade
 19336:20171102:112714.178 completed 2% of database upgrade
 19336:20171102:112714.292 completed 3% of database upgrade
 19336:20171102:112714.402 completed 4% of database upgrade
 19336:20171102:112714.444 completed 5% of database upgrade
 19336:20171102:112714.465 completed 6% of database upgrade
 19336:20171102:112714.502 completed 7% of database upgrade
 19336:20171102:112715.026 completed 8% of database upgrade
 19336:20171102:112715.310 completed 9% of database upgrade
 19336:20171102:112715.360 completed 10% of database upgrade
 19336:20171102:112715.399 completed 11% of database upgrade
 19336:20171102:112715.506 completed 12% of database upgrade
 19336:20171102:112715.589 completed 13% of database upgrade
 19336:20171102:112715.666 completed 14% of database upgrade
 19336:20171102:112715.701 completed 15% of database upgrade
 19336:20171102:112715.774 completed 16% of database upgrade
 19336:20171102:112715.846 completed 17% of database upgrade
 19336:20171102:112715.936 completed 18% of database upgrade
 19336:20171102:112716.025 completed 19% of database upgrade
 19336:20171102:112716.093 completed 20% of database upgrade
 19336:20171102:112716.171 completed 21% of database upgrade
 19336:20171102:112716.264 completed 22% of database upgrade
 19336:20171102:112716.271 completed 23% of database upgrade
 19336:20171102:112716.309 completed 24% of database upgrade
 19336:20171102:112717.334 completed 25% of database upgrade
 19336:20171102:112717.438 completed 26% of database upgrade
 19336:20171102:112717.511 completed 27% of database upgrade
 19336:20171102:112717.595 completed 28% of database upgrade
 19336:20171102:112719.696 completed 29% of database upgrade
 19336:20171102:112719.724 completed 30% of database upgrade
 19336:20171102:112719.747 Action "Report problems to Zabbix administrators" condition "Trigger value = PROBLEM" will be removed during database up
grade: this type of condition is not supported anymore
 19336:20171102:112719.748 Action "Report problems to Zabbix administrators via email" condition "Trigger value = PROBLEM" will be removed during d
atabase upgrade: this type of condition is not supported anymore
 19336:20171102:112719.748 Action "Report problems to Mike via email" condition "Trigger value = PROBLEM" will be removed during database upgrade:
this type of condition is not supported anymore
 19336:20171102:112720.721 completed 31% of database upgrade
 19336:20171102:112721.041 completed 32% of database upgrade
 19336:20171102:112721.272 completed 33% of database upgrade
 19336:20171102:112721.349 completed 34% of database upgrade
 19336:20171102:112722.715 completed 35% of database upgrade
 19336:20171102:112725.870 completed 36% of database upgrade
 19336:20171102:112725.950 completed 37% of database upgrade
 19336:20171102:112726.034 completed 38% of database upgrade
 19336:20171102:112726.126 completed 39% of database upgrade
 19336:20171102:112726.170 completed 40% of database upgrade
 19336:20171102:112726.256 completed 41% of database upgrade
 19336:20171102:112726.316 completed 42% of database upgrade
 19336:20171102:112728.571 completed 43% of database upgrade
 19336:20171102:112728.638 completed 44% of database upgrade
 19336:20171102:112728.880 completed 45% of database upgrade
 19336:20171102:112730.389 completed 46% of database upgrade
 19336:20171102:112730.411 [Z3005] query failed: [1025] Error on rename of './zabbix/dservices' to './zabbix/#sql2-6eb-1c' (errno: 152) [alter tabl
e dservices drop foreign key c_dservices_2]
 19336:20171102:112730.411 database upgrade failed

[ptera]

SHOW ENGINE INNODB STATUS;

LATEST FOREIGN KEY ERROR
------------------------
171102 12:58:28 Error in dropping of a foreign key constraint of table "zabbix"."dservices",
in SQL command
alter table dservices drop foreign key c_dservices_2
Cannot find a constraint with the given id "c_dservices_2".

[ptera]

MariaDB [zabbix]> ALTER TABLE dservices ADD CONSTRAINT c_dservices_2 FOREIGN KEY (dserviceid) REFERENCES applications (dseviceid) ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'zabbix.#sql-5cf_24' (errno: 150)

[ptera]

So I had to create the missing CONSTRAINTS one by one util the upgrade finally finished, Here is one of the mysql commands used.

ALTER TABLE sessions  ADD CONSTRAINT c_sessions_1 FOREIGN KEY (userid) REFERENCES users (userid) ON DELETE CASCADE;

Just increased my knowledge of mysql these last few days