Re: [Netdisco] Upgrade to 2.042010 fails when running ~/bin/netdisco-deploy

[Oliver G. <ol...@cp...>]

Hi Ted

Many thanks. I think the upgrade is using SQL dialect not supported by
PostgreSQL 8.4.

Yes, we say in the docs that we support back to 8.4 but I admit as it’s a
pretty old version it’s not well tested. We can look into that.

There is no adverse effect as you see. The update fixes a case in the map
where some links were shown in error on some systems only.

If you cannot upgrade, then sit tight and we’ll try to make a patch to fix
in the future. I would plan for the upgrade if you can as newer pg is way
better and also in time we’ll have to drop 8.4 support due to its age.

Oliver.

On Fri, 21 Jun 2019 at 13:22, <tbu...@vi...> wrote:

> I have the same problem and it appeared first when attempting to upgrade
> to 2.04209.  System information:  pretty much stock CentOS 6 with EPEL
> repository.
> App::Netdisco   2.42.8
> SNMP::Info      3.68
> DB Schema       57
> PostgreSQL      8.4.20
> Perl            5.10.1
>
> Everything but the Neighbors map seems to work.  The Neighbors map worked
> prior to the upgrade attempt.
>
> May I take the opportunity to say thanks to you for this piece of
> software.  It is very helpful for sysadmins.
>
>
> --
> Ted Buchanan
>
>
>
>
> From:   "Oliver Gorwits" <ol...@cp...>
> To:     "Scott Harrison" <sco...@as...>
> Cc:     "net...@li..."
> <net...@li...>
> Date:   06/21/2019 04:08 AM
> Subject:        Re: [Netdisco] Upgrade to 2.042010 fails when running
> ~/bin/netdisco-deploy
>
>
>
> Hi Scott, sorry to hear this. Can you let us know what version of
> PostgreSQL you’re running?
>
> Thanks,
> Oliver.
>
> On Thu, 20 Jun 2019 at 23:54, Scott Harrison <sco...@as...>
> wrote:
> I just upgraded to 2.042010 and while the upgrade ran without errors I got
> the following error when running netdisco-deploy.
>
> Has anyone run into this and already solved it?
>
> [netdisco@netdisco01 ~]$ ~/bin/netdisco-deploy
> This is the Netdisco II deployment script.
>
> Before we continue, the following prerequisites must be in place:
> * Database added to PostgreSQL for Netdisco
> * User added to PostgreSQL with rights to the Netdisco Database
> * "~/environments/deployment.yml" file configured with Database
> dsn/user/pass
> * A full backup of any existing Netdisco database data
> * Internet access (for OUIs and MIBs)
>
> You will be asked to confirm all changes to your system.
>
> So, is all of the above in place? [y/N]: y
>
> Would you like to deploy the database schema? [y/N]: y
> DB schema update complete.
> DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st
> execute failed: ERROR:  syntax error at or near "ORDER"
> LINE 5:              array_agg(dp.port ORDER BY dp.port) AS left_por...
>                                        ^ [for Statement "INSERT INTO
> statistics ( day, device_count, device_ip_count, device_link_count,
> device_port_count, device_port_up_count, ip_active_count, ip_table_count,
> netdisco_ver, node_active_count, node_table_count, perl_ver, pg_ver,
> schema_ver, snmpinfo_ver) VALUES ( ?, (SELECT COUNT( * ) FROM device me),
> (SELECT COUNT( * ) FROM device_ip me), (SELECT COALESCE( SUM( aggports ),
> 0 ) FROM (  WITH BothWays AS
>     ( SELECT dp.ip AS left_ip,
>              ld.dns AS left_dns,
>              ld.name AS left_name,
>              array_agg(dp.port ORDER BY dp.port) AS left_port,
>              array_agg(dp.name ORDER BY dp.name) AS left_descr,
>
>              count(dpp.*) AS aggports,
>              sum(COALESCE(dpp.raw_speed, 0)) AS aggspeed,
>
>              di.ip AS right_ip,
>              rd.dns AS right_dns,
>              rd.name AS right_name,
>              array_agg(dp.remote_port ORDER BY dp.remote_port) AS
> right_port,
>              array_agg(dp2.name ORDER BY dp2.name) AS right_descr
>
>      FROM device_port dp
>
>      LEFT OUTER JOIN device_port_properties dpp ON (
>         (dp.ip = dpp.ip) AND (dp.port = dpp.port)
>         AND (dp.type IS NULL
>              OR dp.type !~*
> '^(53|ieee8023adLag|propVirtual|l2vlan|l3ipvlan|135|136|137)$')
>         AND (dp.is_master = 'false'
>              OR dp.slave_of IS NOT NULL) )
>
>      INNER JOIN device ld ON dp.ip = ld.ip
>      INNER JOIN device_ip di ON dp.remote_ip = di.alias
>      INNER JOIN device rd ON di.ip = rd.ip
>
>      LEFT OUTER JOIN device_port dp2 ON (di.ip = dp2.ip
>                                          AND ((dp.remote_port = dp2.port)
>                                               OR (dp.remote_port =
> dp2.name)
>                                               OR (dp.remote_port =
> dp2.descr)))
>
>      WHERE dp.remote_port IS NOT NULL
>        AND dp.port !~* 'vlan'
>        AND (dp.descr IS NULL OR dp.descr !~* 'vlan')
>
>      GROUP BY left_ip,
>               left_dns,
>               left_name,
>               right_ip,
>               right_dns,
>               right_name )
>
>   SELECT *
>   FROM BothWays b
>   WHERE NOT EXISTS
>       ( SELECT *
>        FROM BothWays b2
>        WHERE b2.right_ip = b.left_ip
>          AND b2.right_port = b.left_port
>          AND b2.left_ip < b.left_ip )
>   ORDER BY aggspeed DESC, 1, 2
> ) me), (SELECT COUNT( * ) FROM device_port me), (SELECT COUNT( * ) FROM
> device_port me WHERE ( up = ? )), (SELECT COUNT( * ) FROM (SELECT me.ip
> FROM node_ip me WHERE ( active ) GROUP BY me.ip) me), (SELECT COUNT( * )
> FROM node_ip me), ?, (SELECT COUNT( * ) FROM (SELECT me.mac FROM node me
> WHERE ( active ) GROUP BY me.mac) me), (SELECT COUNT( * ) FROM node me),
> ?, ?, ?, ? )" with ParamValues: 1='2019-06-20', 2='up', 3='2.42.10',
> 4='5.10.1', 5='8.4.20', 6='58', 7='3.68'] at
> /home/netdisco/perl5/lib/perl5/App/Netdisco/Util/Statistics.pm line 42
> Updating statistics... [netdisco@netdisco01 ~]$
> _______________________________________________
> Netdisco mailing list
> net...@li...
> https://sourceforge.net/p/netdisco/mailman/netdisco-users/
> _______________________________________________
> Netdisco mailing list
> net...@li...
> https://sourceforge.net/p/netdisco/mailman/netdisco-users/
>
>
>