Question 293875 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

Tags:

2

Answers


  1. 0

    Combining insert, select, and delete operations into a single SQL statement is typically done using the MERGE or UPSERT statement if supported by your database. However, if your current code has separate SQL strings, direct merging might be complex.

    Example of MERGE statement:

    foreach my $section (@$sectionsList) {
    my $fields = join(',', ('SNMP_ID', @{$sectionsMeta->{$section}->{field_arrayref}}));

    $sectionsMeta->{$section}->{sql_merge} = "
        MERGE INTO $section USING dual ON ($section.SNMP_ID = ? AND $section.ID = ?)
        WHEN MATCHED THEN UPDATE SET $fields = VALUES($fields)
        WHEN NOT MATCHED THEN INSERT ($fields) VALUES (?) WHEN NOT MATCHED BY SOURCE THEN DELETE;
    ";
}
    Login or Signup to reply.
  2. 0

    Show us an example of the DELETE and INSERT. Also please provide SHOW CREATE TABLE.

    This may suffice:

    • Ignore the DELETE
    • Change the INSERT by simply changing INSERT to REPLACE.

    Assuming all the columns are in the INSERT, then the REPLACE will perform 2 steps:

    1. DELETE the row(s) based on the UNIQUE (or PRIMARY) keys in the table.
    2. INSERT.

    Notes:

    • Probably there will be no issue with AUTO_INCREMENT columns.
    • An Upsert (IODKU) would be better than REPLACE, but that would be messier to implement.
    • If no row exists yet, REPLACE acts like INSERT. (This takes care of the case where there is no DELETE in the binlog.
    • (Flaw) A deliberate DELETE will be lost.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search