[ZBX-7617] database node conversion fails in 2.2.1 Created: 2014 Jan 07  Updated: 2017 May 30  Resolved: 2014 Jan 08

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Documentation (D)
Affects Version/s: 2.2.1
Fix Version/s: 2.0.11, 2.2.2

Type: Incident report Priority: Major
Reporter: jean-marc CHORIER Assignee: Martins Valkovskis
Resolution: Fixed Votes: 0
Labels: dm
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

CentOS
MySQL

Issue Links:
Duplicate
is duplicated by ZBX-7667 database conversion to master node faild Closed

 Description   

I just install zabbix from official rpm and try to convert to multi node

  1. /usr/sbin/zabbix_server -n 2 -c /etc/zabbix/zabbix_server.conf
    Dropping foreign keys ....................................................................................................................................... done.
    Converting tables ............................................................................................................ done.
    Creating foreign keys .................................................................................................................................zabbix_server [3254]: Error: [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.<result 2 when explaining filename '#sql-79c_19118'>, CONSTRAINT `c_sessions_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE) [ALTER TABLE `sessions` ADD CONSTRAINT `c_sessions_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE]
    ......Conversion failed.

Centos

  1. cat /etc/centos-release
    CentOS release 6.2 (Final)

Mysql & Zabbix 2.2.1

  1. rpm -qa |grep mysql
    mysql-server-5.1.69-1.el6_4.x86_64
    zabbix-server-mysql-2.2.1-1.el6.x86_64
    php-mysql-5.3.3-23.el6_4.x86_64
    mysql-libs-5.1.69-1.el6_4.x86_64
    zabbix-web-mysql-2.2.1-1.el6.noarch
    mysql-5.1.69-1.el6_4.x86_64

 Comments   
Comment by jean-marc CHORIER [ 2014 Jan 07 ]

with all frontends windows close it works
I stop httpd daemon et re run my test and it works

please append the documentation

thanks

Comment by Oleksii Zagorskyi [ 2014 Jan 07 ]

I faced already with this problem once for 2.2 default DB. My log record was:

zabbix_server [14555]: Error: [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix22node1`.`#sql-32c7_1fb`, CONSTRAINT `c_sessions_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE) [ALTER TABLE `sessions` ADD CONSTRAINT `c_sessions_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE]
......Conversion failed.

but I had opened that zabbix frontend (which can perform automatic page refreshes) in browser during conversion, maybe even several tabs !

The problem happened because between tables "sessions" and "users" conversion I've got row(s) update for "sessions" table.
Then during foreign keys -recreation we get the error.

Later I tried to reproduce the scenario 4 times on default 2.2 DB and was not able to catch the error anymore.
So it may happen not always, supposedly depends on environment.

Conclusion: do close all tabs with zabbix frontend before conversion !!!

We just need to add a red note (after existing one) that it's strongly recommended to stop Apache (it's indeed provides guaranteed result) before the conversion step:
https://www.zabbix.com/documentation/2.0/manual/distributed_monitoring/nodes
https://www.zabbix.com/documentation/2.2/manual/distributed_monitoring/nodes

martins-v RESOLVED in both suggested places. Please review.

zalex_ua I think it's enough.
CLOSED

Generated at Sat Apr 27 07:24:09 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.