NetXMS 5.2 patch release 5

Filipp Sudanov

Hi,

The problem is that "apt upgrade" automatically started nxdbmgr background-upgrade.
But there's currently no lock mechanism, so starting it manually ended up in it running two parallel processes of it.
Lock will be added in a future version. Currently just don't run nxdbmgr background-upgrade after the upgrade (you can check with ps command that it actually running).

Spheron

Hi Filipp,

thanks for then explanation. So, i give the update a second try...

Greetings
Marco

Alex Kirhenshtein

@spheron said in NetXMS 5.2 patch release 5:

If i run then nxdbmgr background-upgrade i get the following errors:

DROP INDEX idx_tdata_7035
SQL query failed (42P16 FEHLER:  mehrere Primärschlüssel für Tabelle »idata_7046« nicht erlaubt):
ALTER TABLE idata_7046 ADD PRIMARY KEY (item_id,idata_timestamp)
SQL query failed (42P16 FEHLER:  mehrere Primärschlüssel für Tabelle »tdata_7046« nicht erlaubt):
ALTER TABLE tdata_7046 ADD PRIMARY KEY (item_id,tdata_timestamp)
SQL query failed (42704 FEHLER:  Index »idx_idata_7052_id_timestamp« existiert nicht):
DROP INDEX idx_idata_7052_id_timestamp
SQL query failed (42P16 FEHLER:  mehrere Primärschlüssel für Tabelle »tdata_7052« nicht erlaubt):
ALTER TABLE tdata_7052 ADD PRIMARY KEY (item_id,tdata_timestamp)
SQL query failed (42P16 FEHLER:  mehrere Primärschlüssel für Tabelle »idata_7089« nicht erlaubt):
ALTER TABLE idata_7089 ADD PRIMARY KEY (item_id,idata_timestamp)
SQL query failed (42704 FEHLER:  Index »idx_tdata_7089« existiert nicht):
DROP INDEX idx_tdata_7089
SQL query failed (42P16 FEHLER:  mehrere Primärschlüssel für Tabelle »idata_7096« nicht erlaubt):
ALTER TABLE idata_7096 ADD PRIMARY KEY (item_id,idata_timestamp)
SQL query failed (42P16 FEHLER:  mehrere Primärschlüssel für Tabelle »tdata_7096« nicht erlaubt):
ALTER TABLE tdata_7096 ADD PRIMARY KEY (item_id,tdata_timestamp)
SQL query failed (42704 FEHLER:  Index »idx_idata_7127_id_timestamp« existiert nicht):

There is one more problem - you already have duplicates in the idata_* tables.

Try this query to find them:

DO $$
DECLARE
    node_record RECORD;
    tbl_name TEXT;
    dup_count INTEGER;
    total_duplicates INTEGER := 0;
BEGIN
    FOR node_record IN SELECT id FROM nodes
    LOOP
        tbl_name := 'idata_' || node_record.id;

        IF EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'public'
            AND table_name = tbl_name
        ) THEN
            EXECUTE format('
                SELECT COUNT(*) FROM public.%I
                WHERE ctid NOT IN (
                    SELECT MIN(ctid)
                    FROM public.%I
                    GROUP BY item_id, idata_timestamp
                )', tbl_name, tbl_name) INTO dup_count;

            IF dup_count > 0 THEN
                RAISE NOTICE 'Table % has % duplicate rows', tbl_name, dup_count;
                total_duplicates := total_duplicates + dup_count;
            END IF;
        END IF;
    END LOOP;

    RAISE NOTICE 'Total duplicate rows found: %', total_duplicates;
END $$;

Spheron

Hi Alex,

thanks for the hint. I try the script via psql with (showduplicate.sql is your script):

 psql -h localhost -U netxms -p 5432 -a -f showduplicate.sql

But the only output is the script himself, there are noch other outputs:

xxx@xxx:~$ psql -h localhost -U netxms -p 5432 -a -f showduplicate.sql
Passwort für Benutzer netxms:
DO $$
DECLARE
    node_record RECORD;
    tbl_name TEXT;
    dup_count INTEGER;
    total_duplicates INTEGER := 0;
BEGIN
    FOR node_record IN SELECT id FROM nodes
    LOOP
        tbl_name := 'idata_' || node_record.id;
        IF EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'public'
            AND table_name = tbl_name
        ) THEN
            EXECUTE format('
                SELECT COUNT(*) FROM public.%I
                WHERE ctid NOT IN (
                    SELECT MIN(ctid)
                    FROM public.%I
                    GROUP BY item_id, idata_timestamp
                )', tbl_name, tbl_name) INTO dup_count;
            IF dup_count > 0 THEN
                RAISE NOTICE 'Table % has % duplicate rows', tbl_name, dup_count;
                total_duplicates := total_duplicates + dup_count;
            END IF;
        END IF;
    END LOOP;
    RAISE NOTICE 'Total duplicate rows found: %', total_duplicates;
END $$;

The script is running about 1h bevor i canceld it.

Am I on the right way to run the script?

Greetings
Marco

Alex Kirhenshtein

@spheron said in NetXMS 5.2 patch release 5:

The script is running about 1h bevor i canceld it.

It was rather inefficient.

Try this one instead:

DO $$
DECLARE
    node_record RECORD;
    tbl_name TEXT;
    dup_count INTEGER;
    total_duplicates INTEGER := 0;
BEGIN
    FOR node_record IN SELECT id FROM nodes
    LOOP
        tbl_name := 'idata_' || node_record.id;

        IF EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'public'
            AND table_name = tbl_name
        ) THEN
            RAISE NOTICE 'Processing table %', tbl_name;

            EXECUTE format('
                WITH ranked AS (
                    SELECT ctid,
                          ROW_NUMBER() OVER (PARTITION BY item_id, idata_timestamp ORDER BY ctid) as rn
                    FROM public.%I
                )
                SELECT COUNT(*)
                FROM ranked
                WHERE rn > 1
            ', tbl_name) INTO dup_count;

            IF dup_count > 0 THEN
                RAISE NOTICE 'Table % has % duplicate rows', tbl_name, dup_count;
                total_duplicates := total_duplicates + dup_count;
            END IF;
        END IF;
    END LOOP;

    RAISE NOTICE 'Total duplicate rows found: %', total_duplicates;
END $$;

And then to delete duplicates:

DO $$
DECLARE
    node_record RECORD;
    tbl_name TEXT;
    deleted_count INTEGER;
    total_deleted INTEGER := 0;
BEGIN
    FOR node_record IN SELECT id FROM nodes
    LOOP
        tbl_name := 'idata_' || node_record.id;

        IF EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'public'
            AND table_name = tbl_name
        ) THEN
            RAISE NOTICE 'Processing table %', tbl_name;

            EXECUTE format('
                WITH duplicates AS (
                    SELECT ctid,
                          ROW_NUMBER() OVER (
                              PARTITION BY item_id, idata_timestamp
                              ORDER BY ctid
                          ) as rn
                    FROM public.%I
                )
                DELETE FROM public.%I
                WHERE ctid IN (
                    SELECT ctid
                    FROM duplicates
                    WHERE rn > 1
                )', tbl_name, tbl_name);

            GET DIAGNOSTICS deleted_count = ROW_COUNT;

            IF deleted_count > 0 THEN
                RAISE NOTICE 'Deleted % duplicate rows from table %', deleted_count, tbl_name;
                total_deleted := total_deleted + deleted_count;
            ELSE
                RAISE NOTICE 'Table % has no duplicates', tbl_name;
            END IF;
        END IF;
    END LOOP;

    RAISE NOTICE 'Total duplicate rows deleted: %', total_deleted;
END $$;

Spheron

Hi Alex,

thanks for the new script... Started it an the result is:

psql:showduplicate1.sql:39: HINWEIS: Total duplicate rows found: 0

So, at earlier post you said i had duplicates, but the script says there are none... Whats the truth?

Greetings
Marco

Filipp Sudanov

Could be that during the execution of the first script it deleted all the duplicates.

Spheron

Hi Filipp,

@filipp-sudanov said in NetXMS 5.2 patch release 5:

Could be that during the execution of the first script it deleted all the duplicates.

I think, the duplicates was created while the update of NetXMS to V5.2.5. But, in cause of my revert to a VM snapshot with NetXMS V5.2.4, there are no duplicates in tables. I run the scripts above against the V5.2.4 Version of the tables (after i restored the snapshot of the VM)...

Greetings
Marco

johnnyva

Hey Alex, I'd imagine we should also be checking for dupes on the tdata tables too right?

@alk said in NetXMS 5.2 patch release 5:

@spheron said in NetXMS 5.2 patch release 5:

The script is running about 1h bevor i canceld it.

Alex Kirhenshtein

@johnnyva said in NetXMS 5.2 patch release 5:

Hey Alex, I'd imagine we should also be checking for dupes on the tdata tables too right?

Yes, if you have table DCIs configured.

But this issue is already sorted in 5.2.6

NetXMS Forum

NetXMS 5.2 patch release 5