Send Netdot-users mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
https://osl.uoregon.edu/mailman/listinfo/netdot-users
or, via email, send a message with subject or body 'help' to
[email protected]
You can reach the person managing the list at
[email protected]
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Netdot-users digest..."
Today's Topics:
1. Re: Database update problem (Brian Candler)
2. Re: Database update problem (Roberto Greiner)
3. Re: Database update problem (Roberto Greiner)
4. Re: Database update problem (Brian Candler)
5. Re: Database update problem (Roberto Greiner)
----------------------------------------------------------------------
Message: 1
Date: Fri, 04 Jul 2014 07:22:43 +0100
From: Brian Candler <[email protected]>
Subject: Re: [Netdot-users] Database update problem
To: Roberto Greiner <[email protected]>,
[email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
On 03/07/2014 13:20, Roberto Greiner wrote:
> I'm running NetDot 1.0.6 in a Debian 7 machine, and since yesterday
> I'm getting the following error error through syslog:
>
> Jul 3 09:00:10 leonis netdot: ERROR - Error while updating
> Netdot::Model::Interface: Can't update 936: DBD::mysql::st execute
> failed: Duplicate entry '17-63-vif19.0' for key 'interface1' [for
> Statement "UPDATE interface#012SET doc_status = ?, number =
> ?#012WHERE id=?#012" with ParamValues: 0='snmp', 1='63', 2='936'] at
> /usr/share/perl5/DBIx/ContextualFetch.pm line 52.#012 at
> /usr/local/netdot/lib/Netdot/Model/Device.pm line 6171#012
So: the query it's trying to do is
UPDATE interface SET doc_status='snmp', number=63 where id=936
and is getting a constraint error, presumably because there is some
other interface with number 63.
(1) You can find out which device it is like this:
http://x.x.x.x/netdot/management/interface.html?id=936
Click on the device name and you should go to
http://x.x.x.x/netdot/management/device.html?table=Device&id=<DEVICEID>
Make a note of the device name and ID.
(2) Now run the poller in debug mode for just this one device:
/usr/local/netdot/bin/updatedevices.pl -I -d -H <devicename>
Capture the messages for a few lines before that SQL error. Looking at
the Perl code around line 6171 of Device.pm there should be messages like:
Interface with [name|number] XXX found
and/or
Interface XXX had number: Y
(3) Show the interface table like this:
http://x.x.x.x/netdot/generic/
enter query:
select id,number,name from interface where device=<DEVICEID>
See which interface ID (1st column) currently has number 63 (2nd
column). I'm guessing it's not interface 936. Make a note of its ID.
It's possible that two different interfaces have swapped around, but
with SQL constraints it's very hard to swap two values between two rows.
Netdot would have to delete the two old interfaces and then insert the
new ones and it doesn't have logic for that.
So for now you can help it out. Try setting the interface numbers to
dummy values:
update interface set number=-1 where id=936
update interface set number=-2 where id=<INTERFACEID> -- from query (3)
above
and then try re-running the poller (without the -d debug flag). You
should see a message like
INFO - XXXXX: Interface XXXX had number: -1, now has: 63
Or delete these two interfaces; Netdot should re-insert them. Or delete
the whole device at worst (since you know which one it is now)
Regards,
Brian.
------------------------------
Message: 2
Date: Fri, 04 Jul 2014 08:48:29 -0300
From: Roberto Greiner <[email protected]>
Subject: Re: [Netdot-users] Database update problem
To: Brian Candler <[email protected]>, [email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=ISO-8859-1
Hi,
I ran the recipe and removed both interfaces. But when I ran the
updatedevices script, it crashed with the following message:
root@leonis:/usr/local/netdot/bin# ./updatedevices.pl -I -H
xen-server-ii.ipiranga.unesp.br
INFO - ./updatedevices.pl started at Fri Jul 4 08:27:45 2014
INFO - Updating single device: xen-server-ii.ipiranga.unesp.br
INFO - xen-server-ii.ipiranga.unesp.br: Interface vif19.0 had number:
-1, now has: 63
Netdot::Model::Interface 936 destroyed without saving changes to
doc_status, number at ./updatedevices.pl line 199
ERROR: Error while updating Netdot::Model::Interface: Can't update 936:
DBD::mysql::st execute failed: Duplicate entry '17-63-vif19.0' for key
'interface1' [for Statement "UPDATE interface
SET doc_status = ?, number = ?
WHERE id=?
" with ParamValues: 0='snmp', 1='63', 2='936'] at
/usr/share/perl5/DBIx/ContextualFetch.pm line 52.
at /usr/local/netdot/lib/Netdot/Model/Device.pm line 6171
Curious part is that running the 'select id,number,name from interface
where device=17' (it was device 17) is no longer bringing two interfaces
with the id 63. Looking again (I sorted that same query by number), I
found two entries with numbers 26, 38, 49, 51, 55, among many others.
So, something screwed that server entry badly.
I decided to delete the entire server entry and recreate it. Now the
'select id,number,name from interface where device=<device> order by
number' query no longer show any duplicate number entry.
The conclusion, since it's the second time this happens, I believe, is
that there is something in the snmp answers given by Xen-Server that is
confusing Netdot and making it create duplicate/erroneous interface
entries. Is there someway I could instruct netdot to log all answers it
receives from my xenservers, so in case it breaks again I can send you
this information? It would help find what is making netdot create those
duplicate entries.
Thanks for the help,
Roberto Greiner
On 04/07/2014 03:22, Brian Candler wrote:
> On 03/07/2014 13:20, Roberto Greiner wrote:
>> I'm running NetDot 1.0.6 in a Debian 7 machine, and since yesterday
>> I'm getting the following error error through syslog:
>>
>> Jul 3 09:00:10 leonis netdot: ERROR - Error while updating
>> Netdot::Model::Interface: Can't update 936: DBD::mysql::st execute
>> failed: Duplicate entry '17-63-vif19.0' for key 'interface1' [for
>> Statement "UPDATE interface#012SET doc_status = ?, number =
>> ?#012WHERE id=?#012" with ParamValues: 0='snmp', 1='63', 2='936'] at
>> /usr/share/perl5/DBIx/ContextualFetch.pm line 52.#012 at
>> /usr/local/netdot/lib/Netdot/Model/Device.pm line 6171#012
> So: the query it's trying to do is
>
> UPDATE interface SET doc_status='snmp', number=63 where id=936
>
> and is getting a constraint error, presumably because there is some
> other interface with number 63.
>
> (1) You can find out which device it is like this:
>
> http://x.x.x.x/netdot/management/interface.html?id=936
>
> Click on the device name and you should go to
> http://x.x.x.x/netdot/management/device.html?table=Device&id=<DEVICEID>
>
> Make a note of the device name and ID.
>
> (2) Now run the poller in debug mode for just this one device:
>
> /usr/local/netdot/bin/updatedevices.pl -I -d -H <devicename>
>
> Capture the messages for a few lines before that SQL error. Looking at
> the Perl code around line 6171 of Device.pm there should be messages
> like:
>
> Interface with [name|number] XXX found
> and/or
> Interface XXX had number: Y
>
> (3) Show the interface table like this:
>
> http://x.x.x.x/netdot/generic/
>
> enter query:
> select id,number,name from interface where device=<DEVICEID>
>
> See which interface ID (1st column) currently has number 63 (2nd
> column). I'm guessing it's not interface 936. Make a note of its ID.
>
> It's possible that two different interfaces have swapped around, but
> with SQL constraints it's very hard to swap two values between two
> rows. Netdot would have to delete the two old interfaces and then
> insert the new ones and it doesn't have logic for that.
>
> So for now you can help it out. Try setting the interface numbers to
> dummy values:
>
> update interface set number=-1 where id=936
> update interface set number=-2 where id=<INTERFACEID> -- from query
> (3) above
>
> and then try re-running the poller (without the -d debug flag). You
> should see a message like
>
> INFO - XXXXX: Interface XXXX had number: -1, now has: 63
>
> Or delete these two interfaces; Netdot should re-insert them. Or
> delete the whole device at worst (since you know which one it is now)
>
> Regards,
>
> Brian.
>
--
-----------------------------------------------------
Marcos Roberto Greiner
Os otimistas acham que estamos no melhor dos mundos
Os pessimistas tem medo de que isto seja verdade
James Branch Cabell
-----------------------------------------------------
------------------------------
Message: 3
Date: Fri, 04 Jul 2014 09:19:37 -0300
From: Roberto Greiner <[email protected]>
Subject: Re: [Netdot-users] Database update problem
To: Brian Candler <[email protected]>, [email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=ISO-8859-1
Just got the same error again, but with another Xen-server:
Jul 4 09:00:34 leonis netdot: ERROR - Error while updating
Netdot::Model::Interface: Can't update 1176: DBD::mysql::st execute
failed: Duplicate entry '38-20-vif7.1' for key 'interface1' [for
Statement "UPDATE interface#012SET doc_status = ?, name = ?#012WHERE
id=?#012" with ParamValues: 0='snmp', 1='vif7.1', 2='1176'] at
/usr/share/perl5/DBIx/ContextualFetch.pm line 52.#012 at
/usr/local/netdot/lib/Netdot/Model/Device.pm line 6171#012
Again, the 'select id,number,name from interface where device=<deviceid>
order by number' query show lots of entries with the same number. Again,
I deleted the Server entry and recreated it, cleaning this server's entry.
Roberto Greiner
On 04/07/2014 08:48, Roberto Greiner wrote:
> Hi,
>
> I ran the recipe and removed both interfaces. But when I ran the
> updatedevices script, it crashed with the following message:
>
> root@leonis:/usr/local/netdot/bin# ./updatedevices.pl -I -H
> xen-server-ii.ipiranga.unesp.br
> INFO - ./updatedevices.pl started at Fri Jul 4 08:27:45 2014
> INFO - Updating single device: xen-server-ii.ipiranga.unesp.br
> INFO - xen-server-ii.ipiranga.unesp.br: Interface vif19.0 had number:
> -1, now has: 63
> Netdot::Model::Interface 936 destroyed without saving changes to
> doc_status, number at ./updatedevices.pl line 199
> ERROR: Error while updating Netdot::Model::Interface: Can't update 936:
> DBD::mysql::st execute failed: Duplicate entry '17-63-vif19.0' for key
> 'interface1' [for Statement "UPDATE interface
> SET doc_status = ?, number = ?
> WHERE id=?
> " with ParamValues: 0='snmp', 1='63', 2='936'] at
> /usr/share/perl5/DBIx/ContextualFetch.pm line 52.
> at /usr/local/netdot/lib/Netdot/Model/Device.pm line 6171
>
> Curious part is that running the 'select id,number,name from interface
> where device=17' (it was device 17) is no longer bringing two interfaces
> with the id 63. Looking again (I sorted that same query by number), I
> found two entries with numbers 26, 38, 49, 51, 55, among many others.
> So, something screwed that server entry badly.
>
> I decided to delete the entire server entry and recreate it. Now the
> 'select id,number,name from interface where device=<device> order by
> number' query no longer show any duplicate number entry.
>
> The conclusion, since it's the second time this happens, I believe, is
> that there is something in the snmp answers given by Xen-Server that is
> confusing Netdot and making it create duplicate/erroneous interface
> entries. Is there someway I could instruct netdot to log all answers it
> receives from my xenservers, so in case it breaks again I can send you
> this information? It would help find what is making netdot create those
> duplicate entries.
>
> Thanks for the help,
>
> Roberto Greiner
>
>
> On 04/07/2014 03:22, Brian Candler wrote:
>> On 03/07/2014 13:20, Roberto Greiner wrote:
>>> I'm running NetDot 1.0.6 in a Debian 7 machine, and since yesterday
>>> I'm getting the following error error through syslog:
>>>
>>> Jul 3 09:00:10 leonis netdot: ERROR - Error while updating
>>> Netdot::Model::Interface: Can't update 936: DBD::mysql::st execute
>>> failed: Duplicate entry '17-63-vif19.0' for key 'interface1' [for
>>> Statement "UPDATE interface#012SET doc_status = ?, number =
>>> ?#012WHERE id=?#012" with ParamValues: 0='snmp', 1='63', 2='936'] at
>>> /usr/share/perl5/DBIx/ContextualFetch.pm line 52.#012 at
>>> /usr/local/netdot/lib/Netdot/Model/Device.pm line 6171#012
>> So: the query it's trying to do is
>>
>> UPDATE interface SET doc_status='snmp', number=63 where id=936
>>
>> and is getting a constraint error, presumably because there is some
>> other interface with number 63.
>>
>> (1) You can find out which device it is like this:
>>
>> http://x.x.x.x/netdot/management/interface.html?id=936
>>
>> Click on the device name and you should go to
>> http://x.x.x.x/netdot/management/device.html?table=Device&id=<DEVICEID>
>>
>> Make a note of the device name and ID.
>>
>> (2) Now run the poller in debug mode for just this one device:
>>
>> /usr/local/netdot/bin/updatedevices.pl -I -d -H <devicename>
>>
>> Capture the messages for a few lines before that SQL error. Looking at
>> the Perl code around line 6171 of Device.pm there should be messages
>> like:
>>
>> Interface with [name|number] XXX found
>> and/or
>> Interface XXX had number: Y
>>
>> (3) Show the interface table like this:
>>
>> http://x.x.x.x/netdot/generic/
>>
>> enter query:
>> select id,number,name from interface where device=<DEVICEID>
>>
>> See which interface ID (1st column) currently has number 63 (2nd
>> column). I'm guessing it's not interface 936. Make a note of its ID.
>>
>> It's possible that two different interfaces have swapped around, but
>> with SQL constraints it's very hard to swap two values between two
>> rows. Netdot would have to delete the two old interfaces and then
>> insert the new ones and it doesn't have logic for that.
>>
>> So for now you can help it out. Try setting the interface numbers to
>> dummy values:
>>
>> update interface set number=-1 where id=936
>> update interface set number=-2 where id=<INTERFACEID> -- from query
>> (3) above
>>
>> and then try re-running the poller (without the -d debug flag). You
>> should see a message like
>>
>> INFO - XXXXX: Interface XXXX had number: -1, now has: 63
>>
>> Or delete these two interfaces; Netdot should re-insert them. Or
>> delete the whole device at worst (since you know which one it is now)
>>
>> Regards,
>>
>> Brian.
>>
>
--
-----------------------------------------------------
Marcos Roberto Greiner
Os otimistas acham que estamos no melhor dos mundos
Os pessimistas tem medo de que isto seja verdade
James Branch Cabell
-----------------------------------------------------
------------------------------
Message: 4
Date: Fri, 04 Jul 2014 14:24:17 +0100
From: Brian Candler <[email protected]>
Subject: Re: [Netdot-users] Database update problem
To: Roberto Greiner <[email protected]>,
[email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
On 04/07/2014 12:48, Roberto Greiner wrote:
> Hi,
>
> I ran the recipe and removed both interfaces. But when I ran the
> updatedevices script, it crashed with the following message:
>
> root@leonis:/usr/local/netdot/bin# ./updatedevices.pl -I -H
> xen-server-ii.ipiranga.unesp.br
> INFO - ./updatedevices.pl started at Fri Jul 4 08:27:45 2014
> INFO - Updating single device: xen-server-ii.ipiranga.unesp.br
> INFO - xen-server-ii.ipiranga.unesp.br: Interface vif19.0 had number:
> -1, now has: 63
> Netdot::Model::Interface 936 destroyed without saving changes to
> doc_status, number at ./updatedevices.pl line 199
> ERROR: Error while updating Netdot::Model::Interface: Can't update 936:
> DBD::mysql::st execute failed: Duplicate entry '17-63-vif19.0' for key
> 'interface1' [for Statement "UPDATE interface
> SET doc_status = ?, number = ?
> WHERE id=?
> " with ParamValues: 0='snmp', 1='63', 2='936'] at
> /usr/share/perl5/DBIx/ContextualFetch.pm line 52.
> at /usr/local/netdot/lib/Netdot/Model/Device.pm line 6171
OK. The problem in this case is that the name is not unique (i.e. some
other interface is called "vif19.0")
I thought this meant there are database constraints saying that the
interface number *and* the interface name both have to be unique for any
interface on a given device, and these could conflict during an update(*).
But actually, it turns out that the constraint is only on the number and
name *both* being the same:
(mysql)
UNIQUE KEY `interface1` (`device`,`number`,`name`),
(postgres)
"interface1" UNIQUE CONSTRAINT, btree (device, number, name)
That is: it's OK for two interfaces on a device to have number 4, or two
interfaces on a device to be called Gi1/0/1; but not for there to be two
interfaces which are (4, Gi1/0/1). I'm not sure if this is what Carlos
had intended; it would be stronger to have two constraints, but would
introduce the problem you described.
From your error message it's clear that it's trying to do an update
which would result in two interfaces which both had number 63 *and* name
vif19.0. That looks wrong, unless the Linux box is reporting two rows
with the same number or the same name.
To debug this, the next time this happens can you show the output of:
(1) select id,number,name from interface where device=<deviceid>
(2) updatedevices -I -H <hostname> -d
(3)
snmpwalk -v2c -c <community> <hostname> ifName
snmpwalk -v2c -c <community> <hostname> ifDescr
Regards,
Brian.
(*) If there is a problem like the one I described above, Postgres has a
solution in the form of deferred constraints:
http://stackoverflow.com/questions/5403437/atomic-multi-row-update-with-a-unique-constraint
http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html
which mysql doesn't have:
http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit
What's not at all clear is whether "DEFERRABLE INITIALLY DEFERRED" is
the default or not.
------------------------------
Message: 5
Date: Fri, 04 Jul 2014 10:42:09 -0300
From: Roberto Greiner <[email protected]>
Subject: Re: [Netdot-users] Database update problem
To: Brian Candler <[email protected]>, [email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=ISO-8859-1
On 04/07/2014 10:24, Brian Candler wrote:
> On 04/07/2014 12:48, Roberto Greiner wrote:
>> Hi,
>>
>> I ran the recipe and removed both interfaces. But when I ran the
>> updatedevices script, it crashed with the following message:
>>
>> root@leonis:/usr/local/netdot/bin# ./updatedevices.pl -I -H
>> xen-server-ii.ipiranga.unesp.br
>> INFO - ./updatedevices.pl started at Fri Jul 4 08:27:45 2014
>> INFO - Updating single device: xen-server-ii.ipiranga.unesp.br
>> INFO - xen-server-ii.ipiranga.unesp.br: Interface vif19.0 had number:
>> -1, now has: 63
>> Netdot::Model::Interface 936 destroyed without saving changes to
>> doc_status, number at ./updatedevices.pl line 199
>> ERROR: Error while updating Netdot::Model::Interface: Can't update 936:
>> DBD::mysql::st execute failed: Duplicate entry '17-63-vif19.0' for key
>> 'interface1' [for Statement "UPDATE interface
>> SET doc_status = ?, number = ?
>> WHERE id=?
>> " with ParamValues: 0='snmp', 1='63', 2='936'] at
>> /usr/share/perl5/DBIx/ContextualFetch.pm line 52.
>> at /usr/local/netdot/lib/Netdot/Model/Device.pm line 6171
>
> OK. The problem in this case is that the name is not unique (i.e. some
> other interface is called "vif19.0")
>
> I thought this meant there are database constraints saying that the
> interface number *and* the interface name both have to be unique for
> any interface on a given device, and these could conflict during an
> update(*).
>
> But actually, it turns out that the constraint is only on the number
> and name *both* being the same:
>
> (mysql)
> UNIQUE KEY `interface1` (`device`,`number`,`name`),
> (postgres)
> "interface1" UNIQUE CONSTRAINT, btree (device, number, name)
>
> That is: it's OK for two interfaces on a device to have number 4, or
> two interfaces on a device to be called Gi1/0/1; but not for there to
> be two interfaces which are (4, Gi1/0/1). I'm not sure if this is what
> Carlos had intended; it would be stronger to have two constraints, but
> would introduce the problem you described.
>
> From your error message it's clear that it's trying to do an update
> which would result in two interfaces which both had number 63 *and*
> name vif19.0. That looks wrong, unless the Linux box is reporting two
> rows with the same number or the same name.
>
> To debug this, the next time this happens can you show the output of:
>
> (1) select id,number,name from interface where device=<deviceid>
>
> (2) updatedevices -I -H <hostname> -d
>
> (3)
> snmpwalk -v2c -c <community> <hostname> ifName
> snmpwalk -v2c -c <community> <hostname> ifDescr
>
Ok. As soon as the problem happens again, I will collect this data and
send it.
> Regards,
>
> Brian.
>
> (*) If there is a problem like the one I described above, Postgres has
> a solution in the form of deferred constraints:
>
> http://stackoverflow.com/questions/5403437/atomic-multi-row-update-with-a-unique-constraint
>
> http://www.postgresql.org/docs/9.4/static/sql-set-constraints.html
>
> which mysql doesn't have:
> http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit
>
>
> What's not at all clear is whether "DEFERRABLE INITIALLY DEFERRED" is
> the default or not.
Hmm, I'm using MySQL... :-)
Tks,
Roberto
--
-----------------------------------------------------
Marcos Roberto Greiner
Os otimistas acham que estamos no melhor dos mundos
Os pessimistas tem medo de que isto seja verdade
James Branch Cabell
-----------------------------------------------------
------------------------------
_______________________________________________
Netdot-users mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-users
End of Netdot-users Digest, Vol 68, Issue 3
*******************************************
