CLI Scripts unusable

[Alice]

Well... let's try this:
root@stats3:/home/cacti/cli# php add_graphs.php --list-input-fields --graph-template-id=47

The scripts gets to this query:
SELECT DISTINCT
data_input_fields.data_name AS `name`,
data_input_fields.name AS `description`,
data_input_data.value AS `default`,
data_template_data.data_template_id,
data_input_fields.id AS `data_input_field_id`
FROM data_input_data
INNER JOIN (((data_template_rrd
INNER JOIN (graph_templates
INNER JOIN graph_templates_item
ON graph_templates.id = graph_templates_item.graph_template_id)
ON data_template_rrd.id = graph_templates_item.task_item_id)
INNER JOIN data_template_data
ON data_template_rrd.data_template_id=data_template_data.data_template_id)
INNER JOIN data_input_fields
ON data_template_data.data_input_id=data_input_fields.data_input_id)
ON (data_input_data.data_template_data_id = data_template_data.id)
AND (data_input_data.data_input_field_id = data_input_fields.id)
WHERE (((graph_templates.id)=47)
AND ((data_input_data.t_value)='on')
AND ((data_input_fields.input_output)='in'))

which brings down MySQL.

mysql> select count(*) from data_input_fields;
+----------+
| count(*) |
+----------+
| 95 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from data_template_data;
+----------+
| count(*) |
+----------+
| 4773 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from graph_templates;
+----------+
| count(*) |
+----------+
| 47 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from data_template_rrd;
+----------+
| count(*) |
+----------+
| 9720 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from graph_templates_item;
+----------+
| count(*) |
+----------+
| 40078 |
+----------+
1 row in set (0.00 sec)

MySQL it's on a sepparate server, with a P4 3.0 HT / 1GB RAM, with a CPU usage average of 9%

[TheWitness]

What version? It runs just fine on my system. You might also want to remove the brackets around the where clause below:

Code: Select all

WHERE (((graph_templates.id)=47) 
AND ((data_input_data.t_value)='on') 
AND ((data_input_fields.input_output)='in')) 

You might also want to repair the tables in question. Wierd.

TheWitness

[Alice]

Solved.
ALTER TABLE `data_input_data` ADD INDEX ( `t_value` )

[TheWitness]

Ahh, oh shit, you have a big installation...

TheWitness

[TheWitness]

Can you open a bug report so that we don't loose that tidbit?

TheWitness

[Alice]

What version what?
Cacti is 0.8.7a
MySQL is 5.0.37
Tables are OK, checked.

[TheWitness]

Alice, submit a bug report to http://bugs.cacti.net

TheWitness

[Alice]

Done, http://bugs.cacti.net/view.php?id=1093 .I've posted the versions while you were posting.

[TheWitness]

Can you post your "/etc/my.cnf"...

TheWitness

[TheWitness]

Also,

du -k /var/lib/mysq/cacti

TheWitness

[Alice]

root@mysql:/var/lib/mysql# du -k cacti3/
23332 cacti3/

submited to mantis too.

[Tybio]

I'm seeing something similar when I try to use the script. I'll try that fix and let you know if it works for me.

[Tybio]

FYI, this fixed the add_graphs.php problem I was having with my script.

[TheWitness]

Anyone post a "/etc/my.cnf" file?

TheWitness

[Alice]

root@mysql:~# free
total used free shared buffers cached
Mem: 1033036 624384 408652 0 197008 240520
-/+ buffers/cache: 186856 846180
Swap: 1004020 0 1004020


root@mysql:~# cat /etc/my.cnf |grep -v "#"

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 32M
table_cache = 64k
sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 64M
thread_cache_size = 256
query_cache_size=64M
set-variable=key_buffer=64M
set-variable=max_connections=250
thread_concurrency = 2
net_buffer_length = 128K
max_heap_table_size = 128M
tmp_table_size = 256M
server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M

[mysqlhotcopy]
interactive-timeout