OTRS 5中Perl脚本更新CustomerUser触发外键约束错误

Let's break down exactly what's happening here and how to fix it:

Root Cause #

The foreign key error you're seeing is because you're passing the wrong value for the UserID parameter in CustomerUserUpdate.

This parameter doesn't refer to the customer user's ID (from the customer_user table)—it requires a valid ID from OTRS's internal users table (these are your agent/administrator accounts). The change_by field in customer_user is tied to users.id via a foreign key, so when you pass UserID => 4 (which is the ID of a customer user, not an agent), MySQL rejects the update because that ID doesn't exist in the users table.

Looking at your customer_user database record, you can see create_by => 1 and change_by => 2—these are valid agent IDs that already exist in your users table. You should use one of these (or another valid agent ID) instead of 4.

Fixed Code #

Replace the UserID value with a valid agent ID from your users table (e.g., 1, which is typically the default admin account root@localhost):

my $test = $CustomerUserObject->CustomerUserUpdate(
    ID => 'admin', # Current customer user's login (correct)
    UserCustomerID => 'acmecompany',
    UserLogin => 'newuser', # New customer user login
    UserFirstname => 'myfirst',
    UserLastname => 'mylast',
    UserPassword => '', # Not required
    UserEmail => 'whatever@gmail.com',
    ValidID => 1,
    UserID => 1, # Use a valid agent ID from the `users` table here
);

Verification Steps #

1. Confirm valid agent IDs: Run this SQL query against your OTRS database to list all valid agent accounts and their IDs:

   SELECT id, login, first_name, last_name FROM users;
   

   Pick any ID from the results to use as your UserID parameter.

2. Validate agent permissions: Ensure the agent you're using has permission to modify customer users (default admin accounts will have this by default, but if you're using a custom agent, double-check their role permissions in the OTRS admin interface).

3. Avoid parameter confusion: It's easy to mix up customer user IDs and agent user IDs in OTRS—remember:

   • ID in CustomerUserUpdate = the customer user's current login (not their database ID)
   • UserID = the agent's database ID (from the users table)

Extra Tip #

If you want to programmatically verify an agent ID is valid before using it, you can use the Kernel::System::User module:

my $UserObject = $Kernel::OM->Get('Kernel::System::User');
my %AgentData = $UserObject->GetUserData(UserID => 1);

if (%AgentData) {
    print "Valid agent ID: $AgentData{login}\n";
}
else {
    die "Invalid agent ID!\n";
}

内容的提问来源于stack exchange，提问作者pm1391