Cannot update. Getting Mysql error

While using /update.php from the UI, one update is able to be run, another is not:

entity_legal module
Update #9002
Failed: Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key: ALTER TABLE "entity_legal_document_version" DROP PRIMARY KEY; Array ( ) in entity_legal_update_9002() (line 86 of /var/aegir/platforms/drupal/9/distro/1-dev/custom/cci-social-11.9.x-ae/html/modules/contrib/entity_legal/entity_legal.install).

I'm also getting this error.

Not really sure what to do about it.
Will search some more for a solution.

I have the same error

> [notice] Update started: entity_legal_update_9002
> [error] SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key: ALTER TABLE "entity_legal_document_version" DROP PRIMARY KEY; Array

on a test site, where I had entity_legal 3 enabled, I had to delete the legal docs and acceptance which would be BAD on a live site.
Then I uninstalled.

Then I upgraded and re-installed. It worked well.

This flow does not work for entity_legal on active sites.

That is indeed an absolutely unacceptable upgrade path…

I haven’t had the time to delve into this issue yet because I happened to notice our case wasn’t using this module (no legal documents were ever created) so I simply uninstalled the module to move the project along.

I do think this is an absolutely blocking issue though, I’ll try to take a second look next time I find the time.

I don't have this error with 4.0-alpha1 but face a similar issue with the same update hook:

>  [notice] Update started: entity_legal_update_9002
>  [error]  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1677569101' for key 'PRIMARY': UPDATE "entity_legal_document_version" SET vid = CAST(SUBSTRING_INDEX(name, '_', -1) AS UNSIGNED); Array

Turned out that we have several document versions with the same timestamp prefix because they were created programmatically at the same time by a script. We have to add a custom update hook running before 9002 to solve the issue.

function osp_core_update_dependencies() : array {
  $dependencies = [];
  $dependencies['entity_legal'][9002] = ['mymodule' => 9001];
  return $dependencies;
}

function mymodule_update_9001() : void {
  // The update hook 9002 from entity_legal adds an incremental ID column to
  // the entity_legal_document_version table. It casts the last substring after
  // the _ in the name column into int. That substring was generated using the
  // current timestamp when the document version was create. However, there are
  // several entries with same timestamp as they were generated at the same time.
  // We have to change that timestamp so that entity_legal_update_9002() does
  // not have the integrity constraint violation error.
  // @see entity_legal_update_9002().
  $versions = [];
  $database = \Drupal::database();
  $query = $database->select('entity_legal_document_version', 'v');
  $query->addExpression("SUBSTRING_INDEX(name, '_', -1)", 'timestamp');
  $query->addExpression("GROUP_CONCAT(name)", 'names');
  $query->groupBy('timestamp')
    ->having('COUNT(timestamp) > 1');
  $results = $query->execute()->fetchAll();
  foreach ($results as $result) {
    $timestamp = (int) $result->timestamp;
    $names = explode(',', $result->names);
    foreach ($names as $delta => $name) {
      $new_name = strrev($name);
      $new_name = preg_replace(strrev('/_' . $timestamp . '/'), strrev('_' . ($timestamp + $delta)), $new_name, 1);
      $new_name = strrev($new_name);
      $versions[$name] = $new_name;
    }
  }

  if (empty($versions)) {
    return;
  }

  // Gather the tables to update the document names.
  $tables = [
    'entity_legal_document_version' => ['name'],
    'entity_legal_document_version_data' => ['name'],
    'entity_legal_document_acceptance' => ['document_version_name'],
  ];
  // Additional tables for fields.
  $key_value = \Drupal::keyValue('entity.storage_schema.sql');
  $entity_type = 'entity_legal_document_version';
  $field_name = 'entity_legal_document_text';
  $storage_schema = $key_value->get($entity_type . '.field_schema_data.' . $field_name);
  foreach ($storage_schema as $table => $schema_data) {
    if ($database->schema()->tableExists($table)) {
      $tables[$table] = ['entity_id', 'revision_id'];
    }
  }

  // Update document_version name to new name.
  foreach ($tables as $table => $columns) {
    foreach ($versions as $name => $new_name) {
      $query = $database->update($table);
      $fields = [];
      foreach ($columns as $column) {
        $fields[$column] = $new_name;
        $query->condition($column, $name);
      }
      $query->fields($fields);
      $query->execute();
    }
  }

}

After updating to Entity Legal 4.0.0-alpha1 in Drupal 9.5.10 and I try updating database using Drush I get the following message and update is aborted,

>  [notice] Update started: entity_legal_update_9002
>  [error]  SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key: ALTER TABLE "entity_legal_document_version" DROP PRIMARY KEY; Array
> (
> )
>
>  [error]  Update failed: entity_legal_update_9002
>  [notice] Batch process has consumed in excess of 60% of available memory. Starting new thread
 [error]  Update aborted by: entity_legal_update_9002
 [error]  Finished performing updates.

This issue is critical. I looked at the 404 page requests for one of my sites and see it reporting the terms and conditions page. I checked in this module and found it reporting no versions exists and that I should create a default version. Apparently the page just got missing some days now just about 6 days ago when I updated the module to the latest version.

When I tried re-creating the document I got the error:

Drupal\Core\Entity\EntityStorageException: SQLSTATE[HY000]: General error: 1364 Field 'name' doesn't have a default value: INSERT INTO "entity_legal_document_version" ("document_name", "uuid", "langcode") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2); Array ( [:db_insert_placeholder_0] => terms_and_conditions [:db_insert_placeholder_1] => 6619a27f-7acb-40ac-9804-341578513e72 [:db_insert_placeholder_2] => en ) in Drupal\Core\Entity\Sql\SqlContentEntityStorage->save() (line 815 of /home/public_html/core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

I tried rolling back to entity_legal 3.0.0-beta5 and it broke the site.

My path of the module update.
The main issues arise from trying to update a module by jumping multiple versions.

Before running of the update need to see if was performed this hook
It includes code that makes the object ID unique, allowing you to perform the following updates for version 4.0.0-alpha1

You may also have a problem with the same timestamp under multiple documents.
The solution to this issue is described in this comment 🐛 Cannot update. Getting Mysql error Active Thanks man!

You may also have a question that is described here 🐛 Length of menu_tree.url and menu_tree.route_param_key are too short (255 characters) Needs review .
It occurs due to the use of a timestamp in the entity identifier.

The reason for the original question of this issue is described here
The easiest solution to this problem is to use a database dump that was made before running the update.

The best approach seems to be to upgrade to latest 3.x version and then upgrade to version 4.x.

During entity_legal_update_9002 I've got error The field has already been deleted and it is in the process of being purged. and update failed.
What helped was catching the exception.

Yes, the upgrade to 4.0.x is painful, sorry for that. I did my best to provide an update path even Drupal has no API to tackle such a case (changing the primary key from string to integer).

What can be done?

• While still in 3.0x, make sure you've applied all versions until the latest from 3.0.x
• You might consider exporting data when still on 3.0.x, then reimporting in 4.0.x

Re #7

Turned out that we have several document versions with the same timestamp suffix because they were created programmatically at the same time by a script. We have to add a custom update hook running before 9002 to solve the issue.

I was thinking that this might be possible, but 2 different documents with the same timestamp? It would be nice if we can get a patch from #7 to improve the update path

I can feel the pain but, don't forget, 4.0.x is still in alpha. That means you're using on your own risk. Everybody is welcomed to provide patches so we can make it more stable.

@claudiu.cristea For #7, it was an issue specifically on our system as we used a script (deploy hook) to generate document versions. The hook in #7 was tailored for our data so I couldn't provide a patch for entity_legal as I wasn't sure about the data on other systems. I think it's acceptable for site maintainers to check the data and apply the hook at their discretion.

I understand the frustration but that change it had to be performed and it wasn't easy. I hope everyone managed to finally fix their sites. Can we close this?