Problems with CustomerData LDAP/DB Oracle

[Cipher]

Hello,

I have some small problems with the CustomerData in OTRS (3.1.7/Debian), the first one is, that a search in Customers is triggering a "Sizelimit exceeded" warning from the LDAP search:

Code: Select all

[Wed Jan 22 17:00:31 2014][Error][Kernel::System::CustomerUser::LDAP::CustomerSearch][363] Sizelimit exceeded

Code: Select all

[Wed Jan 22 17:00:25 2014] [notice] Apache/2.2.22 (Debian) mod_perl/2.0.7 Perl/v5.14.2 configured -- resuming normal operations
ERROR: OTRS-CGI-10 Perl: 5.14.2 OS: linux Time: Wed Jan 22 17:00:31 2014

 Message: Sizelimit exceeded

 Traceback (32671):
   Module: Kernel::System::CustomerUser::LDAP::CustomerSearch (v1.62) Line: 363
   Module: Kernel::System::CustomerUser::CustomerSearch (v1.63) Line: 189
   Module: Kernel::Modules::AdminCustomerUser::_Overview (v1.99) Line: 549
   Module: Kernel::Modules::AdminCustomerUser::Run (v1.99) Line: 489
   Module: Kernel::System::Web::InterfaceAgent::Run (v1.64) Line: 868
   Module: ModPerl::ROOT::ModPerl::Registry::usr_share_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 46
   Module: (eval) (v1.90) Line: 204
   Module: ModPerl::RegistryCooker::run (v1.90) Line: 204
   Module: ModPerl::RegistryCooker::default_handler (v1.90) Line: 170
   Module: ModPerl::Registry::handler (v1.99) Line: 31

I don't notice anything unusual or out of order, but I still don't like errors. Maybe someone has an idea, from where this error is? The LDAP DB is a Active Directory on Windows 2008 R2.

The second problem is with the external customer database. It's a rather old contacts table from another era But the data in there is needed in otrs. It runs on a oracle db and I have a view for it, to "mend" the uncommon data in there to a usable structure (no duplicates and name and email not null, ...). That fixed most of the problems in displaying of the data. But the data should be edited from within otrs (I know, a view won't help here, but my DBA will provide me with either a materialized view or a procedure/trigger to get me good data in the future) and when I click on a customer, all I get is a empty new customer form (read only = 0!).
The problem persists, even with direct read/write access to the table without the view and the data from the LDAP can be edited without problems.

But contacts from the oracle db can be used in tickets and all the info is there (phone, email, etc). It seems that otrs doesn't have the right record to display/can't select it from the db.


The third and last problem is the insertion of new customers in the oracle db. If I try to I get a error message, that is the result of too many columns that are tried to be inserted by otrs:

Code: Select all

INSERT INTO OTRS.V_CONTACT ( NAME, CONTACT_ID, E_MAIL, TELEPHONE_NO, FAX_NO, INSURANCE_COMPANY_ID, SERVICE_PARTNER_ID, POSITION, create_time, create_by, change_time, change_by) VALUES ( 'Test Tester', 'asdasdasdasdasd', 'test.test@test.test', '', '', '', '', '', '2014-01-22 17:38:24' , 2, '2014-01-22 17:38:24' , 2)'

the last four columns are not available in the oracle db, so why is otrs trying to write these values, even if they are never mentioned in the configuration of this customer db? Is there a way to prevent this or change the columns to some we already have?

I hope my description is understandable, since English, as you might have guessed from some abused grammatical constructs, is not my first language

Greetings and many thanks!
Paul

[wurzel]

Hi,

Hello,

I have some small problems with the CustomerData in OTRS (3.1.7/Debian), the first one is, that a search in Customers is triggering a "Sizelimit exceeded" warning from the LDAP search:

it's a warning, because of too many users to display. I ignore them.

(...)

I don't notice anything unusual or out of order, but I still don't like errors. Maybe someone has an idea, from where this error is? The LDAP DB is a Active Directory on Windows 2008 R2.

It is no error, just a warning. But I do not know, how to get rid of it.

The third and last problem is the insertion of new customers in the oracle db. If I try to I get a error message, that is the result of too many columns that are tried to be inserted by otrs:

Code: Select all

INSERT INTO OTRS.V_CONTACT ( NAME, CONTACT_ID, E_MAIL, TELEPHONE_NO, FAX_NO, INSURANCE_COMPANY_ID, SERVICE_PARTNER_ID, POSITION, create_time, create_by, change_time, change_by) VALUES ( 'Test Tester', 'asdasdasdasdasd', 'test.test@test.test', '', '', '', '', '', '2014-01-22 17:38:24' , 2, '2014-01-22 17:38:24' , 2)'

the last four columns are not available in the oracle db, so why is otrs trying to write these values,

I think, that there are mandatory fields which are always mandatory. (valid, created_by) etc.

There is a option called ForeignDB = 1 or similar. Perhaps you can use this?


For your second issue, I don't know.

Flo

[Cipher]

Hi,

it's a warning, because of too many users to display. I ignore them.

It is no error, just a warning. But I do not know, how to get rid of it.

Ah ok, I thought it was something more pressing, if I can safely ignore it, thats ok, I can do that

I think, that there are mandatory fields which are always mandatory. (valid, created_by) etc.

There is a option called ForeignDB = 1 or similar. Perhaps you can use this?


For your second issue, I don't know.

Flo

ForeignDB = 1 sounds like an option I could use for that, thanks for the tip! I will try it tomorrow, thank you very much

[wurzel]

Hi,

ForeignDB = 1 sounds like an option I could use for that, thanks for the tip! I will try it tomorrow, thank you very much

search for it in Defaults.pm or search here in the otterhub, you'll find something like

Code: Select all

ForeignDB => 1,    # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields

This should do it (or at least help)

Flo

[Cipher]

Hi,

ForeignDB = 1 sounds like an option I could use for that, thanks for the tip! I will try it tomorrow, thank you very much

search for it in Defaults.pm or search here in the otterhub, you'll find something like

Code: Select all

ForeignDB => 1,    # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields

This should do it (or at least help)

Flo

Sadly it didn't help in the Defaults.pm, the only ForeignDB I found is for the CustomerBackend, but not for CustomerUser.

Defaults.pm with ForeignDB in CustomerCompany:

Code: Select all

    $Self->{CustomerCompany} = {
        Params => {
            # if you want to use an external database, add the
            # required settings
#            DSN => 'DBI:odbc:yourdsn',
#            DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
#            User => '',
#            Password => '',
            Table => 'customer_company',
#            ForeignDB => 0,    # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields
        },

Defaults.pm CustomerUser:

Code: Select all

    $Self->{CustomerUser} = {
        Name   => 'Database Backend',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            # if you want to use an external database, add the
            # required settings
#            DSN => 'DBI:odbc:yourdsn',
#            DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
#            User => '',
#            Password => '',
            Table => 'customer_user',
            # if your frontend is unicode and the charset of your
            # customer database server is iso-8859-1, use these options.
#           SourceCharset => 'iso-8859-1',
#           DestCharset => 'utf-8',

            # CaseSensitive will control if the SQL statements need LOWER()
            #   function calls to work case insensitively. Setting this to
            #   1 will improve performance dramatically on large databases.
            CaseSensitive => 0,
        },

My Config.pm with CustomerUser and ForeignDB (sadly not working):

Code: Select all

   $Self->{CustomerUser2} = {
         Name => 'LabDB',
         Module => 'Kernel::System::CustomerUser::DB',
         Params => {
               DSN => 'DBI:Oracle:host=dblaba-crs.domain;SERVICE_NAME=LABDB;port=1521',
               User => 'OTRS',
               Password => 'OTRSPW',
               Table => 'OTRS.V_CONTACT',
               ForeignDB => 1,
         },

Is there another way around these fields?