customer authentication with multi-db and db maintenance

[enopeus]

I have activated customer authentication through 3 media:
- otrs internal db
- external db1
- external db2

when db1 or db2 is unreachable (eg. by entering a wrong IP address or if the db is shutdown for maintenance purpose) I can't access index.pl page.


herunder the connexion error message I get when I try to load otrs/index.pl page:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, webmaster@localhost and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.
Apache/2.2.22 (Ubuntu) Server at xxx Port 80

and the apache2/error.log file:
Thu Mar 20 11:25:24 2014] ZZZAAuto.pm: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAAuto.pm line 7.
[Thu Mar 20 11:25:24 2014] ZZZAuto.pm: Subroutine Load redefined at /opt/otrs/Kernel/Config/Files/ZZZAuto.pm line 7.
[Thu Mar 20 11:25:24 2014] -e: DBI connect('database=gwsp;host=172.16.18.721','otrs',...) failed: Unknown MySQL server host 'xxxxxx' (111) at /opt/otrs//Kernel/System/DB.pm line 252
ERROR: OTRS-CGI-70 Perl: 5.14.2 OS: linux Time: Thu Mar 20 11:25:24 2014

Message: Unknown MySQL server host 'xxxxx' (111)

RemoteAddress: xxxxxx
RequestURI: /otrs/index.pl?Action=AgentTicketZoom;TicketID=4647

Traceback (16402):
Module: Kernel::System::DB::new (OTRS 3.3.5) Line: 223
Module: Kernel::System::CustomerUser::DB::new (OTRS 3.3.5) Line: 105
Module: Kernel::System::CustomerUser::new (OTRS 3.3.5) Line: 105
Module: Kernel::System::Ticket::new (OTRS 3.3.5) Line: 151
Module: Kernel::System::Web::InterfaceAgent::Run (OTRS 3.3.5) Line: 171
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 41
Module: (eval) (v1.99) Line: 204
Module: ModPerl::RegistryCooker::run (v1.99) Line: 204
Module: ModPerl::RegistryCooker::default_handler (v1.99) Line: 170
Module: ModPerl::Registry::handler (v1.99) Line: 31

[Thu Mar 20 11:25:24 2014] [error] [Thu Mar 20 11:25:24 2014] -e: Can't connect to database! at /opt/otrs//Kernel/System/CustomerUser/DB.pm line 105.\n

I would expect otrs to let me log in even if one of my customer backends is not available but I didn't find any solution.

thanks for your help.

[reneeb]

You could wrap the "new" (https://github.com/OTRS/otrs/blob/maste ... ser.pm#L73) with a eval BLOCK

[enopeus]

Thanks Reneeb for your quick answer.

I tried this in Custom/Kernel/System/CustomerUser.pl:
$Self->{"CustomerUser$Count"} = $GenericModule-> eval { new(
Count => $Count,
%{$Self},
PreferencesObject => $Self->{PreferencesObject},
CustomerUserMap => $Self->{ConfigObject}->Get("CustomerUser$Count"),
);}; warn $@ if $@;

but the message already appears in error in the otrs log file and I can not connect to index.pl.

did I misunderstood something?

[reneeb]

It should be

Code: Select all

 eval { $Self->{"CustomerUser$Count"} = $GenericModule->new(
    Count => $Count, 
    %{$Self},
    PreferencesObject => $Self->{PreferencesObject},
    CustomerUserMap => $Self->{ConfigObject}->Get("CustomerUser$Count"),
);} or $Self->{LogObject}->Log( Priority => 'error', Message => $@ );

[enopeus]

unfortunately it doesn't work.

by looking further more in the apache log, the issue seems to be with db file:

Code: Select all

[i]ERROR: OTRS-CGI-70 Perl: 5.14.2 OS: linux Time: Thu Mar 20 19:48:17 2014

 Message: ???

 RemoteAddress: xxxx
 RequestURI: /otrs/index.pl?Action=AgentDashboard

 Traceback (3708): 
   Module: Kernel::System::CustomerUser::DB::new (OTRS 3.3.5) Line: 96
   Module: Kernel::System::CustomerUser::new (OTRS 3.3.5) Line: 105
   Module: Kernel::System::Ticket::ColumnFilter::new (OTRS 3.3.5) Line: 98
   Module: Kernel::System::DynamicField::Driver::Checkbox::new (OTRS 3.3.5) Line: 59
   Module: Kernel::System::DynamicField::Backend::new (OTRS 3.3.5) Line: 130
   Module: Kernel::System::Ticket::new (OTRS 3.3.5) Line: 179
   Module: Kernel::System::Stats::Dynamic::ITSMTicketFirstLevelSolutionRate::new (OTRS 3.3.5) Line: 42
   Module: Kernel::System::Stats::GetObjectName (OTRS 3.3.5) Line: 1500
   Module: Kernel::System::Stats::StatsGet (OTRS 3.3.5) Line: 288
   Module: Kernel::System::Stats::StatsListGet (OTRS 3.3.5) Line: 680
   Module: Kernel::Modules::AgentDashboardCommon::Run (OTRS 3.3.5) Line: 108
   Module: Kernel::System::Web::InterfaceAgent::Run (OTRS 3.3.5) Line: 871
   Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 41
   Module: (eval) (v1.99) Line: 204
   Module: ModPerl::RegistryCooker::run (v1.99) Line: 204
   Module: ModPerl::RegistryCooker::default_handler (v1.99) Line: 170
   Module: ModPerl::Registry::handler (v1.99) Line: 31[/i]

and the System/CustomerUser/DB file:

Code: Select all

# create new db connect if DSN is given
    if ( $Self->{CustomerUserMap}->{Params}->{DSN} ) {
        $Self->{DBObject} = Kernel::System::DB->new(
            LogObject    => $Param{LogObject},
            ConfigObject => $Param{ConfigObject},
            MainObject   => $Param{MainObject},
            EncodeObject => $Param{EncodeObject},
            DatabaseDSN  => $Self->{CustomerUserMap}->{Params}->{DSN},
            DatabaseUser => $Self->{CustomerUserMap}->{Params}->{User},
            DatabasePw   => $Self->{CustomerUserMap}->{Params}->{Password},
            %DatabasePreferences,
            %{ $Self->{CustomerUserMap}->{Params} },
        ) || die('Can\'t connect to database!');

I tried to just remove the

Code: Select all

|| die('Can\'t connect to database!')

statement but without success.

[enopeus]

It is strange that this issue has not been treated before. In the case of external db unavailability otrs is unusable. And the eval function does not seems to prevent die.

[crythias]

In the case of external db unavailability otrs is unusable

Pretty much. If you had a failover environment, you should connect to that instead of direct connection to databases.
Or ... if you know that something is down, comment out the connection to it:

Code: Select all

#    $Self->{'Customer::AuthModule1'} = 'Kernel::System::CustomerAuth::LDAP';

Basically... test if the connection is correct. If it is, enable the connection with the above line (uncommented) otherwise do nothing and the rest for that section won't load.

Edit: and other connections... CustomerUsern, etc.

[reneeb]

unfortunately it doesn't work.

That means you still get the "Internal Server Error"?

Can you post the changed Kernel/System/CustomerUser.pm? I just added a customer backend that doesn't exist in a testsystem and added the eval:

Code: Select all

eval {
        $Self->{"CustomerUser$Count"} = $GenericModule->new(
            Count => $Count,
            %{$Self},
            PreferencesObject => $Self->{PreferencesObject},
            CustomerUserMap => $Self->{ConfigObject}->Get("CustomerUser$Count"),
        );
};

and it worked...

In my previous posts I had an extra $Self->...->Log call to "get informed" when something doesn't work. The "eval { }" catches the error so that you wouldn't recognise that something went wrong.

[enopeus]

Hi reneeb,

please find my CustomerUser.pm file and my Config.pm file.

now I didn't get the error 500 page, but the page is not loaded and the browser is working. in the apache2 error.log, I have the following message:

Code: Select all

can't connect to MySQL server on 'xx.xx.xx.xx' (110)
...

Traceback (1411):
Module: Kernel::Sytem::DB::new OTRS 3.3.5) Line: 223

maybe my config.pm is not correct, but it is a copy/past of otrs admin guide for customer backend...

[enopeus]

Hi reneeb,

in fact I can connect to OTRS but it is really slow.
It needs more than a minute to let me access the connection screen and it takes also a while to let me enter when user and password are given.
was it the same for you?

[reneeb]

Hi reneeb,

in fact I can connect to OTRS but it is really slow.
It needs more than a minute to let me access the connection screen and it takes also a while to let me enter when user and password are given.
was it the same for you?

This is due to a timeout. OTRS waits for x seconds for a connection before it aborts...

[enopeus]

and do you know where this timeout can be modify?

[crythias]

How often will this be a problem for your implementation? Especially, that you won't know about it in advance and can't also comment the appropriate one line in Config.pm when you bring the db down?

[enopeus]

I don't think it will often occure, it is just that the behavior is not acceptable. if my customer backend is not reachable my agents have to be able to access the ticketing tool.
the point is also that we will not have a quick access to production environment. thus commenting a line in a file in production is not as easy as it looks like.

[crythias]

http://search.cpan.org/~capttofu/DBD-my ... D/mysql.pm

If your DSN contains the option "mysql_connect_timeout=##", the connect request to the server will timeout if it has not been successful after the given number of seconds.

Code: Select all

DSN => 'DBI:mysql:database=gwsp;host=xxxxxxx;mysql_connect_timeout=3',

[enopeus]

Thanks Crythias for your help.

nevertheless, it does not solve the problem .
I think that the timeout comes too late and the issue occures before the timeout information is used. it is strange that the eval statement does not stop the connection loop.

[crythias]

the issue occures before the timeout information is used

I don't really understand how this is possible. But good luck.