OTRS can't connect to PostgreSQL database

[marcosaps]

First of all, what I'm using:

- OTRS 3.3.9
- Windows 7 Professional SP1 (x86)
- PostgreSQL 9.3

I'm new to OTRS. I configured OTRS to use PostgresSQL database following the instructions found here:

http://itsm-demo.otrs.com/otrs/public.p ... ;ItemID=53

The whole configuration process went ok. I checked the database connection running C:\otrs\OTRS\bin\otrs.CheckDB.pl. This was the result displayed in the console:

Trying to connect to database
DSN: DBI:Pg:dbname=otrs;host=127.0.0.1;
DatabaseUser: otrs

As you can see, the connection was succesfull. This is my relevant configuraion in C:\otrs\OTRS\Kernel\Config.pm:

$Self->{DatabaseHost} = '127.0.0.1';
$Self->{SecureMode} = 1;
$Self->{Database} = 'otrs';
$Self->{DatabaseUser} = 'otrs';
$Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};host=$Self->{DatabaseHost};";

But when I try to access the application 'http://localhost/otrs/index.pl', I get an HTTP 500 error. Looking at C:\otrs\OTRS\var\log\otrs.log I see messages like this:

[Fri Sep 19 10:28:15 2014][Error][Kernel::System::DB::new][223] could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 5432?

I'm running all this on my local machine. I really don't understand. If the connection was successfull with otrs.CheckDB.pl, why can't it connect using the web application? Everything seems to be ok, yet I'm stuck with this problem and can't proceed.

What am I doing wrong? How can I get rid of this issue?

PS.: By the way, I did all this (configuring PostgreSQL manually) because the OTRS Web Installer (http://localhost/otrs/installer.pl) didn't work. I was getting HTTP 500 error code as well.

Thank you.

Marcos

[Rooobaaat]

1st - in the webinstaller there's a hint, that ONLY MySQL is supported (i think)
2nd - Are you trying to connect with IPv4 oder IPv6? Change the listen_addresses to localhost and make sure localhost resolves to both the IPv4 and IPv6 addresses, or set it to ::1, 127.0.0.1 to explicitly specify both IPv4 and IPv6. Or just set it to * to listen on all interfaces. Alternately, if you don't care about IPv6, connect to 127.0.0.1 instead of localhost.

[marcosaps]

1st - in the webinstaller there's a hint, that ONLY MySQL is supported (i think)
2nd - Are you trying to connect with IPv4 oder IPv6? Change the listen_addresses to localhost and make sure localhost resolves to both the IPv4 and IPv6 addresses, or set it to ::1, 127.0.0.1 to explicitly specify both IPv4 and IPv6. Or just set it to * to listen on all interfaces. Alternately, if you don't care about IPv6, connect to 127.0.0.1 instead of localhost.

Hello, Rooobaat.

This is the relevant content of my pg_hba.conf file:

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

Could you show how it would be like after your modifications?

[marcosaps]

1st - in the webinstaller there's a hint, that ONLY MySQL is supported (i think)
2nd - Are you trying to connect with IPv4 oder IPv6? Change the listen_addresses to localhost and make sure localhost resolves to both the IPv4 and IPv6 addresses, or set it to ::1, 127.0.0.1 to explicitly specify both IPv4 and IPv6. Or just set it to * to listen on all interfaces. Alternately, if you don't care about IPv6, connect to 127.0.0.1 instead of localhost.

I already tried these configurations for listen_addresses in 'postgresql.conf' and nothing has worked:

listen_addresses = '*'
listen_addresses = 'localhost'
listen_addresses = '::1, 127.0.0.1'

What else could be wrong?

[Rooobaaat]

try to disable your IPv6-Interface.
Then restart your PC / PostgreSQL Service

[marcosaps]

try to disable your IPv6-Interface.
Then restart your PC / PostgreSQL Service

I did as you suggested (commented the IPv6 line in pg_hba.conf):

# IPv6 local connections:
#host all all ::1/128 md5

But the problem still persists (HTTP 500). But running otrs.CheckDB.pl the connection is still successfull.

This is the contents of the log file 'C:\otrs\OTRS\var\log\SchedulerERR-1411145105.log':

------------------

DBD::Pg::st DESTROY failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request. at C:/otrs/OTRS/Kernel/System/DB.pm line 639.
DBD::Pg::st execute failed: no connection to the server at C:/otrs/OTRS/Kernel/System/DB.pm line 639.
ERROR: OTRS-otrs.Scheduler-10 Perl: 5.16.3 OS: MSWin32 Time: Fri Sep 19 13:40:26 2014

Message: no connection to the server, SQL: '
SELECT process_name, process_id, process_host, process_create, process_change
FROM process_id
WHERE process_name = ? LIMIT 1'

Traceback (3804):
Module: Kernel::System::PID::PIDGet (OTRS 3.3.9) Line: 181
Module: main::_ServiceStart (unknown version) Line: 485
Module: C:\otrs\OTRS\bin\otrs.Scheduler4win.pl (unknown version) Line: 94

DBI connect('dbname=otrs;host=127.0.0.1;','otrs',...) failed: FATAL: o sistema de banco de dados está desligando at C:/otrs/OTRS/Kernel/System/DB.pm line 252.
ERROR: OTRS-otrs.Scheduler-10 Perl: 5.16.3 OS: MSWin32 Time: Fri Sep 19 13:40:26 2014

Message: FATAL: o sistema de banco de dados está desligando

Traceback (3804):
Module: Kernel::System::DB::new (OTRS 3.3.9) Line: 223
Module: main::_CommonObjects (unknown version) Line: 228
Module: main::_AutoStop (unknown version) Line: 747
Module: main::_ServiceStart (unknown version) Line: 489
Module: C:\otrs\OTRS\bin\otrs.Scheduler4win.pl (unknown version) Line: 94

Got no DBObject! at C:/otrs/OTRS/Kernel/System/PID.pm line 76.
Terminating on signal SIGTERM(15)

------------------

I don't know, but maybe this can be helpful to you.

[Rooobaaat]

Mhm... I've no real idea.... why you don't use MySQL?


Edit:
Maybe it could also be a permission problem in your filesystem. Because you've windows... did you set the permissions to "FULL Access" to "Everyone"?

[marcosaps]

Mhm... I've no real idea.... why you don't use MySQL?

I really have no idea why a Web Installer for any application where you can define your database of choice would require that you have one of the databases installed (in this case MySQL). This is even paradoxical because I need to have a database that I don't want to configure one that I want. Also, OTRS says that it can work with other databases other than MySQL. I'm starting to see that maybe this is not the case. I won't switch to another database because OTRS refuses to work with the one I choose. I've done everything all right and OTRS logs don't tell the real reason of the connection problem.

[crythias]

no connection to server means the pgsql isn't available under the params of the DSN.

[marcosaps]

no connection to server means the pgsql isn't available under the params of the DSN.

Could you elaborate a little more?

[crythias]

I don't know enough about PGSQL to elaborate further. The error message says that the connection via DSN is not working.

What does it mean?

It means that:
a) the server isn't listening
b) the server crashed and isn't listening
c) the server isn't listening where the DSN is pointing to
d) the server isn't running
e) the server is pushing additional error messages and isn't listening
f) you're talking to the wrong server

[marcosaps]

I'm almost sure that my issue is related to this, as I'm convinced that everything is ok with the database server:

viewtopic.php?f=62&t=25158#p100204

So the problem is the license to the Perl ISAPE module provided by ActiveState Perl. Any suggestions how I can fix this?

[jojo]

I'm almost sure that my issue is related to this, as I'm convinced that everything is ok with the database server:

viewtopic.php?f=62&t=25158#p100204

So the problem is the license to the Perl ISAPE module provided by ActiveState Perl. Any suggestions how I can fix this?

Buy one or switch to Linux

[crythias]

I don't understand how connection to your PGSQL server has anything to do with IIS. Connectivity from OTRS to PGSQL is necessary even if the web server isn't running.

[marcosaps]

I'm almost sure that my issue is related to this, as I'm convinced that everything is ok with the database server:

viewtopic.php?f=62&t=25158#p100204

So the problem is the license to the Perl ISAPE module provided by ActiveState Perl. Any suggestions how I can fix this?

Buy one or switch to Linux

Answering your reply I'd rather buy one than switch to Linux.

By the way, it's nice to see how OTRS is free and open source, yet its installer suggests a non free and non open source product. This is contradictory. I think that they should review this.

[jojo]

you are free to use any other PERL product for windows.

But Windows is o a plattform which is the worst choice for running PERL based web applications. You really should considert to switch to Linux or to a managed system

[marcosaps]

I don't understand how connection to your PGSQL server has anything to do with IIS. Connectivity from OTRS to PGSQL is necessary even if the web server isn't running.

The connection problem was solved, but even with this I was getting error 500 while trying to access the index.pl or installer.pl.

[marcosaps]

you are free to use any other PERL product for windows.

But Windows is o a plattform which is the worst choice for running PERL based web applications. You really should considert to switch to Linux or to a managed system

I've heard of Strawberry Perl. Will OTRS work with it?

[jojo]

it will work but will be slow as there is no stable acclerator (PerlEx for IIS or mod_perl for apache2)

[crythias]

The connection problem was solved, but even with this I was getting error 500 while trying to access the index.pl or installer.pl.

I didn't see this mentioned until this point. That 500 error should be in a new topic.