Trouble with migration from one database to another, base table not found db1.migrate_status

I have some related issue, but with map tables. In theory, when you define the MigrateSQLMap, you can specify the database where map tables will be created. When using the 'default' (local / target) database, the map tables are created in local, but when performing the migration, Migrate looks for them in the remote / source DB, thus getting a SQL error:

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'user'@'62.42.x.x' for table 'migrate_map_noticia'

If I define the MigrateSQLMap in the remote DB like

    // We instantiate the MigrateMap
    $this->map = new MigrateSQLMap($this->machineName,
        array(
          'nid' => array('type' => 'int',
                          'unsigned' => TRUE,
                          'not null' => TRUE,
                          'description' => 'D6 Unique node ID',
                          'alias' => 'n'
                         )
        ),
        MigrateDestinationNode::getKeySchema(),
        'sourcce_db'
      );

then the tables are created in the source DB, and the error doesn't appear, but performance is very low (well, in fact I wasn't able to run the migration with map tables in local, so I cannot compare, but I figure out that those tables are being queried/updated intensively and that's not a good thing to do with a remote server).

If at least the error message indicated the line where it happens, the problem would be easier to pinpoint...

@madmatter23 - I would bet that using db_set_active() is your problem. You should not be using db_set_active() to query the legacy database, use Database::getConnection('default', 'legacy')->select() in place of db_select().

@albertczyk - Your case is different, please open distinct issues rather than tack on to slightly-related ones. That being said, setting the option map_joinable => FALSE in the MigrateSourceSQL constructor will ensure that the map table is accessed separately from the source query, from the default database.

The problem here, at least in my case, is that the source database tables are prefixed.

When migrate tries to execute this source query:

SELECT n.nid AS nid, n.title AS title, n.status AS status, n.created AS created, n.changed AS changed, n.promote AS promote, n.sticky AS sticky, n.uid AS uid, nr.body AS body, nr.teaser AS teaser, ctv.field_video_embed AS field_video_embed, ctv.field_video_info_value AS field_video_info_value, map.sourceid1 AS migrate_map_sourceid1, map.destid1 AS migrate_map_destid1, map.needs_update AS migrate_map_needs_update, GROUP_CONCAT(DISTINCT(td1.name)) AS terms_tags, GROUP_CONCAT(DISTINCT(td2.name)) AS terms_video_categories
FROM 
{node} n
INNER JOIN {node_revisions} nr ON nr.vid = n.vid
LEFT OUTER JOIN {term_node} tn1 ON tn1.nid = n.nid
LEFT OUTER JOIN {term_data} td1 ON td1.tid = tn1.tid AND td1.vid = 2
LEFT OUTER JOIN {term_node} tn2 ON tn2.nid = n.nid
LEFT OUTER JOIN {term_data} td2 ON td2.tid = tn2.tid AND td2.vid = 6
LEFT OUTER JOIN {content_type_video} ctv ON ctv.vid = n.vid
LEFT OUTER JOIN {sourcedb.migrate_map_videonode} map ON n.nid = map.sourceid1
WHERE  (n.type = 'video') AND (n.status = '1') AND (n.nid = '98525') AND( (map.sourceid1 IS NULL ) OR (map.needs_update = '1') )
GROUP BY n.nid
ORDER BY n.nid ASC

Note that the map table (sourcedb.migrate_map_videonode) is also between {} and it already has the database name appended.

When the database layers adds the prefixes we end up with the following query:

SELECT n.nid AS nid, n.title AS title, n.status AS status, n.created AS created, n.changed AS changed, n.promote AS promote, n.sticky AS sticky, n.uid AS uid, nr.body AS body, nr.teaser AS teaser, ctv.field_video_embed AS field_video_embed, ctv.field_video_info_value AS field_video_info_value, map.sourceid1 AS migrate_map_sourceid1, map.destid1 AS migrate_map_destid1, map.needs_update AS migrate_map_needs_update, GROUP_CONCAT(DISTINCT(td1.name)) AS terms_tags, GROUP_CONCAT(DISTINCT(td2.name)) AS terms_video_categories
FROM 
vb_drupal_node n
INNER JOIN vb_drupal_node_revisions nr ON nr.vid = n.vid
LEFT OUTER JOIN vb_drupal_term_node tn1 ON tn1.nid = n.nid
LEFT OUTER JOIN vb_drupal_term_data td1 ON td1.tid = tn1.tid AND td1.vid = 2
LEFT OUTER JOIN vb_drupal_term_node tn2 ON tn2.nid = n.nid
LEFT OUTER JOIN vb_drupal_term_data td2 ON td2.tid = tn2.tid AND td2.vid = 6
LEFT OUTER JOIN vb_drupal_content_type_video ctv ON ctv.vid = n.vid
LEFT OUTER JOIN vb_drupal_sourcedb.migrate_map_videonode map ON n.nid = map.sourceid1
WHERE  (n.type = :db_condition_placeholder_0) AND (n.status = :db_condition_placeholder_1) AND (n.nid = :db_condition_placeholder_2) AND( (map.sourceid1 IS NULL ) OR (map.needs_update = :db_condition_placeholder_3) )
GROUP BY n.nid
ORDER BY n.nid ASC

Which is obviously incorrect since the prefix has been appended to the database name for the map table.

My settings.php connections array looks something like this:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'maindb',
      'username' => 'maindb',
      'password' => 'maindb',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
  'sourcedb' => 
  array (
    'default' => 
    array (
      'database' => 'sourcedb',
      'username' => 'sourcedb',
      'password' => 'sourcedb',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => 'vb_drupal_',
    ),
  ),
);

The only solution for me so far has been to add the sourcedb parameter to the MigrateSQLMap constructor, as @albertczyk pointed out.

But I don't want migrate tables to be in the sourcedb. Is there any solution in this case?

Sorry @mikeryan, I had tried the map_joinable solution with no luck, but I just realized I was passing it in the wrong array. It is working now. Thanks.

obfuscating