sqlmysqldatabaseperl

Need to convert An insert and a delete instruction in database into an update one


My issue is that we use Debezium to monitor the DB events and even when the is a simple update of the data present in the plugin table, the OCS inventory source code performs a delete and an insert operation instead of simply updating the field of the table.

Which means that instead of catching an update event, Debezium catches a creation and a deletion and not always in the correct order which leads us to data inconsistency.

I identified the pieces of code responsible of this defect, but I don't know how to modify them:

There are two Data.pm files that have different code for the same instruction.

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;
  }
  }

And 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';

I need a modification of those two parts, so that when there is an update, a simple SQL update instruction is performed. Without any delete or insert.

Please note that the Perl code of those two files is part of the associated packages, so modification is not that easy.

EDIT 1 : @RickJames The issue is with OCS. You can see clearly that they make an insert and then a delete instead of simply performing an update when the case is appropriate. That's why debezium is lost. It gets an insert, followed by a delete and this leads us to inconsistency data in our results.We opened a ticket with OCS and they told us that they don't want to improve the code for that purpose but we actually have a real need of an update statement instead of a total re-creation of the line in the table.

EDIT 2 : here are further explications OCS inventory is a solution that centralisez computers on which the OCS agent is installed. There is a Web Server writtten in PHP and another part (the Communication Server) using a lot of PERL code that is used to receive agents informmations and update the MYSQL database. We use debezium to monitor the binlogs of the database. And during agent Inventory the PERL module of the Communication Server performs systematically an insert and a delete aven when the field given by the OCS Inventory AGent could simply be updated. My need is to have an update case when data can simmply be updated, an insert when the data is totally new and a delete operation when the inventory of the OCS Agent returns the deletion of a line in the table

Can you explain why the INSERT and DELETE might be in the wrong order? If that could be fixed, the problem would go away. We don't know why it has been developed this way. And when we've asked for an update they told us that they didn't want to modify the existing code and that it had been developed this way for performances purposes


Solution

  • I found the solution by myself. In order to do what I want to do I need to use this :

    INSERT INTO table (column_names)  
    VALUES (data)  
    ON DUPLICATE KEY UPDATE
    

    We have seen the two UPSERT commands so far, but they had some limitations. The INSERT IGNORE statement only ignores the duplicate error without making any modification to the table. And the REPLACE method detected the INSERT error, but it will delete the row before adding the new record. Hence, we are still searching for a more refined solution until now.

    So, we use a more refined solution as the INSERT ON DUPLICATE KEY UPDATE statement. It is a non-destructive method that means it does not remove the duplicate row. Instead, when we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.