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