OCS Inventory数据库操作优化请求：替换删插逻辑为更新语句以解决Debezium数据一致性问题

Hey folks, let's break down this tricky issue we're facing with OCS Inventory and Debezium, then walk through how to resolve it.

The Core Problem #

We're using Debezium to monitor database changes from OCS Inventory, and we hit a critical data consistency snag: whenever we need to update simple data in plugin tables, OCS doesn't run a straightforward UPDATE query. Instead, it deletes the existing record and inserts a new one.

This completely messes up Debezium's change capture—instead of catching a single update event, we get a DELETE followed by an INSERT... but often these events arrive out of order. The end result? Hard-to-trace data inconsistencies that break our downstream systems.

Where the Issue Resides #

We traced the problematic logic to two Perl files in OCS's core codebase:

1. Snmp/Data.pm #

# Build the "DBI->prepare" sql insert string
$fields_string = join ',', ('SNMP_ID', @{$sectionsMeta->{$section}->{field_arrayref}});
$sectionsMeta->{$section}->{sql_insert_string} = "INSERT INTO $section($fields_string) VALUES(";
for(0..@{$sectionsMeta->{$section}->{field_arrayref}}){
 push @bind_num, '?';
}
$sectionsMeta->{$section}->{sql_insert_string}.= (join ',', @bind_num).')';
@bind_num = ();
# Build the "DBI->prepare" sql select string
$sectionsMeta->{$section}->{sql_select_string} = "SELECT ID,$fields_string FROM $section WHERE SNMP_ID=? ORDER BY ".$DATA_MAP{$section}->{sortBy};
# Build the "DBI->prepare" sql deletion string
$sectionsMeta->{$section}->{sql_delete_string} = "DELETE FROM $section WHERE SNMP_ID=? AND ID=?";
# to avoid many "keys"
push @$sectionsList, $section;
}
}

2. Inventory/Data.pm #

# Build the "DBI->prepare" sql insert string
for (@{$sectionsMeta->{$section}->{field_arrayref}}) {
 s/^(.*)$/\`$1\`/;
}
$fields_string = join ',', ('`HARDWARE_ID`', @{$sectionsMeta->{$section}->{field_arrayref}});
$sectionsMeta->{$section}->{sql_insert_string} = "INSERT INTO $section($fields_string) VALUES(";
for(0..@{$sectionsMeta->{$section}->{field_arrayref}}){
 push @bind_num, '?';
}
$sectionsMeta->{$section}->{sql_insert_string}.= (join ',', @bind_num).')';
@bind_num = ();
# Build the "DBI->prepare" sql select string
$sectionsMeta->{$section}->{sql_select_string} = "SELECT ID,$fields_string FROM $section WHERE HARDWARE_ID=? ORDER BY ".$DATA_MAP{$section}->{sortBy};
# Build the "DBI->prepare" sql deletion string
$sectionsMeta->{$section}->{sql_delete_string} = "DELETE FROM $section WHERE HARDWARE_ID=? AND ID=?";
# to avoid many "keys"
push @$sectionsList, $section;
}
#Special treatment for hardware section
$sectionsMeta->{'hardware'} = &_get_hardware_fields;
push @$sectionsList, 'hardware';

Our Requirements & Constraints #

Our goal is to rewrite this logic so that:

• We run an INSERT only when the record is brand new
• We run an UPDATE when the record exists and needs changes
• We only run DELETE when the OCS Agent explicitly reports the record should be removed

A quick heads-up: OCS's official support told us they won't fix this because of performance concerns, so we're on our own with a custom patch. Also, these Perl files are part of OCS's core package, so we need to be careful not to break existing Agent communication or database integrity.

Why the Events Are Out of Order #

Before diving into fixes, let's explain why the delete/insert events are getting mixed up:

• OCS might execute the DELETE and INSERT in separate transactions, or even in a single transaction but Debezium processes them as individual events
• Depending on database replication lag or Debezium's polling interval, the INSERT can be captured before the DELETE, leading to stale records popping up temporarily before being deleted

Step-by-Step Fix #

1. Add UPDATE Query Preparation #

First, we need to add code to build an UPDATE SQL string in both files, right after the existing select/delete query setup.

For Snmp/Data.pm:

Add this snippet after the sql_delete_string block:

# Build the "DBI->prepare" sql update string
my @update_fields = map { "$_=?" } @{$sectionsMeta->{$section}->{field_arrayref}};
my $update_string = join ',', @update_fields;
$sectionsMeta->{$section}->{sql_update_string} = "UPDATE $section SET $update_string WHERE SNMP_ID=? AND ID=?";

For Inventory/Data.pm:

Add this snippet after the sql_delete_string block:

# Build the "DBI->prepare" sql update string
my @update_fields = map { "`$_`=?" } @{$sectionsMeta->{$section}->{field_arrayref}};
my $update_string = join ',', @update_fields;
$sectionsMeta->{$section}->{sql_update_string} = "UPDATE $section SET $update_string WHERE `HARDWARE_ID`=? AND ID=?";

2. Rewrite the Data Handling Flow #

Next, find the part of the code where OCS checks for existing records (using the sql_select_string query). Replace the current delete-then-insert logic with this:

1. Run the select query to check if the record exists
2. If it does: execute the sql_update_string with the new data
3. If it doesn't: execute the sql_insert_string
4. Only run sql_delete_string when the Agent says the record is no longer present

3. Enforce Transaction Consistency #

Wrap all these operations (update/insert/delete) in a single database transaction. This ensures Debezium captures events in the correct order, and if anything fails, all changes are rolled back to avoid partial updates.

Quick Context on OCS Inventory #

For anyone new to the tool: OCS Inventory is a centralized device management solution with a PHP web frontend and Perl-based communication servers that handle data sent from OCS Agents and update the MySQL database. Make sure to test your modified code thoroughly in a staging environment before deploying to production.

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