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

Bjørn T Johansen btj at havleik.no
Sun Dec 16 21:49:21 CET 2012


On Sun, 16 Dec 2012 21:10:37 +0100
Michael Friedrich <michael.friedrich at gmail.com> wrote:

> 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.
> https://git.icinga.org/?p=icinga-core.git;a=blob;f=module/idoutils/db/pgsql/pgsql.sql;hb=refs/heads/r1.7
> 
> so, how did you setup your database?
> >
> >
> > BTJ
> >
> 
> 

I followed the instruction on the wiki but I found the problem...
I see that the missing function is in the create schema script but the problem is that the create function statement looks like this:

CREATE OR REPLACE FUNCTION from_unixtime(integer) RETURNS timestamp with time zone AS '
         SELECT to_timestamp($1) AS result' LANGUAGE 'SQL';


And I did not notice that there was an error when I ran the script... Apparently PostgreSQL does not understand LANGUAGE 'SQL' and it needs to be in
lowercase, like this LANGUAGE 'sql'.... Then the function is created...


BTJ

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.





More information about the icinga-users mailing list