[icinga-users] Problem with PostgreSQL and FROM_UNIXTIME(int)?

Michael Friedrich michael.friedrich at gmail.com
Sun Dec 16 21:10:37 CET 2012

On 16.12.2012 20:57, Bjørn T Johansen wrote:
> On Sun, 16 Dec 2012 20:39:23 +0100
> Michael Friedrich<michael.friedrich at gmail.com>  wrote:
>> On 16.12.2012 20:29, Bjørn T Johansen wrote:
>>> I have just installed Icinga and I see a lot of these in my log...:
>>> Dec 16 03:32:38 gandalf ido2db: Error: database query failed for 'UPDATE icinga_programstatus SET status_update_time=FROM_UNIXTIME(1355625158), program_start_time=FROM_UNIXTIME(1355614426), is_currently_
>>> running=1, process_id=20690, daemon_mode=1, last_command_check=FROM_UNIXTIME(1355625158), last_log_rotation=FROM_UNIXTIME(0), notifications_enabled=1, active_service_checks_enabled=1, passive_service_che
>>> cks_enabled=1, active_host_checks_enabled=1, passive_host_checks_enabled=1, event_handlers_enabled=1, flap_detection_enabled=1, failure_prediction_enabled=1, process_performance_data=0, obsess_over_hosts
>>> =0, obsess_over_services=0, modified_host_attributes=0, modified_service_attributes=0, global_host_event_handler=E'', global_service_event_handler=E'' WHERE instance_id=1' - 'ERROR:  function from_unixti
>>> me(integer) does not exist#012LINE 1: ...PDATE icinga_programstatus SET status_update_time=FROM_UNIXT...#012                                                             ^#012HINT:  No function matches th
>>> e given name and argument types. You might need to add explicit type casts.#012'
>>> Is this a known bug or? How can I fix it?
>> without any hints on version/os/src|pkg it will hard to guess.
>> furthermore, does from_unixtime exist in your icinga database as
>> accessible function?
>>> Regards,
>>> BTJ
> I just thought I would check if this was a known problem first.. And it seems to be that it should have been, but it might have been fixed in a later
> version...

nope, it's not a known issue but just a good guess - i do know the setup 
quite frankly since i implemented postgresql support within icinga, so 
my guesses normally target the right direction here.

> btw, I am running CentOS 6 and PGSQL 9.2 and Icinga 1.7.2 (the latest version in repoforge..)

repoforge got recent version updates pushed, but everyone's waiting for 
dag to build new packages. there's a wiki howto on building newer 
packages on your own, if that's desired.

> I took a quick search and that function seems to be a function that MySQL has and PGSQL has not..

true. and for the easyness of that, there has been put effort into 
adding those 2 functions directly into the pgsql schema in order to have 
the same interface to address within ido2db.

> But I also find a funtion that I could add to make
> PGSQL support the from_unixtime funtion and it seems to be working....

the postgresql schema sql contains that exact function which requires to 
be created on schema import. so i was wondering, if your database setup 
was done properly?

check the 1.7 tree in git, this is what your package also contains.

so, how did you setup your database?

DI (FH) Michael Friedrich

mail:     michael.friedrich at gmail.com
twitter:  https://twitter.com/dnsmichi
jabber:   dnsmichi at jabber.ccc.de
irc:      irc.freenode.net/icinga dnsmichi

icinga open source monitoring
position: lead core developer
url:      https://www.icinga.org

More information about the icinga-users mailing list