[dev] How many tanle are required for External Backends ?

4 Jan 2006

      Hi all,

If you configure an external backend strangely OTRS 2.0.4 will make 
request on other tables
than the only customer one.

I've got a PostgresSQL database where OTRS is installed.
My Customers are in an Oracle database so I've changed 
$Self->{CustomerUser} in Config.pm
to override the internal default one.

While searching and displaying current customers is OK, creating failed 
with:

Software error:

Can't call method "fetchrow_array" on an undefined value at /home/otrs//Kernel/System/DB.pm line 477.

In fact OTRS try to select valid table in Oracle not in PostgresQL where the table is located.

Where is the catch ?

Here the apache logs:

And in the apache logs:

[Wed Jan  4 15:40:47 2006] -e: DBD::Oracle::db prepare failed: 
ORA-00942: table or view does not exist (DBD ERROR: error possibly near 
<*> indicator at char 15 in 'SELECT id FROM <*>valid WHERE name = 
'valid'') [for Statement "SELECT id FROM valid WHERE name = 'valid'"] at 
/home/otrs//Kernel/System/DB.pm line 432.
ERROR: OTRS-CGI-10 Perl: 5.8.0 OS: linux Time: Wed Jan  4 15:40:47 2006

 Message: ORA-00942: table or view does not exist (DBD ERROR: error 
possibly near <*> indicator at char 15 in 'SELECT id FROM <*>valid WHERE 
name = 'valid''), SQL: 'SELECT id FROM valid WHERE name = 'valid''

 Traceback (27021):
   Module: Kernel::System::DB::GetValidIDs (v1.47) Line: 680
   Module: Kernel::System::CustomerUser::DB::CustomerSearch (v1.36) 
Line: 178
   Module: Kernel::System::CustomerUser::DB::CustomerUserAdd (v1.36) 
Line: 340
   Module: Kernel::System::CustomerUser::CustomerUserAdd (v1.22) Line: 293
   Module: Kernel::Modules::AdminCustomerUser::Run (v1.37 ) Line: 260
   Module: Kernel::System::Web::InterfaceAgent::Run (v1.8) Line: 651
   Module: 
ModPerl::ROOT::ModPerl::Registry::home_otrs_bin_cgi_2dbin_index_2epl::handler 
(v) Line: 48
   Module: (eval) (v1.80) Line: 187
   Module: ModPerl::RegistryCooker::run (v1.80) Line: 187
   Module: ModPerl::RegistryCooker::default_handler (v1.80) Line: 159
   Module: ModPerl::Registry::handler (v1.99) Line: 16

[Wed Jan 04 15:40:47 2006] [error] 27021: ModPerl::Registry: [Wed Jan  4 
15:40:47 2006] -e: Can't call method "fetchrow_array" on an undefined 
value at /home/otrs//Kernel/System/DB.pm line 477.!

Here my Config.pm:
package Kernel::Config;
sub Load {
    my $Self = shift;
    $Self->{DatabaseHost} = 'localhost';
    $Self->{Database} = 'otrs';
    $Self->{DatabaseUser} = '*********';
    $Self->{DatabasePw} = '********';
    $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};";
    $Self->{Home} = '/home/otrs';
    $Self->{SecureMode} = 1;
    $Self->{'Ticket::Frontend::CustomerInfoCompose'} = 1;
    $Self->{'Ticket::Frontend::CustomerInfoZoom'} = 1;
    $Self->{'Ticket::Frontend::CustomerInfoQueue'} = 0;
    $Self->{FQDN} = 'support.perinfo.biz';
    $Self->{AdminEmail} = 'fguiliani@perinfo.com';
    $Self->{Organization} = 'Perinfo';
    $Self->{DefaultLanguage} = 'fr';
    $Self->{DefaultCharset} = 'iso-8859-15';
    $Self->{CheckEmailAddresses} = 0;
    $Self->{SessionMaxTime} = 30*24*60*60;
    $Self->{SessionMaxIdleTime} = 3*24*60*60;
    $Self->{'TimeWorkingHours'} = {
        Mon => [ 9,10,11,12,13,14,15,16,17,18 ],
        Tue => [ 9,10,11,12,13,14,15,16,17,18 ],
        Wed => [ 9,10,11,12,13,14,15,16,17,18 ],
        Thu => [ 9,10,11,12,13,14,15,16,17,18 ],
        Fri => [ 9,10,11,12,13,14,15,16,17,18 ],
        Sat => [  ],
        Sun => [  ],
    };
    $Self->{'TimeVacationDays'} = {
        1 => {
            1 => 'Nouvel an',
        },
        5 => {
            1 => '1er mai',
        },
        7 => {
            14 => '14 juillet',
        },
        11 => {
            11 => '11 novembre',
        },
        12 => {
            25 => 'Noël',
            26 => 'Saint Etienne',
        },
    };
    $Self->{SwitchToUser} = 1;   
    $Self->{SpellCheckerDictDefault} = 'francais';
    $Self->{PreferencesGroups}->{SpellDict}->{Data} = {
            'francais'=> 'Francais',
            'english' => 'English',
            'deutsch' => 'Deutsch',
        };
    $Self->{CustomerGroupSupport} = 1;
    $Self->{CustomerPanelLogoutURL} = 'http://www.perinfo.com';
    $ENV{ORACLE_HOME} = '/oracle/8.1.7';
    $ENV{NLS_LANG} = "french_france.we8iso8859p1";
    $ENV{LD_LIBRARY_PATH} = "/oracle/8.1.7/lib";
    $Self->{CustomerUser} = {
        Name => 'BDD_GESCAR',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            DSN => 'DBI:Oracle:sid=perorcl;host=192.100.0.11;port=1521;',
            User => '********',
            Password => '**********',
            Table => 'otrs_customer',
        },
        CustomerKey => 'login',
        CustomerID => 'customer_id',
        CustomerValid => 'valid_id',
        CustomerUserListFields => ['first_name', 'last_name', 'email'],
        CustomerUserSearchFields => ['login', 'last_name', 'customer_id'],
        CustomerUserSearchPrefix => '',
        CustomerUserSearchSuffix => '*',
        CustomerUserSearchListLimit => 250,
        CustomerUserPostMasterSearchFields => ['email'],
        CustomerUserNameFields => ['salutation', 'first_name', 'last_name'],
        CustomerUserEmailUniqCheck => 1,
        Map => [
            [ 'UserSalutation', 'Salutation', 'salutation',  1, 0, 
'var', '', 0 ],
            [ 'UserFirstname',  'Firstname',  'first_name',  1, 1, 
'var', '', 0 ],
            [ 'UserLastname',   'Lastname',   'last_name',   1, 1, 
'var', '', 0 ],
            [ 'UserLogin',      'Username',   'login',       1, 1, 
'var', '', 0 ],
            [ 'UserPassword',   'Password',   'pw',          0, 1, 
'var', '', 0 ],
            [ 'UserEmail',      'Email',      'email',       0, 1, 
'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'customer_id', 0, 1, 
'var', '', 0 ],
            [ 'UserComment',     'Comment',   'comments',    1, 0, 
'var', '', 0 ],
            [ 'UserPhone',     'Telephone',   'phone',    1, 0, 'var', 
'', 0 ],
            [ 'UserFax',     'Fax',   'fax',    1, 0, 'var', '', 0 ],
            [ 'ValidID',         'Validité',     'valid_id',    0, 1, 
'int', '', 0 ],
        ],
        Selections => {
            UserSalutation => {
                'M.' => 'M.',
                'Mme.' => 'Mme.',
                'Mlle.' => 'Mlle.',
            },
        },
    };
}

use strict;
use vars qw(@ISA $VERSION);
use Kernel::Config::Defaults;
push (@ISA, 'Kernel::Config::Defaults');
$VERSION = '$Revision: 1.16 $';
$VERSION =~ s/^\$.*:\W(.*)\W.+?$/$1/;

1;

Florent,

[dev] How many tanle are required for External Backends ?

Florent Guiliani