0

I am using the awesome module Migrate, but I have problem, which I am unable to solve. I try to migrate articles from custom PHP site with MySQL DB to D7 with MySQL. The generated SQL query from my migration is ok when I run it in my MySQL DB using MySQL CLI. But when I try to migrate it using the Migrate UI, I get an error:

An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=113&op=do StatusText: Service unavailable (with message) ResponseText: PDOException: SQLSTATE[HY000]: General error: 1364 Field 'sourceid2' doesn't have a default value: INSERT INTO {migrate_map_geografbgarticlenode} (sourceid1, needs_update, rollback_action, hash) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3); Array ( [:db_insert_placeholder_0] => 2 [:db_insert_placeholder_1] => 3 [:db_insert_placeholder_2] => 0 [:db_insert_placeholder_3] => ) in MigrateSQLMap->saveIDMapping() (line 434 of ~/drupal/dev/sites/all/modules/migrate/plugins/sources/sqlmap.inc).

It seems the problem is with my MigrateSQLMap function, looks like N.id is NULL when the query is run by Drupal. But I triple checked it before posting, it's not nullable field in DB and in the generate query result. Any idea how can I fix it?

$query = Database::getConnection('default', 'legacy')
      ->select('news', 'N')
      ->fields('N', array('id', 'category_id', 'createdon', 'updatedon', 'createdby', 'updatedby'))
      ->fields('L', array('code'))
      ->fields('NT', array('name', 'short_description', 'description'))
      ->fields('G', array('id', 'description'));
    $query->leftJoin('news_translations', 'NT', 'NT.target_id = N.id');
    $query->leftJoin('languages', 'L', 'NT.language_id = L.id');
    $query->leftJoin('tags', 'T', 'T.news_id = N.id');
    $query->leftJoin('gallery', 'G', 'G.target_id = N.id');
    $query->addExpression('GROUP_CONCAT(LOWER(T.tag))', 'tags');
    $query->groupBy('N.id');
    $query->orderBy('N.id', 'ASC');

    $this->source = new MigrateSourceSQL($query);
    $this->destination = new MigrateDestinationNode('article');

    $this->map = new MigrateSQLMap($this->machineName,
        array(
          'id' => array(
            'type' => 'int',
            'unsigned' => TRUE,
            'not null' => TRUE,
            'description' => t('Article Source ID'),
            'alias' => 'N',
          ),
        ),
        MigrateDestinationNode::getKeySchema()
      );

    //... not relevant
0

Ok, I resolved this issue. I am new to Migrate module and I think the message was not clear enough.

First I have mapped to complex foreign key with two columns:

<?php
    $this->map = new MigrateSQLMap($this->machineName,
        array(
          'id' => array(
            'type' => 'int',
            'description' => t('Article Source ID'),
            'alias' => 'N',
          ),
          'id2' => array( // no longer exists in my code
            'type' => 'int',
            'description' => t('Article Source ID'),
            'alias' => 'N',
          ),
        ),
        MigrateDestinationNode::getKeySchema()
      );
?>

But then I used only the first foreign key. The database table storing the mapping between source and destination had two columns sourceId, because of first attempt to run migration, but hasn't updated with the new migration in the code.

I dropped the migration_map table and everything was freshly rebuild and works like a charm.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.