Cacti timezone issues

[jfstenuit]

I recently updated to 0.8.8a with rrdtool 1.4.7, as I tough my old version (0.8.7g/1.2.9) where the cause of my issue.

Indeed, this didn't solve my problem, which is that graphs do not update.

Actually, I found out that the issue was caused by the php.ini value date.timezone = "UTC".

This should have an impact on unix timestamps provided to rrdtool, but apparently it does !

From the logs :
07/25/2012 09:45:20 AM - POLLER: Poller[0] CACTI2RRD: /usr/bin/rrdtool update /var/www/cacti/rra/magento_stg_db_traffic_in_321.rrd --template traffic_in:traffic_out 1343227517:1119063717:3360557635

The human-readable time is OK (logging actually occurred at 09:45 UTC), but the unix timestamp supplied to rrdtool is wrong (1343227517 is actually 14:45 UTC).

Needless to say, my rrd files are now all messed up.

Does anyone know the correct way to change the time zone in Cacti (in my case to have everything relate to UTC) ?

[jfstenuit]

After a lot of investigation, I figured out that cacti was translating back and forth between human-readable times (in specific timezones) and unix timestamps, causing all kinds of issues.

I found out that three elements must have their timezone aligned to make that kind of setup work :
[*] cacti
[*] rrdtool
[*] mysql

Cacti time can be set by adding a "date.timezone" value in /etc/php.ini.

Cacti must be instructed to reflect this timezone settings to MySQL database. I got the idea by reading http://bugs.cacti.net/view.php?id=2144.
My diff against 0.8.8a :

Code: Select all

*** include/global.php.orig     2012-07-25 12:01:48.000000000 +0000
--- include/global.php  2012-07-25 12:04:36.000000000 +0000
***************
*** 223,228 ****
--- 223,230 ----
  include_once($config["library_path"] . "/variables.php");
  include_once($config["library_path"] . "/auth.php");

+ db_execute(sprintf("SET time_zone = '%s'", date_default_timezone_get()));
+
  api_plugin_hook("config_insert");

  /* current cacti version */

Cacti must also be instructed to reflect this timezone to rrdtool. This came clearly by reading http://nicolas.vanwambeke.net/wp/2010/0 ... g-rrdtool/.
Patch against 0.8.8a :

Code: Select all

*** ./lib/rrd.php.orig  2012-07-25 12:06:30.000000000 +0000
--- ./lib/rrd.php       2012-07-25 12:12:08.000000000 +0000
***************
*** 39,44 ****
--- 39,47 ----
                putenv("RRD_DEFAULT_FONT=" . read_config_option("path_rrdtool_default_font"));
        }

+       /* Set the time zone */
+       putenv(sprintf("TZ=%s", date_default_timezone_get()));
+
        if ($output_to_term) {
                $command = read_config_option("path_rrdtool") . " - ";
        }else{
***************
*** 92,97 ****
--- 95,103 ----
                $pipe_mode = "rb";
        }

+       /* Set the time zone */
+       putenv(sprintf("TZ=%s", date_default_timezone_get()));
+
        /* an empty $rrdtool_pipe array means no fp is available */
        if (!is_resource($rrdtool_pipe)) {
                session_write_close();
***************
*** 405,410 ****
--- 411,419 ----
                $rrd_tune .= " --data-source-rename $data_source_name:" . $rrd_tune_array["data-source-rename"];
        }

+       /* Set the time zone */
+       putenv(sprintf("TZ=%s", date_default_timezone_get()));
+
        if ($rrd_tune != "") {
                if (file_exists($data_source_path) == true) {
                        $fp = popen(read_config_option("path_rrdtool") . " tune $data_source_path $rrd_tune", "r");

Now, the tricky part is : both your rrd and you MySQL databases still refer to the previous timezone, so you need to update both !

In the database, you need to reset all poller status (before doing the following, you may want to ensure that no poller is running and no poller is scheduled to run) :
truncate poller;
truncate poller_command;
truncate poller_output;
truncate poller_time;

To support symbolic timezone names in MySQL, you should populate the mysql.timezone table using this command :
/usr/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Then, you need to fix all the rrds ! Maybe the trickiest part. I found no other way than writing a small bit of perl code :

Code: Select all

#!/usr/bin/perl

my ($fn)=@ARGV;

my $tmpfn="/tmp/rrdfix.$$.xml";

die "Cannot find $fn : $!" unless (-f $fn);

open(IN,"rrdtool dump $fn|") or die "Cannot spawn rrdtool : ".$!;
open(OUT,">$tmpfn");
while(<IN>) {
  if (/(.*?<lastupdate>)(\d+)(<\/lastupdate>.*)/) {
    my ($pre,$lastu,$post)=($1,$2,$3);
    print OUT $pre.($lastu-18000).$post;
  } else {
    print OUT $_;
  }
}
close(OUT);
close(IN);

system("rrdtool restore $tmpfn $fn -f");
unlink $tmpfn;

[immauss]

This is brilliant. Thanks. I was having the same problem with a new install on openSuSE 12.1. I had tried to import some data from a previous install and assumed that was the issue. After I started again from scratch and had the same problem. .

I followed your directions and they worked perfectly. (I had to manually make the changes to rrd.php for some reason as patch didn't like any of the hunks, but still worked great. )

THANKS!!

[ScorpionSting]

Very awesome....

[blammo]

http://n00blab.com/correcting-cacti-tim ... mment-3854

Correcting Cacti Timezone

Battled a super annoying issue with what appeared to be Cacti not displaying graphs, which after half a day of messing around ended up being a timezone setting…..grrrrrrrrrrrr

After discovering Cacti was indeed graphing my expected values, just 4 hours ahead of my local time (EDT, America/Toronto) I realized cacti.log was showing UTC timestamps.

After confirming my Linux system time:

pi@raspberrypi ~ $ date
Fri Aug 17 04:39:13 EDT 2012

I dug around on cacti.net and found instructions on updating php.ini with timezone values:

http://docs.cacti.net/faq#time_zone_warning


I had seen this warning while checking the apache2 error log earlier and corrected the php.ini file accordingly:

sudo vi /etc/php5/apache2/php.ini

/timezone

date.timezone = America/Toronto

:wq

The list of supported php timezones can be found here

No luck. Cacti’s log still tracked in UTC and graph timezones were still off. I read about mysql and rrdtool timezone altering etc…but both tools were fine, showing the correct system time.

finally i decided to search for any other php.ini file that might exist on my system:

sudo find / -name php.ini

/etc/php5/apache2/php.ini
/etc/php5/cli/php.ini

what do you know…..

updated the alternate php.ini file accordingly….and we’re live!

wow…i need a beer.

[jasem200]

simply add this line into the file graph_image.php with your time zone
add it on the second line just under the <?php

<?php
putenv('TZ=Europe/Berlin');

[alexvelitsky]

Hi guys,

I tried to perform cacti server version upgrade v1.2.24 -> v1.2.25 and now stuck on step "Cacti Server v1.2.25 - Installation Wizard" now. Wizard shows following error:

ERROR: Your MySQL TimeZone database is not populated. Please populate this database before proceeding.

I try this but:

vel@debian:~$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u cacti -p cacti
Enter password:
ERROR 1146 (42S02) at line 10: Table 'cacti.time_zone' doesn't exist

They I check if this table exist in my cacti database - it's not for some reason:

vel@debian:~$ mysqlshow cacti -u cacti -p
Enter password:
Database: cacti
+-------------------------------------+
| Tables |
+-------------------------------------+
| aggregate_graph_templates |
| aggregate_graph_templates_graph |
| aggregate_graph_templates_item |
| aggregate_graphs |
| aggregate_graphs_graph_item |
| aggregate_graphs_items |
| automation_devices |
| automation_graph_rule_items |
| automation_graph_rules |
| automation_ips |
| automation_match_rule_items |
| automation_networks |
| automation_processes |
| automation_snmp |
| automation_snmp_items |
| automation_templates |
| automation_tree_rule_items |
| automation_tree_rules |
| cdef |
| cdef_items |
| color_template_items |
| color_templates |
| colors |
| data_debug |
| data_input |
| data_input_data |
| data_input_fields |
| data_local |
| data_source_profiles |
| data_source_profiles_cf |
| data_source_profiles_rra |
| data_source_purge_action |
| data_source_purge_temp |
| data_source_stats_daily |
| data_source_stats_hourly |
| data_source_stats_hourly_cache |
| data_source_stats_hourly_last |
| data_source_stats_monthly |
| data_source_stats_weekly |
| data_source_stats_yearly |
| data_template |
| data_template_data |
| data_template_rrd |
| external_links |
| graph_local |
| graph_template_input |
| graph_template_input_defs |
| graph_templates |
| graph_templates_gprint |
| graph_templates_graph |
| graph_templates_item |
| graph_tree |
| graph_tree_items |
| host |
| host_graph |
| host_snmp_cache |
| host_snmp_query |
| host_template |
| host_template_graph |
| host_template_snmp_query |
| plugin_config |
| plugin_db_changes |
| plugin_hooks |
| plugin_notification_lists |
| plugin_realms |
| plugin_thold_contacts |
| plugin_thold_daemon_data |
| plugin_thold_host_failed |
| plugin_thold_host_template |
| plugin_thold_log |
| plugin_thold_template_contact |
| plugin_thold_threshold_contact |
| poller |
| poller_command |
| poller_data_template_field_mappings |
| poller_item |
| poller_output |
| poller_output_boost |
| poller_output_boost_local_data_ids |
| poller_output_boost_processes |
| poller_output_realtime |
| poller_reindex |
| poller_resource_cache |
| poller_time |
| processes |
| reports |
| reports_items |
| rrdcheck |
| sessions |
| settings |
| settings_tree |
| settings_user |
| settings_user_group |
| sites |
| snmp_query |
| snmp_query_graph |
| snmp_query_graph_rrd |
| snmp_query_graph_rrd_sv |
| snmp_query_graph_sv |
| snmpagent_cache |
| snmpagent_cache_notifications |
| snmpagent_cache_textual_conventions |
| snmpagent_managers |
| snmpagent_managers_notifications |
| snmpagent_mibs |
| snmpagent_notifications_log |
| thold_data |
| thold_template |
| user_auth |
| user_auth_cache |
| user_auth_group |
| user_auth_group_members |
| user_auth_group_perms |
| user_auth_group_realm |
| user_auth_perms |
| user_auth_realm |
| user_auth_row_cache |
| user_domains |
| user_domains_ldap |
| user_log |
| vdef |
| vdef_items |
| version |
+-------------------------------------+

Pls help me to fix this timezone issue. How to create this table or maybe there's another fix of this issue?
Thanks!

[phalek]

You are importing it into the wrong database. It needs to be imported into the mysql database instead of the cacti one:

Code: Select all

# Create the timezone data and import it into the mysql database
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/mysql_timezone.sql
mysql mysql < /tmp/mysql_timezone.sql

[alexvelitsky]

Hi! Thx for your prompt reply!

I performed your recommendations but:


vel@debian:~$ mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/mysql_timezone.sql
vel@debian:~$ mysql mysql < /tmp/mysql_timezone.sql
ERROR 1045 (28000): Access denied for user 'vel'@'localhost' (using password: NO)
vel@debian:~$ mysql -u cacti -p cacti < /tmp/mysql_timezone.sql
Enter password:
ERROR 1054 (42S22) at line 15: Unknown column 'Use_leap_seconds' in 'field list'
vel@debian:~$
vel@debian:~$

[phalek]

You need to execute the commands using the root login. So either become root and execute these, or at least use the "-u root -p" option.

[alexvelitsky]

Maybe something is wrong with root access to my database?

vel@debian:~$ mysql -u root -p cacti < /tmp/mysql_timezone.sql
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
vel@debian:~$

MariaDB [cacti]> SELECT User FROM mysql.user;
+------------+
| User |
+------------+
| cacti |
| root |
| rsyslog |
| ‘root’ |
+------------+
4 rows in set (0.001 sec)

MariaDB [cacti]> SHOW GRANTS;
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [cacti]> SHOW GRANTS FOR cacti;
ERROR 1141 (42000): There is no such grant defined for user 'cacti' on host '%'
MariaDB [cacti]>

Thanks!

[alexvelitsky]

Also, there's the same issue under root:

root@debian:~# mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/mysql_timezone.sql
root@debian:~# mysql -u cacti -p cacti < /tmp/mysql_timezone.sql
Enter password:
ERROR 1054 (42S22) at line 15: Unknown column 'Use_leap_seconds' in 'field list'
root@debian:~#

[phalek]

see below ...

[phalek]

Can you actually just copy&paste these commands, run under root and not change anything ?

Code: Select all

mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/mysql_timezone.sql
mysql mysql < /tmp/mysql_timezone.sql

[alexvelitsky]

Thanks!
It solved my MySQL TimeZone database population issue:

root@debian:~# mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/mysql_timezone.sql
root@debian:~# mysql mysql < /tmp/mysql_timezone.sql
root@debian:~#

Sorry for misunderstanding.