[ZBX-11958] SQL Error adding new Value Mapping Created: 2017 Mar 22  Updated: 2024 Apr 10  Resolved: 2017 Mar 24

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: 3.0.8
Fix Version/s: None

Type: Incident report Priority: Minor
Reporter: Daniel Weber Assignee: Unassigned
Resolution: Won't fix Votes: 0
Labels: error, sql, valuemapping
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Single Server Environment.
Centos 7


Team: Team C
Sprint: Sprint 4
Story Points: 1

 Description   

When trying to add a valuemapp from Frontend, it shows the Error:
Error in query [INSERT INTO valuemaps (name,valuemapid) VALUES ('test','25')] [Duplicate entry '25' for key 'PRIMARY']
SQL statement execution has failed "INSERT INTO valuemaps (name,valuemapid) VALUES ('test','25')".

Seems like Zabbix doesn't check the latest number on the Table, f.e. with select MAX(valuemapid) FROM valuemaps;



 Comments   
Comment by richlv [ 2017 Mar 22 ]

that looks like a corrupted database or an attempt to edit the db manually.
you might want to consider discussing this in the support channels, listed at http://zabbix.org/wiki/Getting_help .

Comment by Daniel Weber [ 2017 Mar 23 ]

We haven't manipulated the DB. It was installed a year ago on a Centos 7 over yum.
I'm not sure anymore, if we have imported some of the Templates, etc. Maybe this have created an error?
Where does Zabbix read the Value for valuemapid?

Comment by Gregory Chalenko [ 2017 Mar 23 ]

Values for all key fields are stored in "ids" table, in Your case it will contain row:

table_name field_name nextid
valuemaps valuemapid 25
Comment by Daniel Weber [ 2017 Mar 23 ]

Hello Gregory Chalenko

Thanks for your Reply, yes you're right, the field shows 25, is it save to update this filed manually as a workarround?

So the question for the bug is, which process hasn't updated this field.
Is there a way to find this?

Regards:
Daniel

Comment by Gregory Chalenko [ 2017 Mar 23 ]

Increasing field value to 26 will work but it is not advisable to change Zabbix database manually.

All database writes in Zabbix are wrapped in transactions and for this case it looks like transaction was completed partially. New row were added but ids table was not saved properly.

Comment by Gregory Chalenko [ 2017 Mar 23 ]

Can you please describe steps you did before get this kind of error?

Comment by richlv [ 2017 Mar 23 ]

you can also just remove that entry from the 'ids' table.
some templates needed value maps, for which the only way in the past was to do direct sql updates, you might have done that in the past.

of course, there is a chance that some bug in zabbix has left this problem in the db - but unless there is a repeatable scenario on how to do so, there does not seem to be an indication of a bug here.

Comment by Daniel Weber [ 2017 Mar 23 ]

I'm not really sure, we just try to Import a Template from share Zabbix and had to insert new Valuemaps.

At the beginning of this installation, we have imported some of the Templates and Valuemaps from another Zabbix Installation.
In the mean time we have added machines and templates, but never modified anything directly on the Database.

Comment by Daniel Weber [ 2017 Mar 23 ]

@richlv Thanks, will we still be supportable?
Do i just have to execute: DELETE FROM ids WHERE table_name like 'valuemaps' ?

Comment by richlv [ 2017 Mar 23 ]

well, if you don't have a support contract, it's all just somebody in the internet
but it should be mostly safe, yes. as always, have a full db backup when touching the db.

when you said "had to insert new Valuemaps", which methods did you use - as in, how exactly did you do that all those times ?

Comment by Daniel Weber [ 2017 Mar 23 ]

We used the normal way over the Frontend (Web).
But as i mentioned, at the beginning, we did an Import via Zabbix Import feature.

Yes we have a Support contract, i just wanted to be sure we're not doing something unsupported.

Comment by Gregory Chalenko [ 2017 Mar 23 ]

Deleting row will be bad choice. Much better just update field with something like:

UPDATE ids SET nextid=nextid+1 WHERE table_name = "valuemaps" AND field_name = "valuemapid"
Comment by Gregory Chalenko [ 2017 Mar 24 ]

If you can reproduce this bug please open new issue and describe steps.

Comment by richlv [ 2017 Mar 24 ]

gcalenko, could you please expand a bit on why deleting that entry would be bad ?

Comment by Gregory Chalenko [ 2017 Mar 24 ]

Table data is used for every insert operation, deleting can cause another type of error.

Comment by Daniel Weber [ 2017 Mar 24 ]

Thanks for all the comments.
I rather set the nextid to MAX(valuemapid) + 1 of the table valumaps than deleting it.
Give you a Feedback when done.

Comment by richlv [ 2017 Mar 24 ]

gcalenko, zabbix components tend to recalculate ids if missing - are there any known situation when this could cause problems ?
that would be good to know, as users are relying on this behaviour.

Generated at Fri Apr 26 08:14:34 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.