[ZBX-23853] Message "query failed due to primary key constraint:" in history syncer process. Created: 2023 Dec 18  Updated: 2024 Sep 30  Resolved: 2024 Apr 03

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: None
Fix Version/s: 6.0.29rc1, 6.4.14rc1, 7.0.0beta3, 7.0 (plan)

Type: Problem report Priority: Major
Reporter: Kazuo Ito Assignee: Mihails Prihodko
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

6.0.23
Windows OS

Attachments: Zip Archive zabbix_agent-6.0.26rc1-windows-amd64-openssl.zip    
Issue Links:
Causes
Duplicate
is duplicated by ZBX-25316 Zabbix 6.0 violates Primary Key on 'h... Closed
Team: Team B
Sprint: S2401-1, S2401-2, Prev.Sprint, S24-W10/11
Story Points: 5

 Description   

In 6.0.23, the same issue as ZBX-22695 occurred where the last 6 digits of ns were 0.
I checked the history_log and found that "OS time return" occurred at the same time.

zbx_timespec function, but if ts->sec is in the past than last_ts.sec, the time is not corrected.

#if defined(_WINDOWS) || defined(__MINGW32__)
	if (last_ts.sec == ts->sec && (last_ts.ns == ts->ns ||
			(last_ts.ns + corr >= ts->ns && 1000000 > (last_ts.ns + corr - ts->ns))))
#else
	if (last_ts.ns == ts->ns && last_ts.sec == ts->sec)
#endif
	{
		ts->ns = last_ts.ns + (++corr);

		while (ts->ns >= 1000000000)
		{
			ts->sec++;
			ts->ns -= 1000000000;
		}
	}
	else
	{
		last_ts.sec = ts->sec;
		last_ts.ns = ts->ns;
		corr = 0;
	}


 Comments   
Comment by Mihails Prihodko [ 2024 Mar 12 ]

Summary

As far as we understood, this problem happened when the operating system clock shifted back due to time synchronization. This led to duplicate timestamp values of agent checks, which could not be saved to the database because of the primary key constraint.

The problem was fixed on the Zabbix agent side. We ensured that the sequence of timestamp values does not decrease as long as time is shifted backward for no more than 1 second. In this case, a correction of +1 nanosecond is applied to the previous timestamp until the clock reading reaches a time moment in the future compared to the last corrected value.

Comment by Mihails Prihodko [ 2024 Mar 28 ]

Available in versions:

Generated at Sun May 18 07:29:17 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.