PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

[alanmels]

To replicate the problem, enable the Recurring Order sub-module, create very simple product without any additional attributes, place the product to cart, checkout and wait for the second recurring cycle, because the first one goes just ok. Beginning from the second recurring cycle, the log will start getting:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '192' for key 'PRIMARY': INSERT INTO {uc_recurring_orders} (original_order_id,renewal_order_id) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => 192 [:db_insert_placeholder_1] => 194 ) in backdrop_write_record() (line 8296 of /var/www/html/docroot/core/includes/common.inc).

Troubleshooting this issue took me to the uc_recurring_orders table that has this single entry:

Now these lines in uc_recurring.module:

    $recurring_order = new stdClass();
    $recurring_order->original_order_id = $fee->order_id;
    $recurring_order->renewal_order_id = $order->order_id;
    backdrop_write_record('uc_recurring_orders', $recurring_order);

cause the error, because the backdrop_write_record() is trying to insert the new record for the same existing original_order_id.

There is no need to create an object and try to write as simple db_update will do here:

db_update('uc_recurring_orders')->fields(array('renewal_order_id' => $order->order_id))
      ->condition('original_order_id', $fee->order_id)
      ->execute();

[alanmels]

Turns out assigning the original_order_id as primary in DB cause the issue, so reverting back makes it vanish at the same time bringing back the following issues, which probably need to be addressed separately:

1. Table entries without primary key are not possible to delete as phpMyAdmin confirms:

Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.

2. Even-though https://docs.backdropcms.org/api/backdrop/core%21includes%21common.inc/function/backdrop_write_record/1 states:

Saves (inserts or updates) a record to the database based upon the schema.

The records are never updated, and the new ones are created instead:

So probably either direct db_insert and db_update should be used or backdrop_write_record needs to be served with the third $primary_keys argument as it can help to update existing records and not insert new ones all the time:

$primary_keys: To indicate that this is a new record to be inserted, omit this argument. If this is an update, this argument specifies the primary keys' field names. If there is only 1 field in the key, you may pass in a string; if there are multiple fields in the key, pass in an array.