It was very interesting as my cacti installation began failing today. For no apparent reason. I noticed CPU usage and CPU temperature going through the roof as PHP script ran by cacti was stuck in some infinite loop or active wait state. It seemed to query database non stop without sleeping or anything and it was not doing anything either.

I looked in error log and there was nothing, looked in access log of monitored server ... nothing again. Finally i looked into cacti log and found tons of messages like this:

11/05/2011 01:37:01 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1292', SQL:"update host set status = '3', status_event_count = '0', status_fail_date = '0000-00-00 00:00:00', status_rec_date = '0000-00-00 00:00:00', status_last_error = '', min_time = '0.00000', max_time = '0.00000', cur_time = '0', avg_time = '0', total_polls = '597', failed_polls = '0', availability = '100' where hostname = 'somehostname''
11/05/2011 01:37:01 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1292', SQL:"update host set status = '3', status_event_count = '0', status_fail_date = '0000-00-00 00:00:00', status_rec_date = '0000-00-00 00:00:00', status_last_error = '', min_time = '0.08000', max_time = '0.65000', cur_time = '0.1', avg_time = '0.092562253289474', total_polls = '608', failed_polls = '0', availability = '100' where hostname = 'othersomehostname''
11/05/2011 01:37:01 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1292', SQL:"INSERT INTO poller_time (poller_id, pid, start_time, end_time) VALUES (0, 26442, NOW(), '0000-00-00 00:00:00')'

I tried to google it but it was not very clear what was the issue. I was getting worried that my rrd files got messed up and then epiphany :) A few days earlier i enabled strict mode on mysql on the server that cacti used for its DB storage. Most people do not even know that such thing exists! Strict mode for mysql is something that should be enabled by default in every installation then it would behave more like a database. Long story short with strict mode enabled mysql is much more demanding when it comes to accepting data. You cant save empty strings in stead of enums, you cant insert invalid values in many places and in this case you cant use invalid date values.

So all i had to do to fix my cacti setup was to disable strict mode by commenting out

#sql-mode="TRADITIONAL"

I hope it will help someone :-)