Send Netdot-devel mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
https://osl.uoregon.edu/mailman/listinfo/netdot-devel
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-devel digest..."
Today's Topics:
1. Re: [Netdot-users] Seeking feedback about DBMS support
(Phil Regnauld)
2. [Netdot - Bug #1674] (Resolved) Netdot 1.0.1 fails to insert
overlong sysdescription into PostgreSQL ([email protected])
3. [SCM] UNNAMED PROJECT branch netdot-1.0 updated.
netdot-1.0.1-47-gd073fb9 ([email protected])
4. Re: [Netdot-users] Seeking feedback about DBMS support
(Ebo Thompson)
5. [Netdot - Bug #1691] (New) DeviceModule dups problem
([email protected])
6. [Netdot - Bug #1675] (Resolved) Netdot 1.0.1 with localized
PostgreSQL ([email protected])
----------------------------------------------------------------------
Message: 1
Date: Tue, 25 Sep 2012 21:12:05 +0200
From: Phil Regnauld <[email protected]>
Subject: Re: [Netdot-devel] [Netdot-users] Seeking feedback about DBMS
support
To: Ebo Thompson <[email protected]>
Cc: netdot-users <[email protected]>, netdot-devel
<[email protected]>
Message-ID: <[email protected]>
Content-Type: text/plain; charset=us-ascii
Ebo Thompson (ebothompson) writes:
> Hi,
>
> Postgres db. The overiding factor diafavouring mysql is simply oracle!?
I think that was Carlos-humor :) The reasons are primarily technical, I
believe.
Cheers,
Phil
------------------------------
Message: 2
Date: Tue, 25 Sep 2012 12:55:38 -0700
From: [email protected]
Subject: [Netdot-devel] [Netdot - Bug #1674] (Resolved) Netdot 1.0.1
fails to insert overlong sysdescription into PostgreSQL
To: [email protected], [email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=utf-8
Issue #1674 has been updated by Carlos Vicente.
Status changed from New to Resolved
Assignee set to Carlos Vicente
Target version changed from 1.0.1 to 1.0.2
Resolution set to fixed
----------------------------------------
Bug #1674: Netdot 1.0.1 fails to insert overlong sysdescription into PostgreSQL
https://osl.uoregon.edu/redmine/issues/1674#change-2966
Author: Petr ?ech
Status: Resolved
Priority: Normal
Assignee: Carlos Vicente
Category: DeviceManagement
Target version: 1.0.2
Resolution: fixed
Hi,
I've been happy netdot user for some time now. As I like Postgresql more than
mysql I installed the new version with it. When discovering Cisco Aironet 1130
I get the following error:
INFO - hsan73.ooegkk.at: SNMP target address set to XXX.XXX.XXX.60
ERROR: Error while updating Device: Can't update 24: DBD::Pg::st execute
failed: FEHLER: Wert zu lang f?r Typ character varying(255) [for Statement
"UPDATE device
SET sysdescription = ?, last_updated = ?, stp_type = ?, os = ?, snmp_target
= ?, sysname = ?, asset_id = ?, syslocation = ?, layers = ?
WHERE id=?
" with ParamValues: 1='Cisco Aironet 1130 Series (IEEE 802.11a/g) Access Point
Cisco IOS Software, C1130 Software (C1130-K9W7-M), Version 12.4(25d)JA1,
RELEASE SOFTWARE (fc1)
Technical Support: http://www.cisco.com/techsupport
Copyright (c) 1986-2011 by Cisco Systems, Inc.
Compiled Thu 11-Aug-11 02:44 by prod_rel_team', 2='2012/08/14 11:32:34',
3=undef, 4='12.4(25d)JA1', 5='113', 6='HSAN73.XXX.XX', 7='144', 8=undef,
9='00000010', 10='24'] at /usr/share/perl5/DBIx/ContextualFetch.pm line 52.
at ./updatedevices.pl line 188
Device 24 destroyed without saving changes to sysdescription, last_updated,
stp_type, os, snmp_target, sysname, asset_id, syslocation, layers at
./updatedevices.pl line 0
The PostgreSQL error message is in German and it reads "Value to long for type
varchar(255)". I've solved/worked around with the following change
--- Netdot/Model/Device.pm.orig 2012-08-09 18:57:23.000000000 +0200
+++ Netdot/Model/Device.pm 2012-08-14 11:48:17.256739344 +0200
@@ -740,7 +740,7 @@
$dev{physaddr} = $sinfo->b_mac() || $sinfo->mac();
$dev{sysname} = $sinfo->name();
$dev{router_id} = $sinfo->root_ip();
- $dev{sysdescription} = $sinfo->description();
+ $dev{sysdescription} = substr $sinfo->description(), 0, 255;
$dev{syscontact} = $sinfo->contact();
if ( $hashes{'e_descr'} ){
my $first_idx ;
The same should probably be made to the other fields.
--
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
------------------------------
Message: 3
Date: Tue, 25 Sep 2012 12:56:51 -0700
From: [email protected]
Subject: [Netdot-devel] [SCM] UNNAMED PROJECT branch netdot-1.0
updated. netdot-1.0.1-47-gd073fb9
To: [email protected]
Message-ID: <[email protected]>
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "UNNAMED PROJECT".
The branch, netdot-1.0 has been updated
via d073fb95d00541a0519ee837d6554cc176074925 (commit)
via 458bfa4e37760caa8924e76f1c1d75ad2d3d65fa (commit)
via 5791ea18e60c50a8d4bd6c5677b5f1feb6da680f (commit)
via a5a073f2042608c06ee30a9f45a0f784e21348af (commit)
from ccb6aec224a0ea1c2c97912da1e0d6752aca40ce (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit d073fb95d00541a0519ee837d6554cc176074925
Author: Carlos Vicente <[email protected]>
Date: Tue Sep 25 15:53:58 2012 -0400
Avoid errors from Pg when value for varchar field is too long
diff --git a/lib/Netdot/Model.pm b/lib/Netdot/Model.pm
index ea2d06c..25302c2 100644
--- a/lib/Netdot/Model.pm
+++ b/lib/Netdot/Model.pm
@@ -21,6 +21,7 @@ Netdot::Model - Netdot implementation of the Model layer (of
the MVC architectur
my %defaults;
my $logger = Netdot->log->get_logger("Netdot::Model");
+my $db_type = __PACKAGE__->config->get('DB_TYPE');
# Tables to exclude from audit
my %EXCLUDE_AUDIT = (
@@ -64,7 +65,6 @@ BEGIN {
$Netdot::VERSION, $schema_version));
}
-
###########################################################
# Copy stored object in corresponding history table
# - After updating
@@ -531,7 +531,7 @@ sub search_like {
# Not a foreign key, regex this sucker
} else {
# Fix lack of typecast introduced in Pg 8.x
- if (Netdot->config->get('DB_TYPE') eq "Pg") {
+ if ( $db_type eq 'Pg' ) {
push @qual, "UPPER(CAST($column AS text)) LIKE UPPER(?)";
push @bind, $value;
} else {
@@ -814,12 +814,13 @@ sub update {
$class->_adjust_vals(args=>$argv, action=>'update');
- while ( my ($col, $val) = each %$argv ){
- my $a = blessed($self->$col) ? $self->$col->id : $self->$col;
- my $b = blessed($val) ? $val->id : $val;
- if ( (!defined $a || !defined $b) || (defined $a && defined $b &&
$a ne $b) ){
- $self->set($col=>$b);
- push @changed_keys, $col;
+ { # Avoid warnings when comparing to undef
+ no warnings 'uninitialized';
+ while ( my ($col, $val) = each %$argv ){
+ if ( $self->$col ne $val ){
+ $self->set($col=>$val);
+ push @changed_keys, $col;
+ }
}
}
}
@@ -1196,7 +1197,17 @@ sub _adjust_vals{
my %meta_columns;
map { $meta_columns{$_->name} = $_ } $class->meta_data->get_columns;
foreach my $field ( keys %$args ){
- my $mcol = $meta_columns{$field} || $class->throw_fatal("Cannot find
$field in $class metadata");
+ my $mcol = $meta_columns{$field} ||
+ $class->throw_fatal("Cannot find $field in $class metadata");
+
+ # Shorten string if necessary (affects Pg only)
+ # This has effect when the values are not the result of user input
+ # In that case, user gets an error message
+ if ( $mcol->sql_type eq 'varchar' &&
+ defined $mcol->length && ($mcol->length < length($args->{$field}))
){
+ $args->{$field} = substr($args->{$field}, 0, $mcol->length);
+ }
+ # Deal with NULL values properly
if ( !blessed($args->{$field}) &&
(!defined($args->{$field}) || $args->{$field} eq '' ||
$args->{$field} eq 'null' || $args->{$field} eq 'NULL' ) ){
@@ -1208,8 +1219,8 @@ sub _adjust_vals{
$class->throw_fatal("Netdot::Model::_adjust_vals: sql_type
not defined for $field");
}
if ( $mcol->sql_type =~ /^integer|bigint|bool$/o ) {
- $logger->debug(sub{sprintf("Model::_adjust_vals: Setting
empty field '%s' type '%s' to 0.",
- $field, $mcol->sql_type) });
+ $logger->debug(sub{sprintf("Model::_adjust_vals: Setting
empty field '%s' ".
+ "type '%s' to 0.", $field,
$mcol->sql_type) });
$args->{$field} = 0;
}else{
# Insert NULL instead of ""
commit 458bfa4e37760caa8924e76f1c1d75ad2d3d65fa
Author: Carlos Vicente <[email protected]>
Date: Tue Sep 25 12:29:33 2012 -0400
Use bigserial instead of serial with Pg
diff --git a/lib/Netdot/Meta/SQLT.pm b/lib/Netdot/Meta/SQLT.pm
index 7bb399d..4a2c25b 100644
--- a/lib/Netdot/Meta/SQLT.pm
+++ b/lib/Netdot/Meta/SQLT.pm
@@ -43,7 +43,7 @@ sub new{
=head2 sql_schema - Generate SQL code to create schema
Arguments:
- type - DBMS [MySQL|Pg]
+ type - DBMS [MySQL|PostgreSQL]
Returns:
scalar containing SQL code
Examples:
@@ -55,6 +55,11 @@ sub sql_schema {
$t->parser( sub{ return $self->_parser(@_) } ) or croak $t->error;
$t->producer($type) or croak $t->error;
my $output = $t->translate() or croak $t->error;
+ if ( $type eq 'PostgreSQL' ){
+ # Bug in SQLT
+ # https://rt.cpan.org/Public/Bug/Display.html?id=58420
+ $output =~ s/serial NOT NULL/bigserial NOT NULL/smg;
+ }
return $output;
}
commit 5791ea18e60c50a8d4bd6c5677b5f1feb6da680f
Author: Carlos Vicente <[email protected]>
Date: Tue Sep 25 10:21:06 2012 -0400
Fixed bug in asset search
diff --git a/htdocs/management/asset_tasks.html
b/htdocs/management/asset_tasks.html
index c8d44b3..0abc8d8 100644
--- a/htdocs/management/asset_tasks.html
+++ b/htdocs/management/asset_tasks.html
@@ -228,18 +228,25 @@ if ( $submit ){
my $installed = Asset->get_installed_hash()
unless $search_type eq 'all';
- # Search for labels in Asset table and foreign objects recursively
- my $r1 = $ui->select_query(table=>'Asset', terms=>[$search]);
+ my @assets;
+ if ( $search ){
+ # Search for labels in Asset table and foreign objects recursively
+ my $r1 = $ui->select_query(table=>'Asset', terms=>[$search]);
+ @assets = values %$r1;
- # Search for other fields in Asset table
- my @where;
- foreach my $field ( qw/custom_serial inventory_number reserved_for
+ # Search for other fields in Asset table
+ my @where;
+ foreach my $field ( qw/custom_serial inventory_number reserved_for
description info po_number maint_contract/ ){
- push @where, { $field => { '-like' => '%'.$search.'%' } };
+ push @where, { $field => { '-like' => '%'.$search.'%' } };
+ }
+ push @assets, Asset->search_where(\@where);
+ }else{
+ # No criteria given. Get all.
+ @assets = Asset->retrieve_all();
}
- my @assets = Asset->search_where(\@where);
my %res;
- for ( @assets, values %$r1 ) {
+ for ( @assets ) {
if ( $search_type eq 'all' ||
($search_type eq 'installed' && $installed->{$_->id}) ||
($search_type eq 'not installed' && !exists
$installed->{$_->id}) ){
commit a5a073f2042608c06ee30a9f45a0f784e21348af
Author: Carlos Vicente <[email protected]>
Date: Tue Sep 25 10:07:42 2012 -0400
Small fix in SQL statement that affects Pg users
diff --git a/lib/Netdot/Exporter.pm b/lib/Netdot/Exporter.pm
index b6f9e0c..c8a47c0 100644
--- a/lib/Netdot/Exporter.pm
+++ b/lib/Netdot/Exporter.pm
@@ -90,7 +90,7 @@ sub get_device_info {
LEFT JOIN devicecontacts ON d.id=devicecontacts.device
LEFT JOIN contactlist ON
contactlist.id=devicecontacts.contactlist
LEFT JOIN bgppeering ON d.id=bgppeering.device
- WHERE d.monitored=1
+ WHERE d.monitored='1'
AND i.device=d.id
AND d.name=rr.id
AND rr.zone=zone.id
-----------------------------------------------------------------------
Summary of changes:
htdocs/management/asset_tasks.html | 23 +++++++++++++++--------
lib/Netdot/Exporter.pm | 2 +-
lib/Netdot/Meta/SQLT.pm | 7 ++++++-
lib/Netdot/Model.pm | 33 ++++++++++++++++++++++-----------
4 files changed, 44 insertions(+), 21 deletions(-)
hooks/post-receive
--
UNNAMED PROJECT
------------------------------
Message: 4
Date: Tue, 25 Sep 2012 21:18:06 +0000
From: Ebo Thompson <[email protected]>
Subject: Re: [Netdot-devel] [Netdot-users] Seeking feedback about DBMS
support
To: Phil Regnauld <[email protected]>
Cc: netdot-users <[email protected]>, netdot-devel
<[email protected]>
Message-ID:
<CA+ojiv9XMKAJqd4eGEzc9iV6G9UNZ+JzbjzZtaHfc=mktqu...@mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"
Hi,
It actually is a very good point. See what they did to openoffice and
opensolars.
Plus for postgres.
Cheers
On Sep 25, 2012 7:12 PM, "Phil Regnauld" <[email protected]> wrote:
> Ebo Thompson (ebothompson) writes:
> > Hi,
> >
> > Postgres db. The overiding factor diafavouring mysql is simply oracle!?
>
> I think that was Carlos-humor :) The reasons are primarily
> technical, I
> believe.
>
> Cheers,
> Phil
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://osl.uoregon.edu/pipermail/netdot-devel/attachments/20120925/6a0c47c2/attachment-0001.html
------------------------------
Message: 5
Date: Wed, 26 Sep 2012 06:13:03 -0700
From: [email protected]
Subject: [Netdot-devel] [Netdot - Bug #1691] (New) DeviceModule dups
problem
To: [email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=utf-8
Issue #1691 has been reported by Carlos Vicente.
----------------------------------------
Bug #1691: DeviceModule dups problem
https://osl.uoregon.edu/redmine/issues/1691
Author: Carlos Vicente
Status: New
Priority: High
Assignee: Carlos Vicente
Category: DeviceManagement
Target version: 1.0.2
Resolution:
{{{
Sep 25 12:01:11 netdot: ERROR - Error while inserting DeviceModule: Some
values are duplicated Error details: Can't insert new DeviceModule:
DBD::mysql::st execute failed: Duplicate entry '3326-1000000' for key 2 [for
Statement "INSERT INTO devicemodule (last_updated, fru, date_installed, number,
device, description, contained_in, asset_id, type, class, pos) VALUES (?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?) " with ParamValues: 0='2012/09/25 12:01:11', 1=0,
2='2012/09/25 12:01:11', 3=1000000, 4='3326', 5='Juniper MX960 Internet
Backbone Router', 6='00000000', 7='673', 8='jnxChassisMX960', 9='chassis',
10='01000000'] at /usr/lib/perl5/site_perl/5.8.8/DBIx/ContextualFetch.pm line
52. at /usr/local/netdot/lib/Netdot/Model/Device.pm line 5475
Sep 25 12:01:11 netdot: ERROR - Error while inserting DeviceModule: Some
values are duplicated Error details: Can't insert new DeviceModule:
DBD::mysql::st execute failed: Duplicate entry '3326-1000000' for key 2 [for
Statement "INSERT INTO devicemodule (last_updated, fru, date_installed, number,
device, description, contained_in, asset_id, type, class, pos) VALUES (?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?) " with ParamValues: 0='2012/09/25 12:01:11', 1=0,
2='2012/09/25 12:01:11', 3=1000000, 4='3326', 5='Juniper MX960 Internet
Backbone Router', 6='00000000', 7='673', 8='jnxChassisMX960', 9='chassis',
10='01000000'] at /usr/lib/perl5/site_perl/5.8.8/DBIx/ContextualFetch.pm line
52. at /usr/local/netdot/lib/Netdot/Model/Device.pm line 5475
Sep 25 12:02:03 netdot: ERROR - Error while inserting DeviceModule: Some
values are duplicated Error details: Can't insert new DeviceModule:
DBD::mysql::st execut
e failed: Duplicate entry '2081-1000000' for key 2 [for Statement "INSERT INTO
devicemodule (last_updated, fru, date_installed, number, device, description,
contained_in, asset_i
d, type, class, pos) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " with
ParamValues: 0='2012/09/25 12:02:03', 1=0, 2='2012/09/25 12:02:03', 3=1000000,
4='2081', 5='Juniper M120 Inte
rnet Backbone Router', 6='00000000', 7='1985', 8='jnxChassisM120', 9='chassis',
10='01000000'] at /usr/lib/perl5/site_perl/5.8.8/DBIx/ContextualFetch.pm line
52. at /usr/local/n
etdot/lib/Netdot/Model/Device.pm line 5475
Sep 25 12:02:03 netdot: ERROR - Error while inserting DeviceModule: Some
values are duplicated Error details: Can't insert new DeviceModule:
DBD::mysql::st execute failed: Duplicate entry '2081-1000000' for key 2 [for
Statement "INSERT INTO devicemodule (last_updated, fru, date_installed, number,
device, description, contained_in, asset_id, type, class, pos) VALUES (?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?) " with ParamValues: 0='2012/09/25 12:02:03', 1=0,
2='2012/09/25 12:02:03', 3=1000000, 4='2081', 5='Juniper M120 Internet Backbone
Router', 6='00000000', 7='1985', 8='jnxChassisM120', 9='chassis',
10='01000000'] at /usr/lib/perl5/site_perl/5.8.8/DBIx/ContextualFetch.pm line
52. at /usr/local/netdot/lib/Netdot/Model/Device.pm line 5475
}}}
--
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
------------------------------
Message: 6
Date: Wed, 26 Sep 2012 07:53:32 -0700
From: [email protected]
Subject: [Netdot-devel] [Netdot - Bug #1675] (Resolved) Netdot 1.0.1
with localized PostgreSQL
To: [email protected], [email protected]
Message-ID: <[email protected]>
Content-Type: text/plain; charset=utf-8
Issue #1675 has been updated by Carlos Vicente.
Category set to DeviceManagement
Status changed from New to Resolved
Assignee set to Carlos Vicente
Resolution set to fixed
Thank you. The "upsert" trick would be great but apparently it is only possible
with PG 9.1+. I have made a slight change to not check for the error string,
but simply try an update if the insert throws an exception. It happens both in
PhysAddr.pm and Ipblock.pm.
----------------------------------------
Bug #1675: Netdot 1.0.1 with localized PostgreSQL
https://osl.uoregon.edu/redmine/issues/1675#change-2967
Author: Petr ?ech
Status: Resolved
Priority: Normal
Assignee: Carlos Vicente
Category: DeviceManagement
Target version: 1.0.1
Resolution: fixed
Hi,
I'm running PostgreSQL under German locale and so the error messages get
translated. The problem is, what in Netdot/Model/Ipblock.pm and
Netdot/Model/PhysAddr.pm is a test for "Duplicate", which breaks because the
error in German reads "FEHLER: doppelter Schl?sselwert verletzt
Unique-Constraint physaddr1".
It should be noted, that PostgreSQL must be run under English locale
(lc_messages=C in postgresql.conf) or the user has to fix these two places with
localized version of the error message.
For PostgreSQL 9.1+ there exists another solution, so called upsert:
--- PhysAddr.pm.orig 2012-07-13 20:17:17.000000000 +0200
+++ PhysAddr.pm 2012-08-14 14:12:59.780489548 +0200
@@ -178,6 +178,16 @@
foreach my $address ( keys %$macs ){
$sth->execute($address, $timestamp, $timestamp);
}
+ } elsif ( $class->config->get('DB_TYPE') eq 'Pg' ) {
+ $logger->debug(sub{ "PhysAddr::fast_update: Pg MAC Update"} );
+ my $sth = $dbh->prepare_cached("WITH upsert AS
+ (UPDATE physaddr set last_seen=? WHERE
address=? returning id)
+ INSERT INTO physaddr
(address,first_seen,last_seen,static)
+ SELECT ?, ?, ?, False WHERE
NOT EXISTS (SELECT 1 FROM upsert);");
+
+ foreach my $address ( keys %$macs ){
+ $sth->execute($timestamp, $address, $address, $timestamp,
$timestamp);
+ }
}else{
--
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
------------------------------
_______________________________________________
Netdot-devel mailing list
[email protected]
https://osl.uoregon.edu/mailman/listinfo/netdot-devel
End of Netdot-devel Digest, Vol 66, Issue 21
********************************************
