I think the major point you're missing is that this isn't OTRS's issue to solve, but rather the point of failure for your backends. If you're digging into OTRS code to fix this, you're going in the wrong direction. You should fix the reliability of connection to your data sources and make sure the connection is redundant. You do not want to have to rework OTRS just because your Microsoft environment is going down. The connectivity from/to OTRS *must* be agnostic to your (potentially) flaky connections, in order to smoothly upgrade OTRS. This wouldn't be unique to OTRS, though. If you're concerned with the connections, be concerned with the connections, not OTRS. On Thu, Apr 25, 2013 at 8:29 AM, Cyrille Bollu wrote:

Hi Michiel,

Thanks for the answer; There's some food for thought in there.

Of course, we don't use a MySQL server for our Customer data; That would have been too easy... ;-) So the proxy solution doesn't apply. (We use a MS-SQL non-clustered backend btw)

My understanding is now the following:

For LDAP:

1. The LDAP solution I gave works ok for Auth.pm as a connection to one of the LDAP servers is initiated at every agent login (see Kernel::System::Auth::LDAP->Auth()). 2. The LDAP solution I gave wouldn't work perfectly for CustomerUser.pm as a connection to one of the LDAP servers would be initiated at application startup, and kept open until application ends (see Kernel::System::CustomerUser::LDAP->new()). Failover to another LDAP server would thus require restarting the application.

For ODBC:

1. I can't use 2 data backends as OTRS crashes when any of the backends is unavailable + it's not efficient (but I don't really matter about that)

Based on this understanding, I think I'll go for a scripted import of CustomerUser and CustomerCompany data.

Other options that I'm still considering are:

1. Customize OTRS so that it won't die when a CustomerUser backend isn't available => This doesn't seem doable 2. Customize OTRS so that it accepts a 2nd DSN to try to connect to in case it fails to connect to the primary one at application startup => Restarting the application would still be needed in case of backend failure. 3. Add DBD::Multiplex support to OTRS => Doesn't seem well supported (no package in Ubuntu)

((enjoy))

Cyrille

Le 25/04/2013 09:54, Michiel Beijen a écrit :

Hi Cyrille,

First of all, if you use a MySQL server it's typically better and faster to connect via DBD::mysql rather than via ODBC.

Second, you'll want to use MySQL-proxy or a similar solution as a 'load balancer' for your MySQL cluster.

https://launchpad.net/mysql-proxy http://www.slideshare.net/KrisBuytaert/mysql-ha-alternatives-2010

That way from OTRS you connect to only one 'virtual' server. If you'd define two data backends all queries typically hit all cluster database nodes which seems very inefficient to me.

With the LDAP example you gave the 'failover' happens at connection time. Within mod_perl typically the connection is not set up for each query but only once and then is kept open by Apache::DBI. Michiel Beijen Senior Developer

OTRS BV Schipholweg 103 2316 XC Leiden The Netherlands

T: +31 71 8200 255 I: http://www.otrs.com

OTRS 3.2 - More than a Help Desk System – Process and Customer Management – Be an early bird with our special offer - http://j.mp/11TFPSr

On Wed, Apr 24, 2013 at 4:56 PM, Cyrille Bollu wrote:

...

Hi list,

I'm trying to find out how I could setup OTRS to use a redundant ODBC backend for CustomerUser objects.

I initially thought I could setup 2 backends pointing to 2 mirrored mysql servers. But, it seems that OTRS' code dies with a nice HTTP 500 error when a backend isn't available.

Has anyone already worked on such a setup?

I remember doing such a setup for agents authentication with an LDAP backend using the following in config.pm:

# LDAP agent backend my @ldaphosts = ('ldap2','ldap1'); $Self->{'AuthModule'} = 'Kernel::System::Auth::LDAP'; $Self->{'AuthModule::LDAP::Host'} = \@ldaphosts; ...

And it seem to work fine at that time.

Now, is there an easy solution for ODBC backends?

Best regards,

Cyrille --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

I would love to do something with DBD::Multi but this doesn't seem welll supported by Linux distributions. In the end, I'm considering using the CustomerUserImportExport and CustomerCompanyImportExport plugin from OPAR (http://opar.perl-services.de/bin/index.cgi/package/author/CAPEIT). Br, Cyrille Le 25/04/2013 21:26, Michiel Beijen a écrit :

Hi Cyrille,

On Thu, Apr 25, 2013 at 2:29 PM, Cyrille Bollu wrote:

...

Of course, we don't use a MySQL server for our Customer data; That would have been too easy... ;-) So the proxy solution doesn't apply. (We use a MS-SQL non-clustered backend btw) I'm sorry, I don't know why I assumed MySQL.

...

Based on this understanding, I think I'll go for a scripted import of CustomerUser and CustomerCompany data. This is of course always an option, and would probably be easiest.

...

Customize OTRS so that it accepts a 2nd DSN to try to connect to in case it fails to connect to the primary one at application startup => Restarting the application would still be needed in case of backend failure. Yeah this could be possible.

...

Add DBD::Multiplex support to OTRS => Doesn't seem well supported (no package in Ubuntu) Maybe the better module to use is DBIx::HA https://metacpan.org/module/DBIx::HA or DBD::Multi https://metacpan.org/module/DBD::Multi

And one other option might be, that if the app you pull data from is HA, to create a customer backend for this specific app that uses its API, instead of raw SQL.

-- Michiel --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs