My problem is that i have more than 2 000 000 records in data_input_data table. And i want to delete waste data. But i need confirmation is my solution correct.
How to reproduce PROBLEM
1. Delete all graphs and data sources form cacti
2. check if no data
Code: Select all
SELECT COUNT(id) FROM cacti.data_template_data where local_data_id>0;
+-----------+
| COUNT(id) |
+-----------+
| 0 |
+-----------+
3. truncate data_input_data table
4. add 1 graph (for example ucd/net - Load Average)
5. check data_template_data table
Code: Select all
SELECT id,local_data_id,data_input_id FROM cacti.data_template_data where local_data_id>0;
+------+---------------+---------------+
| id | local_data_id | data_input_id |
+------+---------------+---------------+
| 1430 | 1346 | 1 |
| 1431 | 1347 | 1 |
| 1432 | 1348 | 1 |
+------+---------------+---------------+
6. check data_input_data table
Code: Select all
SELECT * from data_input_data;
+---------------------+-----------------------+---------+-----------+
| data_input_field_id | data_template_data_id | t_value | value |
+---------------------+-----------------------+---------+-----------+
| 1 | 1430 | NULL | 127.0.0.1 |
| 2 | 1430 | NULL | XXXXX |
| 3 | 1430 | NULL | |
| 4 | 1430 | NULL | |
| 5 | 1430 | NULL | 2 |
| 40 | 1430 | NULL | 161 |
| 41 | 1430 | NULL | |
| 42 | 1430 | NULL | |
| 43 | 1430 | NULL | |
| 1 | 1431 | NULL | 127.0.0.1 |
| 2 | 1431 | NULL | XXXXX |
| 3 | 1431 | NULL | |
| 4 | 1431 | NULL | |
| 5 | 1431 | NULL | 2 |
| 40 | 1431 | NULL | 161 |
| 41 | 1431 | NULL | |
| 42 | 1431 | NULL | |
| 43 | 1431 | NULL | |
| 1 | 1432 | NULL | 127.0.0.1 |
| 2 | 1432 | NULL | XXXXX |
| 3 | 1432 | NULL | |
| 4 | 1432 | NULL | |
| 5 | 1432 | NULL | 2 |
| 40 | 1432 | NULL | 161 |
| 41 | 1432 | NULL | |
| 42 | 1432 | NULL | |
| 43 | 1432 | NULL | |
+---------------------+-----------------------+---------+-----------+
7. delete graphs from cacti but check "Leave the Data Source(s) untouched."
8. delete 2 of 3 datasources
9. check data_template_data table
Code: Select all
SELECT id,local_data_id,data_input_id FROM cacti.data_template_data where local_data_id>0;
+------+---------------+---------------+
| id | local_data_id | data_input_id |
+------+---------------+---------------+
| 1430 | 1346 | 1 |
+------+---------------+---------------+
10. check data_input_data table
Code: Select all
SELECT * from data_input_data;
+---------------------+-----------------------+---------+-----------+
| data_input_field_id | data_template_data_id | t_value | value |
+---------------------+-----------------------+---------+-----------+
| 1 | 1424 | NULL | 127.0.0.1 |
| 2 | 1424 | NULL | onero |
| 3 | 1424 | NULL | |
| 4 | 1424 | NULL | |
| 5 | 1424 | NULL | 2 |
| 40 | 1424 | NULL | 161 |
| 41 | 1424 | NULL | |
| 42 | 1424 | NULL | |
| 43 | 1424 | NULL | |
| 1 | 1425 | NULL | 127.0.0.1 |
| 2 | 1425 | NULL | onero |
| 3 | 1425 | NULL | |
| 4 | 1425 | NULL | |
| 5 | 1425 | NULL | 2 |
| 40 | 1425 | NULL | 161 |
| 41 | 1425 | NULL | |
| 42 | 1425 | NULL | |
| 43 | 1425 | NULL | |
| 1 | 1426 | NULL | 127.0.0.1 |
| 2 | 1426 | NULL | onero |
| 3 | 1426 | NULL | |
| 4 | 1426 | NULL | |
| 5 | 1426 | NULL | 2 |
| 40 | 1426 | NULL | 161 |
| 41 | 1426 | NULL | |
| 42 | 1426 | NULL | |
| 43 | 1426 | NULL | |
+---------------------+-----------------------+---------+-----------+
all data is still in place
NOW i want to delete waste data and i use the code from script
Code: Select all
SELECT count(*) FROM data_input_data LEFT JOIN data_template_data ON data_template_data.data_input_id=data_input_data.data_template_data_id WHERE data_template_data.data_input_id IS NULL AND data_template_data.data_input_id>0"
+----------+
| count(*) |
+----------+
| 0 |
+----------+
of course. we can't join like that
data_input_data LEFT JOIN data_template_data ON data_template_data.data_input_id=data_input_data.data_template_data_id
and use caluse
WHERE data_template_data.data_input_id IS NULL AND data_template_data.data_input_id>0 (because 2 caluses the same column)
The solution is to modify SQL request
Code: Select all
SELECT count(*) FROM data_input_data LEFT JOIN data_template_data ON data_input_data.data_template_data_id=data_template_data.id WHERE data_template_data.id IS NULL;
+----------+
| count(*) |
+----------+
| 27 |
+----------+
now it's OK
an we can delete data
Code: Select all
delete from data_input_data WHERE data_input_data.data_template_data_id NOT IN (SELECT id FROM data_template_data);
Query OK, 18 rows affected (0.00 sec)
SELECT * from data_input_data;
+---------------------+-----------------------+---------+-----------+
| data_input_field_id | data_template_data_id | t_value | value |
+---------------------+-----------------------+---------+-----------+
| 1 | 1430 | NULL | 127.0.0.1 |
| 2 | 1430 | NULL | onero |
| 3 | 1430 | NULL | |
| 4 | 1430 | NULL | |
| 5 | 1430 | NULL | 2 |
| 40 | 1430 | NULL | 161 |
| 41 | 1430 | NULL | |
| 42 | 1430 | NULL | |
| 43 | 1430 | NULL | |
+---------------------+-----------------------+---------+-----------+
finita la comedia
patch
Code: Select all
--- a/cacti/branches/0.8.8/cli/repair_database.php
+++ b/cacti/branches/0.8.8/cli/repair_database.php
@@ -137,10 +137,10 @@ if ($rows > 0) {
}
/* remove invalid Data Input Data Rows from the Database in two passes */
-$rows = db_fetch_cell("SELECT count(*) FROM data_input_data LEFT JOIN data_template_data ON data_template_data.data_input_id=data_input_data.data_template_data_id WHERE data_template_data.data_input_id IS NULL AND data_template_data.data_input_id>0");
+$rows = db_fetch_cell("SELECT count(*) FROM data_input_data LEFT JOIN data_template_data ON data_input_data.data_template_data_id=data_template_data.id WHERE data_template_data.id IS NULL");
$total_rows += $rows;
if ($rows > 0) {
- if ($force) db_execute("DELETE FROM data_input_data WHERE data_input_data.data_template_data_id NOT IN (SELECT data_input_id FROM data_template_data)");
+ if ($force) db_execute("DELETE FROM data_input_data WHERE data_input_data.data_template_data_id NOT IN (SELECT id FROM data_template_data)");
echo "NOTE: $rows Invalid Data Input Data Rows " . ($force ? "Removed from":"Found in") . " Data Templates\n";
}
$rows = db_fetch_cell("SELECT count(*) FROM data_input_data LEFT JOIN data_input_fields ON data_input_fields.id=data_input_data.data_input_field_id WHERE data_input_fields.id IS NULL");
