[Netdot-devel] [Netdot - Bug #1911] (New) Mysql error: "Can't get last insert id"

r***@osl.uoregon.edu

2017-03-24 12:07:15 UTC

Issue #1911 has been updated by Brian Candler.

Turns out this is a known issue with Class::DBI and mysql 5.7:

https://rt.cpan.org/Public/Bug/Display.html?id=118491

That includes a patch you can make to @/usr/share/perl5/Class/DBI.pm@, and I can confirm this fixes the problem.

Ubuntu has Class::DBI 3.0.17 which is the latest - from October 2007! It seems that Class::DBI is abandonware :-(
----------------------------------------
Bug #1911: Mysql error: "Can't get last insert id"
https://osl.uoregon.edu/redmine/issues/1911#change-3385

Author: Brian Candler
Status: New
Priority: Normal
Assignee:
Category:
Target version:
Resolution:

(Netdot 1.0.7, ubuntu 16.04, mysql)

Problem: "Can't get last insert id" when trying to insert device, either from web interface or CLI.

<pre>
# /usr/local/netdot/bin/updatedevices.pl -H 100.68.100.254 -I -c NetManage
...
INFO - Inserting new Device: 100.68.100.254
ERROR: Error while inserting Device: Can't insert new Device: Can't get last insert id at /usr/local/netdot/bin/updatedevices.pl line 199.
at /usr/local/netdot/bin/updatedevices.pl line 199.
</pre>

After cranking up debugging in etc/Site.conf:

<pre>
log4perl.category.Netdot::Model = DEBUG, Syslog
log4perl.category.Netdot::Model::Device = DEBUG, Syslog
</pre>

then I get:

<pre>
DEBUG - vtp-cndo.ws.nsrc.org type is: Server
INFO - Inserting new Device: 100.68.100.254
DEBUG - Device::insert: Device 2 already exists in DB as vtp-cndo.ws.nsrc.org
WARN - Device::assign_snmp_target: vtp-cndo.ws.nsrc.org: Could not insert snmp_target address: 100.68.100.254: Error while inserting Ipblock: Can't insert new Ipblock: Can't get last insert id at /usr/local/netdot/lib/Netdot/Model/Device.pm line 5693.
at /usr/local/netdot/lib/Netdot/Model/Device.pm line 5693.

DEBUG - vtp-cndo.ws.nsrc.org does not provide a valid base MAC. Using first available interface MAC.
DEBUG - vtp-cndo.ws.nsrc.org: Could not insert base MAC: 5254004378F9: Error while inserting PhysAddr: Can't insert new PhysAddr: Can't get last insert id at /usr/local/netdot/lib/Netdot/Model/Device.pm line 5665.
at /usr/local/netdot/lib/Netdot/Model/Device.pm line 5665.

DEBUG - vtp-cndo.ws.nsrc.org: No suitable base MAC found
ERROR: Error while inserting Asset: Can't insert new Asset: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`netdot`.`asset`, CONSTRAINT `fk_physaddr_1` FOREIGN KEY (`physaddr`) REFERENCES `physaddr` (`id`)) [for Statement "INSERT INTO asset (physaddr, product_id, reserved_for)
VALUES (?, ?, ?)
" with ParamValues: 0=2, 1='1', 2=undef] at /usr/share/perl5/DBIx/ContextualFetch.pm line 52.
at /usr/local/netdot/bin/updatedevices.pl line 199.
</pre>

It's clear the id column is defined correctly as autoincrement:

<pre>
mysql> desc physaddr;
+------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------------------+----------------+
| address | varchar(128) | NO | UNI | NULL | |
| first_seen | timestamp | NO | MUL | 1970-01-02 00:00:01 | |
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| last_seen | timestamp | NO | MUL | 1970-01-02 00:00:01 | |
| static | tinyint(1) | NO | | 0 | |
+------------+--------------+------+-----+---------------------+----------------+
5 rows in set (0.00 sec)
</pre>

And indeed in each case the insert has actually been successful (e.g. the device was inserted with id 2), so when I re-run the updatedevices.pl script it gets a bit further (finding the existing device, existing MAC address, existing asset, existing interface etc)

Aside: I think there's another bug here. Netdot isn't doing its updates within a transaction, or is not rolling back the transaction on exception, and I think it should.

--
You have received this notification because you have either subscribed to it, or are involved in it.
To change your notification preferences, please click here: http://osl.uoregon.edu/redmine/my/account