More than 20000 idata_* tables

[Mortymer]

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

[alk]

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);

[Mortymer]

Thank you Alex,

With this script I'm gonna do the cleaning.

Regards,
Mortymer

[Mortymer]

Hello Alex,

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

Regards,
Mortymer

[alk]

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
    );

[Mortymer]

Thank you Alex,

And can I use this script at tdata_ tables?

Regards,
Mortymer

[alk]

Yes

[Mortymer]

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

---