Drupal 7 to 9 acl_user migration error on incremental or update

Created on 11 February 2022, almost 3 years ago
Updated 27 July 2023, over 1 year ago

Problem/Motivation

I'm upgrading a Drupal 7 site to Drupal 9 using the Migrate Tools module. Using drush commands, I was able to migrate the acl_node and acl_user data the first time around.

I need to sync the latest data from the Drupal 7 site and so I tried to perform an incremental update on the acl_node and acl_user migrations. acl_node did not have a problem but acl_user errored out.

Steps to reproduce

Install:
Drupal Core 9.3.5
ACL 8.x-1.x-dev
Migrate Tools 8.x-5.1
Drush 10.6.2
PHP 7.4.27
MySQL 8.0.27

Generate your migrations using drush. In my case I did
drush migrate-upgrade --legacy-db-url=mysql://username:password@127.0.0.1/databasename --legacy-root=/Users/username/repos/drupal7site/public --configure-only

Then run the migration to import acl_user
drush mim upgrade_d6_d7_acl_user

No error and the data is migrated.

Now make updates to Drupal 7 site and then attempt to migrate to Drupal 9 again

drush mim upgrade_d6_d7_acl_user --update

Drush generates the following error for each row that it attempts to process. With a different acl_id and uid referenced in each error of course.

 [error]  SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ("acl_id" = '4') AND ("uid" = '25370')' at line 2: UPDATE "acl_user" SET
WHERE ("acl_id" = :db_condition_placeholder_0) AND ("uid" = :db_condition_placeholder_1); Array
(
    [:db_condition_placeholder_0] => 4
    [:db_condition_placeholder_1] => 25370
)
 (/Users/username/repos/drupal9site/web/modules/contrib/acl/src/Plugin/migrate/destination/AclTable.php:73)

Proposed resolution

As seen from the error message above, the mysql update SET is missing.

Looking at the code in /src/Plugin/migrate/destination/AclTable.php, an empty $destination array is being passed into the database merge. I don't know what the reason for unsetting it is but this appears to be the problem when the Merge attempts to Update instead of Insert.

public function import(Row $row, array $old_destination_id_values = array()) {
    $destination = $row->getDestination();

    $keys = array();
    foreach(array_keys($this->ids) as $id) {
      $keys[$id] = $destination[$id];
      unset($destination[$id]);
    }

    \Drupal::database()->merge($this->table_name)
      ->keys($keys)
      ->fields($destination)
      ->execute();

    $return = array_map(function($key) use ($row) {
      return $row->getDestinationProperty($key);
    }, $keys);

    return $return;
  }

Removing unset($destination[$id]); fixes migration updates.

Remaining tasks

User interface changes

API changes

Data model changes

πŸ› Bug report
Status

Needs review

Version

1.0

Component

Code

Created by

πŸ‡ΊπŸ‡ΈUnited States retiredpro

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • πŸ‡¨πŸ‡­Switzerland salvis
  • πŸ‡¨πŸ‡­Switzerland salvis

    I wonder whether removing the unset() fixes the Update but breaks the Insert...

  • @salvis, as I read the issue description, I had the same instinct as you in #4. Does the import() API provides the action type (update v.s. insert)? It doesn't look like it to me. Or maybe one of the internal properties has the action type. I will have to take a closer look tomorrow.

    In any case, I don't have a D7 environment or a setup that allows me to test this easily.

  • Hey @salvis. I am not familiar with the migration API. The best I can think of is the following:

      /**
       * {@inheritdoc}
       */
      public function import(Row $row, array $old_destination_id_values = []) {
        $destination = $row->getDestination();
        $needsUpdate = $row->needsUpdate();
    
        $keys = [];
        foreach (array_keys($this->ids) as $id) {
          $keys[$id] = $destination[$id];
          if (!$needsUpdate) {
            unset($destination[$id]);
          }
        }
    
        \Drupal::database()->merge($this->tableName)
          ->keys($keys)
          ->fields($destination)
          ->execute();
    
        return array_map(function ($key) use ($row) {
          return $row->getDestinationProperty($key);
        }, $keys);
      }
    

    I'm not entirely sure that $row->needsUpdate() actually does what It sounds like here (identify whether $row represents and update or an insert).

    On an unrelated note, the return statement seems odd (its cleaned up above).

    But, the more I look at this, the more I am also curious what the heck the unset() is all about. The database merge() function automatically handles whether to update or insert ( example β†’ ). I don't really see why we would need to be removing key/value pairs from $destination. That said, I'm not very familiar with this API, so I could be missing something.

    The has me wondering... is this an issue with updates in general, or only into the context of migrating to D9. I'm guessing the former, as there doesn't appear to be any ACL migration code specifically relevant to D9.

    I see we have migration tests, but I cannot easily determine if the use case described in this issue is covered by the test suite. If not, do you have a system for testing this type of D7->D9 workflow?

  • Open in Jenkins β†’ Open on Drupal.org β†’
    Core: 10.0.7 + Environment: PHP 8.1 & MariaDB 10.3.22
    last update over 1 year ago
    Patch Failed to Apply
  • Open in Jenkins β†’ Open on Drupal.org β†’
    Core: 9.5.x + Environment: PHP 8.1 & MySQL 5.7
    last update over 1 year ago
    Patch Failed to Apply
  • Open in Jenkins β†’ Open on Drupal.org β†’
    Core: 9.5.x + Environment: PHP 8.1 & MySQL 8
    last update over 1 year ago
    Patch Failed to Apply
  • Open in Jenkins β†’ Open on Drupal.org β†’
    Core: 10.0.7 + Environment: PHP 8.1 & MariaDB 10.3.22
    last update over 1 year ago
    5 pass
  • Open in Jenkins β†’ Open on Drupal.org β†’
    Core: 9.5.x + Environment: PHP 8.1 & MySQL 5.7
    last update over 1 year ago
    5 pass
  • Open in Jenkins β†’ Open on Drupal.org β†’
    Core: 9.5.x + Environment: PHP 8.1 & MySQL 8
    last update over 1 year ago
    5 pass
  • πŸ‡¨πŸ‡­Switzerland salvis

    Thank you for working on this, @xeM8VfDh!

    Unfortunately, I know close to nothing about the migration process and its test coverage.

    We have have not enough evidence for or against the change, so let's leave it as it is right now.

    The patch is here, if anyone finds that it's needed, please provide feedback here!

  • Thanks @salvis, fine leaving this for now if it expedites D10 release

Production build 0.71.5 2024