SQL errors in NAV 3.4 - Nav-users

21 Jul 2008

      Hello everyone,
While testing Werner's upcoming Debian package of NAV 3.4.1, we
discovered some bugs in the SQL scripts that initialize the NAV
database, see [1] for details.
Two of the problems described in [1] will affect anyone who has
initialized a new NAV database while installing NAV 3.4.0 or 3.4.1
from scratch.  These two problems are potentially serious if you value
the correctness of your machine tracker data.  Whenever your delete an
IP Device from NAV, there are two database rules making sure that
every machine tracker entry related to this IP Device is closed.  If
you are affected, these rules will not have been defined, and all open
records related to an IP Device will stay open as the device is
deleted.
The third problem is relatively minor.  It only affects those who have
initialized the NAV database on PostgreSQL 8.3 or newer.  When a
service check is deleted, a database rule removes NAV's knowledge of
the service response time RRD file.  Other parts of NAV will
automatically remove this when it sees the file has gone 30 days
without being updated.
To summarize: If you upgraded to NAV 3.4 from an older version, and
you do not run PostgreSQL 8.3 or newer, you do not need to take any
action.
If you are affected by the above problems, you can either wait for NAV
3.4.2, which we hope to release shortly, or you can apply the
attached, short SQL script in your NAV manage database (using psql).
[1] http://sourceforge.net/support/tracker.php?aid=2023345
-- 
mvh
Morten Brekkevold
UNINETT

/*
 *
 * This SQL script is designed to upgrade your NAV database from
 * version 3.4.0 or 3.4.1 to 3.4.2.  
 *
 * This script is only necessary if either:
 *
 * 1. You installed NAV 3.4 from scratch, creating a new database.
 *
 *  or
 *
 * 2. You installed NAV on a PostgreSQL version 8.3 database.
 *
 *
 * NAV 3.4.1 and 3.4.1 were shipped with a manage.sql containing a
 * couple of errors in the order of which relations and rules were
 * created, and also a small typecasting error only present on
 * PostgreSQL 8.3 and newer (See SF#2023345 for more details).
 *
 *
 * Run the script as the nav database user like this:
 *
 *  psql -f 3.4.2.sql manage nav
 *
*/

BEGIN;

-- Redefine these two rules to make sure that they actually are
-- present.
CREATE OR REPLACE RULE netbox_close_arp AS ON DELETE TO netbox
  DO UPDATE arp SET end_time=NOW()
     WHERE netboxid=OLD.netboxid AND end_time='infinity';

CREATE OR REPLACE RULE netbox_close_cam AS ON DELETE TO netbox
  DO UPDATE cam SET end_time=NOW()
     WHERE netboxid=OLD.netboxid AND end_time='infinity';

-- Redefine this rule to make sure it is present also on a PostgreSQL
-- 8.3 database.
CREATE OR REPLACE RULE rrdfile_deleter AS
    ON DELETE TO service
    DO DELETE FROM rrd_file
        WHERE key='serviceid' AND value=old.serviceid;
        WHERE key='serviceid' AND value=old.serviceid::text;

COMMIT;