External Customer DB

[keithf4]

Evaluating OTRS and trying to set up an external customer DB. OTRS runs with Postgresql but the customer DB will be in MySQL. Everything's running on the same system for the test, but the MySQL DB will be on an external machine for production. Been trying to follow the manual to get this done, but so far no luck even with everything local. Probably just missing something obvious. Below is the line in Config.pm for my external DB. I know the user and database itself works because I've got phpmyadmin set up on the same machine and I can get into it fine. And everything in OTRS works perfectly fine without the external DB code.

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=mainstreet;host=127.0.0.1',
            User => '...edited out...',
            Password => '...edited out...',
            Table => 'otrs_customer_user',
            # 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,
        },

        # customer uniq id
        CustomerKey => 'login',

        # customer #
        CustomerID             => 'customer_id',
        CustomerValid          => 'valid_id',
        CustomerUserListFields => [ 'first_name', 'last_name', 'email' ],

#        CustomerUserListFields => ['login', 'first_name', 'last_name', 'customer_id', 'email'],
        CustomerUserSearchFields           => [ 'login', 'first_name', 'last_name', 'customer_id' ],
        CustomerUserSearchPrefix           => '*',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 250,
        CustomerUserPostMasterSearchFields => ['email'],
        CustomerUserNameFields     => [ 'title', 'first_name', 'last_name' ],
        CustomerUserEmailUniqCheck => 1,

#        # show now own tickets in customer panel, CompanyTickets
#        CustomerUserExcludePrimaryCustomerID => 0,
#        # generate auto logins
#        AutoLoginCreation => 0,
#        # generate auto login prefix
#        AutoLoginCreationPrefix => 'auto',
#        # admin can change customer preferences
#        AdminSetPreferences => 1,
#        # use customer company support (reference to company, See CustomerCompany settings)
#        CustomerCompanySupport => 1,
#        # cache time to live in sec. - cache any database queries
#        CacheTTL => 0,
#        # just a read only source
#        ReadOnly => 1,
        Map => [
# note: Login, Email and CustomerID needed!
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
            [ 'UserTitle',      'Title',      'title',      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',   'password',   0, 0, 'var', '', 0 ],
            [ 'UserEmail',      'Email',      'email',      1, 1, 'var', '', 0 ],
			[ 'UserEmail2',		'Email 2',	  'email2',		1, 0, 'var', '', 0 ],
			[ 'UserEmail3',		'Email 3',	  'email3',		1, 0, 'var', '', 0 ],
			[ 'UserEmail4', 	'Email 4',	  'email4',		1, 0, 'var', '', 0 ],

#            [ 'UserEmail',      'Email', 'email',           1, 1, 'var', '$Env{"CGIHandle"}?Action=AgentTicketCompose&ResponseID=1&TicketID=$Data{"TicketID"}&ArticleID=$Data{"ArticleID"}', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'customer_id', 0, 1, 'var', '', 0 ],

#            [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var', '', 0 ],
            [ 'UserPhone',        'Phone',       'phone',        1, 0, 'var', '', 0 ],
            [ 'UserFax',          'Fax',         'fax',          1, 0, 'var', '', 0 ],
            [ 'UserMobile',       'Mobile',      'mobile',       1, 0, 'var', '', 0 ],
            [ 'UserComment',      'Comment',     'comments',     1, 0, 'var', '', 0 ],
            [ 'ValidID',          'Valid',       'valid_id',     0, 1, 'int', '', 0 ],
        ],

        # default selections
        Selections => {

#            UserTitle => {
#                'Mr.' => 'Mr.',
#                'Mrs.' => 'Mrs.',
#            },
#			 Offices?
        },
    };

I get this error in the apache log when I try to load otrs. The second part of the error is what shows up in the browser.

Code: Select all

[Thu Feb 10 11:26:57 2011] [error] [Thu Feb 10 11:26:57 2011] -e: Can't connect to database! at /opt/otrs//Kernel/System/CustomerUser/DB.pm line 110.\n\t(in cleanup) 
[Thu Feb 10 11:26:57 2011] -e: Can't locate object method "EventHandlerTransaction" via package "Kernel::System::Ticket" at /opt/otrs//Kernel/System/Ticket.pm line 8325.\n

[cmadoery]

Hi keithf4

I do get exactly the same error message:
"Can't locate object method "EventHandlerTransaction" via package "Kernel::System::Ticket" at /opt/otrs//Kernel/System/Ticket.pm line 8325"
do you have any solution or did you get any answer to this?
I also restored my "backup" which I did a few hours ago. Still same error

Regards,
Christof

[keithf4]

No response yet. And just to add I do have the perl mysl library installed. I ran the otrs perl module check script again to be sure it recognized it. So I'm still at a loss here.

[cmadoery]

reason for this error in my case was the SMTPTLS.pm. I fixed it and apache is happy again.

[keithf4]

My SMTPS settings are fine. If I take this code out of my Config.pm file, OTRS runs perfect, but uses the default, internal customer DB. It sends over SMTPS and receives over POP3S with no issues. The first error in my log is actually the DB connect fail error. It's then followed by the SMTPS error, so I'm thinking something in the external DB connection code is just falling through to cause the SMTPS error as well.

[djw]

I know it's no help, but I have exactly the same problem.
I'm using freedtds from Debian Linux to access a MSSQL Customer DB.
OBDC is installed and working, I can run queries against the DB in Perl scripts.
When I try set up Config.pm to use the MSSQL rather than the default, I get this error.

[djw]

I tried setting the database type first to mysql (for the OTRS DB), then to mssql for the external dbm then back to mysql.
Now I can connect to the db, but still not get any data from it in OTRS.

Code: Select all

    $Self->{"Database::Type"} = 'mysql';

    $Self->{CustomerUser1} = {
		Database::Type => 'mssql',
        Name   => 'Customer Database Backend',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            DSN => 'dbi:ODBC:mydsn',
            SourceCharset => 'iso-8859',           
            SourceCharset => 'utf-8',           
            DestCharset => 'utf-8',           
            User => 'xx',
            Password => 'xxxxxx',
            Table => 'my-table',
            },
....
	};
#    $Self->{"Database::Type"} = 'mysql';

[djw]

External CustomerUser db will not work with MSSQL. The search for CustomerUsers throws an error:

Code: Select all

[unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared. (SQL-42000), SQL: 'SELECT AAS_ID , AAS_Vorname, AAS_Namen, AAS_Email FROM A_ANSPRECHPARTNER WHERE ((AAS_Vorname LIKE '%'  OR AAS_Namen LIKE '%'  OR AAS_Email LIKE '%' ) )  LIMIT 250'

mssql does not support the "LIMIT" syntax (it's "SELECT TOP 250 FROM ...").
The options I see are to recode the db access module to use correct (for mssql) statements, or create a workaround syncing the otrs db with the external backend.
On the other hand, otrs claims to run on mssql, doesn't it? So maybe there's a way round this?