[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.
> 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...
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
More information about the icinga-users