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

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

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/