[solved] Kundendatenbank - Unknown column 'uid'

Hilfe zu OTRS Problemen aller Art
Post Reply
steste123
Znuny wizard
Posts: 554
Joined: 25 Sep 2012, 19:04
Znuny Version: OTRS Help Desk
Real Name: Stefan

[solved] Kundendatenbank - Unknown column 'uid'

Post by steste123 »

Hallo zusammen,
wir nutzen eine ext. Kundenbenutzer- und Kundendatenbank. Bisher auch eigt ohne Fehler.
Nun ist mir aber aufgefallen, dass im Log öfter mal folgende Fehlermeldung auffällt:

Code: Select all

Unknown column 'uid' in 'where clause', SQL: 'SELECT uemail , ufirstname, ulastname, uemail FROM users WHERE LOWER(uid) = LOWER(?) AND valid_id IN (1) LIMIT 250'
Dateiauszug:
/Kernel/Config/Files/ZZZCustomerUserDB1.pm

Code: Select all

#########################
#########################
#########################
# CustomerUser (customer database backend and settings)
$Self->{CustomerUser1} = {
    Name => 'externe_Datenbank',
    Module => 'Kernel::System::CustomerUser::DB',
    Params => {

DSN => 'DBI:mysql:database=XYZ;host=ABC',
User => 'USER',
Password => 'PASS',
            Table => 'users',
            SourceCharset => 'iso-8859-1',
            DestCharset => 'utf-8',
            CaseSensitive => 0,
            ForeignDB => 1, # <-- THIS IS IMPORTANT WHEN YOU USE EXTERNAL DB WITHOUT create_$
        },
#customer unique id
CustomerKey => 'uemail',

# customer #
CustomerID => 'uid',
CustomerValid => 'valid_id',

    CustomerUserListFields => ['ufirstname', 'ulastname', 'uemail'],
    CustomerUserSearchFields => ['ufirstname', 'ulastname', 'partner_number', 'uemail'],
    CustomerUserSearchPrefix =>  '*',
    CustomerUserSearchSuffix => '*',
    CustomerUserSearchListLimit => 250,
    CustomerUserPostMasterSearchFields => ['uemail'],
    CustomerUserNameFields => ['ufirstname','ulastname'],
    CustomerUserEmailUniqCheck => 1,
#    # show not own tickets in customer panel, CompanyTickets
    CustomerUserExcludePrimaryCustomerID => 0,
#    # generate auto uemails
#    AutouemailCreation => 0,
#    AutouemailCreationPrefix => 'auto',
#    # admin can change customer preferences
#    AdminSetPreferences => 1,
#    # cache time to live in sec. - cache any database queries
    CacheTTL => 0,
#    # just a read only source
#    ReadOnly => 1,
    Map => [
        # note: uemail, uemail and CustomerID needed!
        # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link$
#       [ 'create_time',    'Created',    'activation_date', 2, 0, 'var', '', 0],
        [ 'UserTitle',      'Title',      'ugender',      1, 0, 'var', '', 0 ],
        [ 'UserFirstname',  'Firstname',  'ufirstname', 1, 1, 'var', '', 0 ],
        [ 'UserLastname',   'Lastname',   'ulastname',  1, 1, 'var', '', 0 ],
        [ 'UserLogin',      'Username',   'uemail',      1, 1, 'var', '', 0 ],
        [ 'UserPassword',   'Password',   'upass',         0, 0, 'var', '', 0 ],
        [ 'UserEmail',      'Email',      'uemail',      1, 1, 'var', '', 0 ],

#        [ 'Useruemail',      'uemail', 'uemail',           1, 1, 'var', '$Env{"CGIHandle"}?$
        [ 'UserCustomerID', 'CustomerID', 'partner_number', 0, 1, 'var', '', 0 ],

#        [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var', '', 0 ],
        [ 'UserPhone',        'Phone',       'uphone',        1, 0, 'var', '', 0 ],
  #      [ 'UserFax',          'Fax',         'fax',          1, 0, 'var', '', 0 ],
   #     [ 'UserMobile',       'Mobile',      'mobile',       1, 0, 'var', '', 0 ],
        [ 'UserStreet',       'Street',      'ucompany_street',       1, 0, 'var', '', 0 ],
        [ 'UserZip',          'Zip',         'ucompany_zip',          1, 0, 'var', '', 0 ],
        [ 'UserCity',         'City',        'ucompany_city',         1, 0, 'var', '', 0 ],
        [ 'UserCountry',      'Country',     'user_cntr',      1, 0, 'var', '', 0 ],
 #       [ 'UserComment',      'Comment',     'comments',     1, 0, 'var', '', 0 ],
        [ 'ValidID',          'Valid',       'valid_id',     0, 1, 'int', '', 0 ],
    ],
    # default selections
    Selections => {
        UserTitle => {
            'male' => 'Mr.',
            'female' => 'Mrs.',
        },
    },
};




Last edited by steste123 on 11 Jan 2018, 15:35, edited 1 time in total.
OTRS 5.x - Debian 8 - apache 2.4 - MySQL 5.x
steste123
Znuny wizard
Posts: 554
Joined: 25 Sep 2012, 19:04
Znuny Version: OTRS Help Desk
Real Name: Stefan

Re: Kundendatenbank - Unknown column 'uid'

Post by steste123 »

Jem. eine Idee?
OTRS 5.x - Debian 8 - apache 2.4 - MySQL 5.x
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Kundendatenbank - Unknown column 'uid'

Post by root »

Hi,

klar. Für mich hat das mit

Code: Select all

CustomerID => 'uid',
und

Code: Select all

[ 'UserCustomerID', 'CustomerID', 'partner_number', 0, 1, 'var', '', 0 ],
zu tun. Wahrscheinlich sollte das statt uid partner_number sein. Aber dafür kenne ich die Daten nicht genau.

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
steste123
Znuny wizard
Posts: 554
Joined: 25 Sep 2012, 19:04
Znuny Version: OTRS Help Desk
Real Name: Stefan

Re: Kundendatenbank - Unknown column 'uid'

Post by steste123 »

Hi Roy,
bedeutet es dass UserCustomerID und CustomerID den gleichen Wert enthalten müsste?

Datenaufbau ist wie folgt:

UserID (id in datenbank); ...........;partner_number
1;.......;123456
2;.......;126212
3;.......;123456
OTRS 5.x - Debian 8 - apache 2.4 - MySQL 5.x
root
Administrator
Posts: 3934
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

Re: Kundendatenbank - Unknown column 'uid'

Post by root »

Hallo,

also UserCustomerID <> CustomerID. Die zweite Spalte des Mappings ist hier zu vergleichen. Die erste Spalte sind nur die Bezeichungen wenn Du werte als Platzhalter nimmst: also <OTRS_Customer_UserFirstname> z.B.

CustomerID ist die Kundennummer, du legst einmal fest welches Feld in der Datenquelle das ist und dann im Mapping welchen Namen es als Platzhalter bekommt UserCustomerID

- Roy
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
steste123
Znuny wizard
Posts: 554
Joined: 25 Sep 2012, 19:04
Znuny Version: OTRS Help Desk
Real Name: Stefan

Re: Kundendatenbank - Unknown column 'uid'

Post by steste123 »

Danke fürs aufklären. Fehler behoben.
OTRS 5.x - Debian 8 - apache 2.4 - MySQL 5.x
Post Reply