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 issues. (Carlos Vicente)
2. Re: Database issues. (Michael T. Voity)
----------------------------------------------------------------------
Message: 1
Date: Thu, 21 Aug 2014 15:18:34 -0400
From: Carlos Vicente <[email protected]>
Subject: Re: [Netdot-users] Database issues.
To: "Michael T. Voity" <[email protected]>
Cc: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=windows-1252
Glad it?s working again!
cv
On Aug 21, 2014, at 3:16 PM, Michael T. Voity <[email protected]> wrote:
> Carlos,
>
> Thank you very much. I think it is fixed.
>
> I first had to "drop fwtable" in order to create those tables
>
> prune_db.pl ran just fine. See dubug below:
>
> The "updatedevices.pl -DIFAT" is working like it should.
>
> Thanks for your help.
>
> -Mike
>
------------------------------
Message: 2
Date: Thu, 21 Aug 2014 15:16:35 -0400
From: "Michael T. Voity" <[email protected]>
Subject: Re: [Netdot-users] Database issues.
To: Carlos Vicente <[email protected]>
Cc: "[email protected]" <[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=windows-1252; format=flowed
Carlos,
Thank you very much. I think it is fixed.
I first had to "drop fwtable" in order to create those tables
prune_db.pl ran just fine. See dubug below:
The "updatedevices.pl -DIFAT" is working like it should.
Thanks for your help.
-Mike
[root@intermapper bin]# prune_db.pl -F -A -d 7 -r -g
bash: prune_db.pl: command not found
[root@intermapper bin]# ./prune_db.pl -F -A -d 7 -r -g
DEBUG - NUM_DAYS(7) ago was : 2014-08-14 15:12:29
DEBUG: init: dbi:mysql:dbname=netdot;host=localhost;mysql_local_infile=1
DEBUG: Connected successfully
DEBUG - DROP TABLE fwtableentry
DEBUG - DROP TABLE fwtable
DEBUG - CREATE TABLE `fwtable` (
`device` bigint(20) NOT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`tstamp` timestamp NOT NULL DEFAULT '1970-01-02 00:00:01',
PRIMARY KEY (`id`),
UNIQUE KEY `fwtable1` (`device`,`tstamp`),
KEY `FWTable2` (`device`),
KEY `FWTable3` (`tstamp`),
CONSTRAINT `fk_device_5` FOREIGN KEY (`device`) REFERENCES `device`
(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DEBUG - CREATE TABLE `fwtableentry` (
`fwtable` bigint(20) NOT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`interface` bigint(20) NOT NULL,
`physaddr` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `FWTableEntry1` (`fwtable`),
KEY `FWTableEntry2` (`interface`),
KEY `FWTableEntry3` (`physaddr`),
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 DEFAULT CHARSET=utf8
INFO - Tables fwtableentry, fwtable rotated successfully
DEBUG: Disconnected successfully
DEBUG: init: dbi:mysql:dbname=netdot;host=localhost;mysql_local_infile=1
DEBUG: Connected successfully
DEBUG - DROP TABLE arpcacheentry
DEBUG - DROP TABLE arpcache
DEBUG - CREATE TABLE `arpcache` (
`device` bigint(20) NOT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`tstamp` timestamp NOT NULL DEFAULT '1970-01-02 00:00:01',
PRIMARY KEY (`id`),
UNIQUE KEY `arpcache1` (`device`,`tstamp`),
KEY `ArpCache2` (`device`),
KEY `ArpCache3` (`tstamp`),
CONSTRAINT `fk_device` FOREIGN KEY (`device`) REFERENCES `device` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
DEBUG - CREATE TABLE `arpcacheentry` (
`arpcache` bigint(20) NOT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`interface` bigint(20) NOT NULL,
`ipaddr` bigint(20) NOT NULL,
`physaddr` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `ArpCacheEntry1` (`arpcache`),
KEY `ArpCacheEntry2` (`interface`),
KEY `ArpCacheEntry3` (`physaddr`),
KEY `ArpCacheEntry4` (`ipaddr`),
CONSTRAINT `fk_arpcache` FOREIGN KEY (`arpcache`) REFERENCES
`arpcache` (`id`),
CONSTRAINT `fk_interface` FOREIGN KEY (`interface`) REFERENCES
`interface` (`id`),
CONSTRAINT `fk_ipaddr` FOREIGN KEY (`ipaddr`) REFERENCES `ipblock`
(`id`),
CONSTRAINT `fk_physaddr` FOREIGN KEY (`physaddr`) REFERENCES
`physaddr` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
INFO - Tables arpcacheentry, arpcache rotated successfully
DEBUG: Disconnected successfully
INFO - ./prune_db.pl total runtime: 0 sec
Michael T. Voity
Network Engineer
University of Vermont
(802) 656-8112
On 8/21/2014 11:13 AM, Carlos Vicente wrote:
> Michael,
>
> So the script cannot create the fwtableentry table:
>
> DBD::mysql::db do failed: Can't create table 'netdot.fwtableentry' (errno:
> 150) at ./prune_db.pl line 355.
> Error rotating tables fwtableentry, fwtable. Changes not commited:
> DBD::mysql::db do failed: Can't create table 'netdot.fwtableentry' (errno:
> 150) at ./prune_db.pl line 355.
>
> >From the mysql manual:
>
> "Cannot create table. If the error message refers to error 150, table
> creation failed because a foreign key constraint was not correctly formed. If
> the error message refers to error ?1, table creation probably failed because
> the table includes a column name that matched the name of an internal InnoDB
> table.?
>
> Looking at the foreign keys in the definition, I don?t see anything wrong,
> but I noticed:
>
> DEBUG - CREATE TABLE `fwtable` (
> `device` bigint(20) DEFAULT NULL,
> `id` bigint(20) NOT NULL AUTO_INCREMENT,
> `tstamp` timestamp NOT NULL DEFAULT '1970-01-02 00:00:01',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 <<<<------
>
>
>
> ENGINE=MyISAM ????
>
> Because prune_db learns the create statement out of the ?show create table?
> output, it seems that at some point you created fwtable as a MyISAM table,
> instead of INNODB.
>
> Try pasting this into the mysql client:
>
> 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;
> 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;
>
>
> And then run prune_db again to see if it runs correctly.
>
> Best,
>
> cv
>
>
>
>
> On Aug 21, 2014, at 10:53 AM, Michael T. Voity <[email protected]> wrote:
>
>> Carlos,
>>
>> Thanks for your reply!
>>
>> Here is the information you requested -
>>
>> Please see the attached file.
>>
>> Ideas?
>>
>> -Mike
>>
>> Michael T. Voity
>> Network Engineer
>> University of Vermont
>> (802) 656-8112
>>
>> On 8/20/2014 10:14 AM, Carlos Vicente wrote:
>>> Michael,
>>>
>>> The rotate option in prune_db is supposed to re-create the fwtable and
>>> fwtableentry for you. If that is not happening, that?s where your problem
>>> is.
>>>
>>> Can you try running it in debug mode, and post back the results? Also, show
>>> your list of tables before and after, etc. Any relevant info that can help
>>> figure this out.
>>>
>>> Best,
>>>
>>> cv
>>>
>>> On Aug 18, 2014, at 3:42 PM, Michael T. Voity <[email protected]> wrote:
>>>
>>>> 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:
>> <netdot-prune-db-debug.txt>
------------------------------
_______________________________________________
Netdot-users mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-users
End of Netdot-users Digest, Vol 69, Issue 7
*******************************************
