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. Database issues. (Michael T. Voity)
----------------------------------------------------------------------
Message: 1
Date: Mon, 18 Aug 2014 15:42:19 -0400
From: "Michael T. Voity" <[email protected]>
Subject: [Netdot-users] Database issues.
To: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Hello,
I am ever plagued with a db issue that can not seem to shake with the fwt.
Back story -
Every couple of hours when the 'updatedevices.pl -DIFAT' or
'updatedevices.pl -DFA' run, I get the following error:
WARN - Problem inserting FWT entry: DBD::mysql::st execute failed:
Cannot add or update a child row: a foreign key constraint fails
(`netdot`.`fwtableentry`, CONSTRAINT `fk_fwtable` FOREIGN KEY
(`fwtable`) REFERENCES `fwtable` (`id`)) [for Statement "INSERT INTO
fwtableentry
(fwtable,interface,physaddr)
VALUES (?, ?, (SELECT id FROM
physaddr WHERE address=?))" with ParamValues: 0='3288', 1='32823',
2='001D098E6826'] at /usr/local/share/perl5/DBIx/ContextualFetch.pm line 52
Every week when 'prune_db.pl' runs it deletes the 'fwtableentry' table
when it goes to do a rotate. When this happens I can not search for
mac address.
To recreate the table I do:
SET FOREIGN_KEY_CHECKS = 0;
SET AUTOCOMMIT = 0;
CREATE TABLE `fwtableentry` (
`fwtable` bigint NOT NULL,
`id` bigint NOT NULL auto_increment,
`interface` bigint NOT NULL,
`physaddr` bigint NOT NULL,
INDEX `FWTableEntry1` (`fwtable`),
INDEX `FWTableEntry2` (`interface`),
INDEX `FWTableEntry3` (`physaddr`),
PRIMARY KEY (`id`),
CONSTRAINT `fk_fwtable` FOREIGN KEY (`fwtable`) REFERENCES `fwtable`
(`id`),
CONSTRAINT `fk_interface_1` FOREIGN KEY (`interface`) REFERENCES
`interface` (`id`),
CONSTRAINT `fk_physaddr_3` FOREIGN KEY (`physaddr`) REFERENCES
`physaddr` (`id`)
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
I noticed that the index's of 'fwtable1' , 'fwtable2' and 'fwtable3'
are missing. So I'm thinking this may have something to do with it.
If I drop the 'fwtable' table and then recreate it doing -
CREATE TABLE `fwtable` (
`device` bigint NOT NULL,
`id` bigint NOT NULL auto_increment,
`tstamp` timestamp NOT NULL DEFAULT '1970-01-02 00:00:01',
UNIQUE INDEX `fwtable1` (`device`, `tstamp`),
INDEX `FWTable2` (`device`),
INDEX `FWTable3` (`tstamp`),
PRIMARY KEY (`id`),
CONSTRAINT `fk_device_5` FOREIGN KEY (`device`) REFERENCES `device`
(`id`)
) ENGINE=InnoDB;
How many problems have i just created? Or do you think I fixed it?
Any help would be nice! I really don't want to lose the data that is
in there for IP address and hosts.
Thanks,
-Mike
--
Michael T. Voity
Network Engineer
University of Vermont
------------------------------
_______________________________________________
Netdot-users mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-users
End of Netdot-users Digest, Vol 69, Issue 3
*******************************************
