Installation of Cacti 0.8.6h

[zrichardson]

Okay, so I had this exact same problem installing 0.8.6g yesterday only to discover that it was not compatible with MySQL 5.x. So I rolled back to MySQL 4.1 and all my problems were solved. I then come into work this morning and discover this new version of Cacti was released. So, since I haven't collected hardly any data yet, I decide to wipe out my previous installations and start from scratch with the new version. I installed MySQL 5.0.18, and copied the latest Cacti installation (0.8.6h) to the proper location on my webserver. At this point, I should mention that through reading several posts yesterday, I determined that I should follow the install instructions located here: http://bsod2600.home.comcast.net/install_windows.html
I've re-verified all of my permission levels and am at a complete loss as to why this keeps happening. Doesn't this new version of Cacti support MySQL 5.x? Anyhow, at this point I can log into Cacti, add devices, and navigate the site just fine. However, when I try to create graphs for my devices it fails to create them, but SNMP is working okay.

Here is some output from my log file with DEBUG turned on:

01/04/2006 12:24:00 PM - CMDPHP: Poller[0] DEBUG: SQL Cell: "select count(*) from poller_item"
01/04/2006 12:24:00 PM - CMDPHP: Poller[0] DEBUG: SQL Assoc: "select id from host where disabled = '' order by id"
01/04/2006 12:24:00 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "replace into settings (name,value) values ('path_webroot','c:/Inetpub/wwwroot/cacti')"
01/04/2006 12:24:00 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "truncate table poller_time"
01/04/2006 12:24:00 PM - POLLER: Poller[0] DEBUG: About to Spawn a Remote Process [CMD: c:/php/php-win.exe, ARGS: -q c:/inetpub/wwwroot/cacti/cmd.php 0 2]
01/04/2006 12:24:00 PM - CMDPHP: Poller[0] DEBUG: SQL Assoc: "select * from host where (disabled = '' and id >= 0 and id <= 2) ORDER by id"
01/04/2006 12:24:00 PM - CMDPHP: Poller[0] DEBUG: SQL Assoc: "SELECT * from poller_item WHERE (host_id >= 0 and host_id <= 2) ORDER by host_id"
01/04/2006 12:24:00 PM - CMDPHP: Poller[0] DEBUG: SQL Cell: "SELECT count(*) from poller_item WHERE (action=2 AND (host_id >= 0 and host_id <= 2))"
01/04/2006 12:24:00 PM - CMDPHP: Poller[0] ERROR: Either there are no items in the cache or polling is disabled
01/04/2006 12:24:00 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "insert into poller_time (poller_id, start_time, end_time) values (0, NOW(), NOW())"
01/04/2006 12:24:01 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "replace into settings (name,value) values ('date',NOW())"
01/04/2006 12:24:01 PM - CMDPHP: Poller[0] DEBUG: SQL Assoc: "select poller_id,end_time from poller_time where poller_id = 0"
01/04/2006 12:24:01 PM - CMDPHP: Poller[0] DEBUG: SQL Assoc: "select poller_output.output, poller_output.time, poller_output.local_data_id, poller_item.rrd_path, poller_item.rrd_name, poller_item.rrd_num from (poller_output,poller_item) where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name)"
01/04/2006 12:24:01 PM - SYSTEM STATS: Time:1.0216 Method:cmd.php Processes:1 Threads:N/A Hosts:2 HostsPerProcess:2 DataSources:0 RRDsProcessed:0
01/04/2006 12:24:01 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "replace into settings (name,value) values ('stats_poller','Time:1.0216 Method:cmd.php Processes:1 Threads:N/A Hosts:2 HostsPerProcess:2 DataSources:0 RRDsProcessed:0')"
01/04/2006 12:24:01 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "truncate table poller_output"
01/04/2006 12:24:01 PM - CMDPHP: Poller[0] DEBUG: SQL Cell: "select count(*) from poller_command"

Below is a list of the versions that I'm using:
MySQL 5.0.18
PHP 5.1.1
RRDTool 1.2.11 (cygwin version from www.cacti.net)
Cacti 0.8.6h
Windows 2003
IIS 6.0

Much thanks in advance for any help you can offer!

[BSOD2600]

Do you have any devices with items that should be polled?
Is there anything in your poller cache?

[zrichardson]

I followed your instructions on deleting the default device that is created as I am trying to monitor a Windows 2003 box. So after I deleted it, I then created a new device with the following:

• Description: ComputerName
  Hostname: FullyQualifiedMachineName
  Host Template: Windows 2000/XP Host

The SNMP settins are defaulted to port 161 and version 1, which are correct (as well as the proper community string).

My Steps
1. Click on "Devices" link in Cacti console
2. Click "Create Graphs for this Host" link in top-right corner
3. Select checkboxes for "C:" and one of my NIC's
4. Click the "create" button
5. Cacti responds back with "+ Created graph:" (twice at top of screen)
6. I click on "Graph Management" link in Cacti console
7. No graphs exist

All I have to go on are the log files which don't seem to be making all that much sense. Like I said before, this is the same problem I had with the previous version of Cacti (0.8.6g) yesterday when using MySQL 5.x.

Can I supply you with anymore information?

[BSOD2600]

Well those are the proper steps... guess I'll test mysql 5 on my vmware box some time today and see if I have the same experience as you.

[zrichardson]

That'd be great to get some more result sets.
Also, in regards to your previous question (which I just noticed that I didn't answer), there are no items in my poller cache.

[zrichardson]

I just tried an install on a fresh Windows 2003 box and got the same results.

[BSOD2600]

When you ran the command: mysql --user=root --password cacti < c:\inetpub\wwwroot\cacti\cacti.sql

did you get any errors? I'm getting: ERROR 1264 (22003) at line 1857: Out of range value adjusted for column 'availability' at row 1.

[zrichardson]

Yes I did. I got this in Cacti 0.8.6g as well and I had to adjust the properties for the "availability" column in the "host" table to be "decimal(8,5)" instead of "decimal(7,5)" as the default value didn't even fit. (This is line 1,849 within the cacti.sql file by the way.) I meant to post this under the bugs section yesterday, but forgot.

[BSOD2600]

Well I already posted it as a bug now. One of the devs will hopefully look at the problem today and get it resolved soon. So it seems MySQL 5 support is not completely here yet...

[zrichardson]

I added a note to your bug post as it also affects MySQL 4.1 and Cacti 0.8.6g.

This having been said, it's not a problem with MySQL 5.x (just a typo in the cacti.sql file), so how do we go about testing a solution for my original problem in this post?
Or maybe a developer could verify our findings?

[BSOD2600]

Try commenting out sql-mode in my.ini. That fixed the problem I was having with importing cacti.sql. Hopefully with fix the other mysql 5 issues you're having.

[zrichardson]

Here's what my "sql-mode" line says:

Code: Select all

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Here's what I found from MySQL's website (http://dev.mysql.com/doc/refman/5.1/en/ ... -mode.html):

STRICT_TRANS_TABLES
If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More detail is given later in this section.

NO_AUTO_CREATE_USER
Prevent GRANT from automatically creating new users if it would otherwise do so, unless a non-empty password also is specified.

NO_ENGINE_SUBSTITUTION
Prevents automatic substitution of storage engine when the requested storage engine is disabled or not compiled in.

I don't believe that the last two have anything to do with it, but it sounds like the STRICT_TRANS_TABLES does. I'll test and re-post with my findings.

[zrichardson]

Okay, all is working great now! Great troubleshooting by commenting that line out!
I determined why when you commented out your "sql-mode" line that you had no errors.
Read what I found on MySQL's website (http://dev.mysql.com/doc/refman/5.1/en/ ... -mode.html):

STRICT_TRANS_TABLES
Enable strict mode for transactional storage engines, and when possible for non-transactional storage engines. Additional details follow.

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a column that has no explicit DEFAULT clause in its definition.

For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back.

For non-transactional tables, the behavior is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:

For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, in this case, the earlier rows still have been inserted or updated. This means that you might get a partial update, which might not be what you want. To avoid this, it's best to use single-row statements because these can be aborted without changing the table.

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 13.1.5, “CREATE TABLE Syntax”.

Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero parts such as 2004-04-00' or “zero” dates. To disallow these as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode.

If you are not using strict mode (that is, neither STRICT_TRANS_TABLES nor STRICT_ALL_TABLES is enabled), MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE. See Section 13.5.4.22, “SHOW WARNINGS Syntax”.

So, according to this, because I had STRICT_TRANS_TABLES in my "sql-mode" line, the Cacti program was generating an invalid insert or update on a table either because the data type was invalid, or because the value was missing or out of range. Because of the "sql-mode", it was completely ignoring the statement at that point and not executing the insert or update statement. (My bet is on the insert statement as it would have added a row to a table stating that a graph had been created)

So here is my new "sql-mode" line in the "my.ini" file where I installed MySQL:

Code: Select all

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I believe this portion of the errors we've uncovered only affects MySQL 5.x as it was the first version to introduce the Strict mode and presents it as a checkbox option during configuration of the database server on installation (it also says "recommended" next to the option).

Would you please have a developer look at this post and see if this makes sense? Or should I post it as a bug in the bug-tracking system?

[zrichardson]

Forgot to add this part:

So the reason your "cacti.sql" file import worked once you commented out your "sql-mode" line is that MySQL automatically adjusted the values as they were being inserted/updated in the tables. At least this is what I'm thinking is happening according the information I just posted.

[TheWitness]

Please view the attached post:

http://forums.cacti.net/viewtopic.php?t=11289

If you have a fresh install, simply run the patch. If you have already installed, run the patch and then the update script.

TheWitness