Integrity constraint violation: 1062 Duplicate entry

Hello @balis_m,

I've tried to create several users from the custom registration page provisioned by this module (In the incognito window, by anonymous user). The multiple_registration table in DB is also was not empty.
Each time, during the debug session I saw a new (unique) value for a user which I want to create (It is related to the place from the code mentioned by you). No errors happen during new user creation in my local environment.

Testing environment: Drupal 9.3.3, multiple_registration 3.x-dev with already applied patch - #3264649: Can not set values on immutable configuration multiple_registration.access_settings_page_form_config:multiple_registration_pages_allowed_list

We need more people to test the problem.

Also, please take a look at this comment:

https://www.drupal.org/project/multiple_registration/issues/3261719#comm...

Maybe it will resolve your issue without patching the module

I will describe my experience with this issue:

I was using v3.0.2 with Drupal 9.3.5. When I tried to update to 3.1.0, running the update script (specifically multiple_registration_update_8204() function) failed due to the integrity constrained violation. Checking my multiple_registration table, I realized that there were several multiple entries for the same uids and as result it was not possible to set uid as a primary key. I deleted the surplus entries and running the update was successful. However, when a user tries to register now, it seems that two identical entries are saved in the table and the second one causes the error. Applying @balis_m solution results in just one entry in the table and no errors. I was not able to find which part of the remaining code saves this entry in the table. I should mention that some users are registering via the SAMLAuth module (for their role i have created a registration form that is hidden) and some other users from their role-specific registration form. However, I don't believe this detail is relevant to the problem.

I have the same issue during db update.

Same issue here

Same here multiple_registration_update_8204 fails because there are lots of duplicates in the db so the primary key can't be added... probably needs some code to de-dupliate before it runs that... or maybe uid could just be a key and have a separate pk?

I was just trying to do a mysql de-duplicate but without a primary key I couldn't figure out how to do it, so this script de-duplicates everything in php, then truncates the multiple_registrations table and inserts the clean data... back up the table first since if it goes wrong you could easily wipe your data. But in case anyone else needs this then heres the code. I put it into a drush command.

<?php
$database = \Drupal::database();
    $query = $database->select('multiple_registration', 'mr');
    $query->addField('mr', 'uid');
    $query->addField('mr', 'rid');
    $query->orderBy('mr.uid');
    $result = $query->execute();
    $clean = [];
    if ($result) {
      while ($row = $result->fetchAssoc()) {
        $clean[$row['uid']] = [
          'uid' => $row['uid'],
          'rid' => $row['rid'],
        ];
      }
    }
    if (count($clean)) {
      $database->truncate('multiple_registration')->execute();
      $query = $database->insert('multiple_registration')->fields(['uid', 'rid']);
      foreach ($clean as $record) {
        $query->values($record);
      }
      $query->execute();
    }
?>

After de duplicating the table I tried registering and got a WSD

Merge request !4 solved it

Same issue here. Drupal core 9.3.6 . Multiple Registration 3.1.0 with patch https://www.drupal.org/project/multiple_registration/issues/3264649

Applied merge request !4 and issue seems resolved.

I also encountered this issue, I fixed it on my side with the use of an upsert instead of an insert.

/**
 * Form submit handler to store the correspondent rid for the inserted user.
 */
function _multiple_registration_rid(array $form, FormStateInterface $form_state) {
  $rid = $form_state->getValue('multiple_registration_rid');
  $uid = $form_state->getValue('uid');
  $config = \Drupal::config('multiple_registration.create_registration_page_form_config');

  if ($rid && $uid) {
    $connection = Database::getConnection();
    $connection->upsert('multiple_registration')
      ->fields([
          'uid' => $uid,
          'rid' => $rid,
        ]
      )
      ->key('uid')
      ->execute();
  }

  // Redirect users to custom path.
  if ($redirectPath = $config->get('multiple_registration_redirect_path_' . $rid)) {
    $form_state->setRedirectUrl(Url::fromUserInput($redirectPath));
  }
}

What do you think ? I don't know if completely removing this code block like in the merge request won't create any other issue.

Patch attached if needed.

Applied above patch 14 and it worked for me. Running Drupal 9.3.7 and version 3.1.1 of this module and have still been getting this error every time I create a user via a multiple registration page. This patch is the only thing that has worked so far. I haven't seen any adverse effects, but haven't tested in depth yet.

the patch 14
didn't help me, unfortunately. Still have:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '8' for key 'PRIMARY': ALTER TABLE "multiple_registration" ADD PRIMARY KEY (`uid`); Array

@osab have you tried running sphism's code to remove the duplicate entries in your multiple_registration table? Works for me after running this.

Ive deployed it out through a few environments with an update hook like so:

/**
* Delete duplicate rows from multiple_registration table.
*/
function mymodule_update_8001() {
$database = \Drupal::database();
$query = $database->select('multiple_registration', 'mr');
$query->addField('mr', 'uid');
$query->addField('mr', 'rid');
$query->orderBy('mr.uid');
$result = $query->execute();
$clean = [];
if ($result) {
while ($row = $result->fetchAssoc()) {
$clean[$row['uid']] = [
'uid' => $row['uid'],
'rid' => $row['rid'],
];
}
}
if (count($clean)) {
$database->truncate('multiple_registration')->execute();
$query = $database->insert('multiple_registration')->fields(['uid', 'rid']);
foreach ($clean as $record) {
$query->values($record);
}
$query->execute();
}
}

I'm also still suffering from this. Could you please explain how you used an update hook to run the code in #17. I've tried the patch from #14.
Thanks

>  [notice] Update started: multiple_registration_update_8204
>  [error]  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2945' for key 'PRIMARY': ALTER TABLE "multiple_registration" ADD PRIMARY KEY (`uid`); Array
> (
> )

Hi @Hitby

You could execute the following command to run the database cleaning :

PHP=`cat <<'EOF'
$database = \Drupal::database();$query = $database->select('multiple_registration', 'mr');$query->addField('mr', 'uid');$query->addField('mr', 'rid');$query->orderBy('mr.uid');$result = $query->execute();$clean = [];if ($result) {while ($row = $result->fetchAssoc()) {$clean[$row['uid']] = ['uid' => $row['uid'],'rid' => $row['rid'],];}}if (count($clean)) {$database->truncate('multiple_registration')->execute();$query = $database->insert('multiple_registration')->fields(['uid', 'rid']);foreach ($clean as $record) {$query->values($record);}$query->execute();}
EOF`; ./vendor/bin/drush php:eval "$PHP"

I just copied the code from https://www.drupal.org/project/multiple_registration/issues/3264902#comm..., please use with caution and make backups if necessary.

Or add a custom update hook in a module like shown in https://www.drupal.org/project/multiple_registration/issues/3264902#comm...

Great thank you Renrhaf, that's fixed the issue!

Very much appreciated.

@astoker88 thank you, I'll check again later. For now its hard to find time because of awful and brutal war Russia against our country Ukraine ((. I've just freeze version on 3.0.2 for a while.

I had the same problem with updating the module to 3.1.0. The solution proposed by @astoker88 worked perfectly for me. Thanks!

I had the same problem, with D9.3.12 and patch #14 worked for me. Thank you

Can you commit the patch in #14?

#14 works like a charm, thx a lot !

patch #14 works very well.

I had also this issue after creating a user with uid=18 for example :

Drupal\Core\Database\IntegrityConstraintViolationException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '18' for key 'PRIMARY': INSERT INTO "multiple_registration" ("uid", "rid") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => 18 [:db_insert_placeholder_1] => organization ) in Drupal\mysql\Driver\Database\mysql\ExceptionHandler->handleExecutionException() (line 50 of core/modules/mysql/src/Driver/Database/mysql/ExceptionHandler.php).

Using 9.4.1, I also got the error posted on #26.

Drupal\Core\Database\IntegrityConstraintViolationException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '6' for key 'PRIMARY': INSERT INTO "multiple_registration" ("uid", "rid") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => 6 [:db_insert_placeholder_1] => estate_agent ) in Drupal\mysql\Driver\Database\mysql\ExceptionHandler->handleExecutionException() (line 50 of /core/modules/mysql/src/Driver/Database/mysql/ExceptionHandler.php).

Likewise, patch #14 solved the issue.

Patch #14 works for me using Drupal 9.4.6 and multiple_registration 3.1.1
Thanks

Moving to RTBC as it seems OK for other reviewers too.

Hello everyone.

Thanks for your solutions and review.

The #14 was committed now to the 3.x branch. The new module release will be published soon. Have a good day.

Patch #14 works for me..

Extra confirmation that patch #14 is effective

Patch #14 worked for me.

i couldn't find this issue referenced in any of the recent releases notes, which release fixed this issue?

i couldn't find this issue referenced in any of the recent releases notes, which release fixed this issue?

@munchmunch it was fixed in 3.2.3