NetXMS Support Forum

Hello everybody,
Today I checked the mysql folder in "netxms". I found more than 20000 idata tables. Most of tables are empty.
Can I drop the unused tables?
Someone has any idea?

Thank you for any help!
Regards,
Mortymer

Hello.

Number of idata tables should be roughly the same as number of nodes in the system.

This query should return list of tables without corresponding record in nodes or clusters:
SELECT s.id,s.name FROM (SELECT substr(t.table_name,7) AS id, t.table_name AS name FROM information_schema.tables t WHERE t.table_schema='mtest' AND t.table_name LIKE 'idata_%') AS s WHERE s.id NOT IN (SELECT id FROM nodes UNION SELECT id FROM clusters);

Thank you Alex,

With this script I'm gonna do the cleaning.

Regards,
Mortymer

Hello Alex,

Can I use this script to listing the unused "tdata_" tables?

Regards,
Mortymer

There was a mistake in the script - there are few more object types which can have idata_* tables.
Please use this script instead:

SELECT s.name FROM (
    SELECT
        substr(t.table_name,7) AS id,
        t.table_name AS name
    FROM information_schema.tables t
    WHERE
        t.table_schema='mtest'
        AND t.table_name LIKE 'idata_%') AS s
WHERE s.id NOT IN (
        SELECT id FROM nodes
        UNION SELECT id FROM clusters
        UNION SELECT id FROM access_points
        UNION SELECT id FROM sensors
        UNION SELECT id FROM mobile_devices
    );

Thank you Alex,

And can I use this script at tdata_ tables?

Regards,
Mortymer

Yes

Hello,

When I used the last script I had no result.
I checked the access_points table and I found many same rows.
Screenshot attached.
What do you think about this.
The Node 842 is a Mikrotik router.

I used this script to check how many duplicated records I have.

select count(*), ap.node_id, ap.mac_address, ap.vendor, ap.model, ap.serial_number, ap.ap_state, ap.ap_index 
  from access_points ap
group by ap.node_id, ap.mac_address, ap.vendor, ap.model, ap.serial_number, ap.ap_state, ap.ap_index 

I attached the results.

Any idea or suggestion?

Thank you for any help!

Regards,
Mortymer