Cacti with sqlite - any interest?

[gcargile]

Woops!
Duplicate post... deleted. -G

[beagle1]

Hey Gerry

Here's your problem: you need to set $database_default to "main" in config.php, you have set it to null right now:

$database_default="";

Let me know if that works

[gcargile]

FANTASTIC!!

Thanks beagle1, it's working!!

Cheers!
-Gerry

[gcargile]

Well, almost 7 months later, and I'm back to working on this. (What can I say, I got busy with other priorities)

Adding "Graph Template Items" fails, both when importing a saved template or creating a new one from scratch.

lighttpd error.log shows this:
2014-01-07 16:54:54: (mod_fastcgi.c.2676) FastCGI-stderr: PHP Fatal error: pdo error: [19: graph_templates_item.id may not be NULL] in EXECUTE("INSERT INTO graph_templates_item (hash,graph_template_id,local_graph_id,task_item_id,color_id,alpha,graph_type_id,cdef_id,consolidation_function_id,text_format,value,hard_return,gprint_id,sequence) VALUES ("0ebfae7adc63ba1c8a313abe6de176a9","35","0","116","102","4C","7","0","3","","","","2","1")")
in /var/www/cacti-sqlite/cacti/lib/adodb/adodb-errorhandler.inc.php on line 77


Hopefully somebody can shed some light on the problem. beagle1? you still around?

[beagle1]

Just guessing here, I ran into something like this a long time ago. Check the table 'graph_templates_item', it is probably missing the primary key definition (.schema graph_templates_item). Here is how you can recreate it properly and then retry. Caution: back up your database and check each step for success, I have not tested these statements, they are there for general guidance only!

Code: Select all

create table temp1 as select * from graph_templates item;
drop table graph_templates_item;
CREATE TABLE "graph_templates_item" (
  "id" INTEGER PRIMARY KEY,
  "hash" varchar(32) NOT NULL DEFAULT '',
  "local_graph_template_item_id" int(12)  NOT NULL DEFAULT '0',
  "local_graph_id" mediumint(8)  NOT NULL DEFAULT '0',
  "graph_template_id" mediumint(8)  NOT NULL DEFAULT '0',
  "task_item_id" mediumint(8)  NOT NULL DEFAULT '0',
  "color_id" mediumint(8)  NOT NULL DEFAULT '0',
  "alpha" char(2) DEFAULT 'FF',
  "graph_type_id" tinyint(3) NOT NULL DEFAULT '0',
  "cdef_id" mediumint(8)  NOT NULL DEFAULT '0',
  "consolidation_function_id" tinyint(2) NOT NULL DEFAULT '0',
  "text_format" varchar(255) DEFAULT NULL,
  "value" varchar(255) DEFAULT NULL,
  "hard_return" char(2) DEFAULT NULL,
  "gprint_id" mediumint(8)  NOT NULL DEFAULT '0',
  "sequence" mediumint(8)  NOT NULL DEFAULT '0'
);
insert into graph_templates_item select * from temp1;
drop table temp1;

[gcargile]

Yup! That did the trick. The migration of data via the temp table didn't work, but no big deal as this is a fresh install.
I edited cacti-sqlite.sql and recreated the whole database. I can now import or create new graph templates!

Now, on to my other problem... Cacti is not creating the rrd datasources.
pi@RaspberryPi /var/www/cacti $ cd rra/
pi@RaspberryPi /var/www/cacti/rra $ ll
total 0
pi@RaspberryPi /var/www/cacti/rra $

I can't find any useful error messages about it, either. cacti.log just shows this (poller.php is being called with it's --debug flag):

01/08/2014 03:30:03 PM - POLLER: Poller[0] NOTE: Poller Int: '300', Cron Int: '300', Time Since Last: '301', Max Runtime '298', Poller Runs: '1'
01/08/2014 03:30:03 PM - POLLER: Poller[0] DEBUG: About to Spawn a Remote Process [CMD: /usr/bin/php, ARGS: -q "/var/www/cacti-sqlite/cacti/cmd.php" 0 1]
01/08/2014 03:30:06 PM - SYSTEM STATS: Time:2.9175 Method:cmd.php Processes:1 Threads:N/A Hosts:2 HostsPerProcess:2 DataSources:0 RRDsProcessed:0

[gcargile]

More template problems...

I was having trouble getting my templates to work correctly, so I wiped and reinstalled, starting again from scratch. This time however, I didn't import my templates, opting to build them up from stratch in hopes of finding the problem.

Here's what I have found:
Creating Data Templates and new Data Sources (to read an external RRD file) works fine.
Creating Graph Templates works mostly. I can create a new template, and add Graph Template Items to it without error, but the Graph Item "ignores" the Data Source selection.

It shows this:

Code: Select all

Graph Item     Data Source                Graph Item Type   CF Type   Item Color
Item # 1       (No Task): Upstairs Temp   LINE1             AVERAGE   FF7D00   

Where it should show this:

Code: Select all

Graph Item     Data Source                Graph Item Type   CF Type   Item Color
Item # 1       (Probe1_t): Upstairs Temp  LINE1             AVERAGE   FF7D00   

No web errors are thrown up to this point.


When trying to edit Graph Item: Item #1, The page [white screen]s when trying to save, throwing the error below to the Lighttpd error.log:

2014-01-10 15:08:49: (mod_fastcgi.c.2676) FastCGI-stderr: PHP Fatal error: pdo error: [0: ] in EXECUTE("select local_graph_id,task_item_id as task_item_id from graph_templates_item where graph_template_id=35 and local_graph_id>0 and !((local_graph_template_item_id IN('411'))) and (local_graph_template_item_id IN('411')) group by local_graph_id")
in /var/www/cacti-sqlite/cacti/lib/adodb/adodb-errorhandler.inc.php on line 77


Let me know if there is a fix.

Oh, and this new install is not creating the default rrd files ether.

Cheers!
-Gerry

[beagle1]

Can you run that query manually against your sqlite database and see what error it gives you? That might give us more information on the issue..

Code: Select all

select local_graph_id,task_item_id as task_item_id from graph_templates_item where graph_template_id=35 and local_graph_id>0 and !((local_graph_template_item_id IN('411'))) and (local_graph_template_item_id IN('411')) group by local_graph_id

[gcargile]

Assuming I'm doing this right.... Here's the output:

Code: Select all

$ sudo sqlite3 /var/www/cacti/cacti.db

SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select local_graph_id,task_item_id as task_item_id
   ...>    from graph_templates_item
   ...>     where graph_template_id=35
   ...>      and local_graph_id>0
   ...>      and !((local_graph_template_item_id IN('411')))
   ...>      and (local_graph_template_item_id IN('411'))
   ...> group by local_graph_id;
Error: unrecognized token: "!("
sqlite>

Changing "and !(" to "and NOT (" runs without error, but returns nothing.

[beagle1]

cool! that should be "not (" instead of "!(" for sqlite compatibility

The offending statement is in lib/template.php on line 425 - look for "!(" and replace with "not (". The new line reads:

Code: Select all

$values_to_apply = db_fetch_assoc("select local_graph_id," . $graph_input["column_name"] . " as " . $graph_input["column_name"] . " from graph_templates_item where graph_template_id=" . $graph_input["graph_template_id"] . " and local_graph_id>0 and not (" . array_to_sql_or($new_session_members, "local_graph_template_item_id") . ") $sql_include_items group by local_graph_id");

let me know if that works

[gcargile]

Haven't tested it yet, but in the process of making the change, I've found several other instances of "!(" that look like they should be "not (":

Code: Select all

/var/www/cacti/lib $ grep -n '!(' *.php
api_data_source.php:134:                        if (!($i % 1000)) {
data_query.php:689:                     if (!((sizeof($aggregate_field_values) < sizeof($field_values)) || (in_array("", $aggregate_field_values) == true) || (sizeof($aggregate_field_values) == 0)) || ($nonunique)) {
functions.php:1635:             if (stristr($_SERVER["HTTP_USER_AGENT"], "Mozilla") && (!(stristr($_SERVER["HTTP_USER_AGENT"], "compatible")))) {
graph_export.php:747:                   $sql_where = "AND !(user_auth_perms.user_id IS NOT NULL AND graph_tree_items.host_id>0)";
graph_export.php:749:                   $sql_where = "AND !(user_auth_perms.user_id IS NULL AND graph_tree_items.host_id>0)";
graph_export.php:1492:          $sql_where = "AND !(user_auth_perms.user_id IS NOT NULL AND graph_tree_items.host_id>0)";
graph_export.php:1494:          $sql_where = "AND !(user_auth_perms.user_id IS NULL AND graph_tree_items.host_id>0)";
html_tree.php:1273:     }elseif (!($use_expand_contract) && (!empty($current_title))) {
rrd.php:576:            $access_denied = !(is_graph_allowed($local_graph_id));
rrd.php:1469:           $access_denied = !(is_graph_allowed($local_graph_id));
template.php:431:               if (!(($graph_input["column_name"] == "task_item_id") && (empty($value{$graph_input["column_name"]})))) {
pi@SensorPi-sqlite /var/www/cacti/lib $

Should I blindly go ahead and make the changes (but making sure they are in SQL statements first)?

[beagle1]

It is quite likely I haven't used the functions that have those statements and so haven't run into issues. Can you test them to see if they do blow up? Also, be careful, some of the statements you posted are "if" statements, not SQL, which are fine with the syntax. Let me know if you change anything of course so I can update my copy as well

[pd1acf]

Hi All,

Just started to look into this.
I started a fresh clone of Cacti, and did not yet import old mysql data. I have only 2 hosts on the Raspberry to monitor
I have Nginx running.

Things seem reasonably correct, however when adding graphs to a host, i get an error page, and the following is thrown in the log :



2016/01/18 23:26:53 [error] 14167#0: *25418 FastCGI sent in stderr: "PHP message: PHP Fatal error: pdo error: [19: graph_templates_item.id may not be NULL] in EXECUTE("INSERT INTO graph_templates_item (local_graph_template_item_id,local_graph_id,graph_template_id,task_item_id,color_id,alpha,graph_type_id,consolidation_function_id,cdef_id,value,gprint_id,text_format,hard_return,sequence) VALUES ("167","6","21","56","48","FF","7","1","14","","2","Used","","1")")
in /usr/share/nginx/www/cacti/lib/adodb/adodb-errorhandler.inc.php on line 77" while reading response header from upstream, client: 192.168.1.205, server: localhost, request: "POST /cacti/graphs_new.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "192.168.1.8", referrer: "http://192.168.1.8/cacti/graphs_new.php?host_id=1"


Any ideas would be great !

[pd1acf]

Hi All,

After digging around, i got rid of most errors.
However at this momen still stuck with this error :

2016/01/20 23:30:01 [error] 14168#0: *25795 FastCGI sent in stderr: "PHP message: PHP Notice: Undefined index: local_data_id in /usr/share/nginx/www/cacti/graphs_new.php on line 206
PHP message: PHP Notice: Undefined index: local_data_id in /usr/share/nginx/www/cacti/graphs_new.php on line 206
PHP message: PHP Fatal error: pdo error: [0: ] in EXECUTE("update graph_templates_item set color_id='0' where id=")
in /usr/share/nginx/www/cacti/lib/adodb/adodb-errorhandler.inc.php on line 77" while reading response header from upstream, client: 192.168.1.205, server: localhost, request: "POST /cacti/graphs_new.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "192.168.1.8", referrer: "http://192.168.1.8/cacti/graphs_new.php"




The single quote from the query itseld seems not correct, but i cannot find it in the code...

Any help ?

Thanks !