Hello. 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

Comments

suricactus created an issue. See original summary.

suricactus’s picture

suricactus CreditAttribution: suricactus as a volunteer commented

Little update. Actually there is an imported content, but there is no mapping between the source and destination, so I can import the same page hundreds of times.

suricactus’s picture

suricactus CreditAttribution: suricactus as a volunteer commented

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:

    $this->map = new MigrateSQLMap($this->machineName,
        array(
          'id' => array(
            'type' => 'int',
            'description' => t('Article Source ID'),
            'alias' => 'N',
          ),
          'id2' => array(
            '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.

suricactus’s picture

suricactus CreditAttribution: suricactus as a volunteer commented
Status: Active » Closed (works as designed)