Problems migrating between different databases

Any chance you can turn this into a patch for README.txt (Multiple DB connections)?

Hello,

struggling too here...

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mglegacy1.migrate_map_mgterm' doesn't exist' in...

mglegacy1 is the source database

followed the steps above... and additionally I've got this....

WD php: Argument 4 passed to MigrateSourceSQL::__construct() must be an array, boolean given, called in                                                                           [error]
/sites/all/modules/migrate_mg/mg.inc on line 47 and defined in
/sites/all/modules/migrate/plugins/sources/sql.inc on line 90.

comes from here...

$this->source = new MigrateSourceSQL($query, array(), $count_query, FALSE);

Any Ideas?

Thanks,
Ivo

I use the easy way out - a single database-connection that allows access to both databases, and then using database.table when constructing the db_select()

Ie.

$query = db_select('old_system.article', 'art')
			->fields('art', array('articleid', 'categoryid', 'publishdate', 'modifieddate', 'author'))
			->condition('status','online');
$this->source = new MigrateSourceSQL($query);

So you might want to check if that option is available to you too (that is, accessing the old database from your current Drupal database connection) instead of trying to use multiple database-connections..

Thanks!

This works for me!

Additionally i resolved the problem with using multiple database-connections

I was looking into the other errors...but it is obvious that something was wrong here

    $this->source = new MigrateSourceSQL($query, array(), $count_query, FALSE);

So i changed the code according to the error message to

    $this->source = new MigrateSourceSQL($query, array(), $count_query, array('map_joinable' => FALSE));

It works! :=)

Thanks,
Ivo

FYI, I have been using this "period trick" a lot and it works nicely on mysql. I fought to preserve this feature in Drupal 7 as it was impossible for a little while #715132: Over aggressive escaping of table names. Note that the period technique is better than setting mapJoinable = FALSE since those map joins improve efficiency a lot.

We need some better docs here. Anyone up for a README.txt patch?

Thanks!

When we write up some documentation for the client I will try to gather some info for the docs!

Regards,
Ivo Radulovski
www.drupal.bg/en

Rather than README.txt, let's work detailed documentation like this into the module documentation on d.o, which is finally taking shape. I've created a page for this subject: http://drupal.org/node/1014558

I was going through this for a project I'm migrating from Drupal 6 to Drupal 7 and I found that using Database::getConnection requires for the target and key to be flipped - I've added a comment at http://drupal.org/node/1014558#comment-4105852

EDIT: nvm - I think I had my thing idea flipped..

Hi,

the issues are a littlebit confusing me so i write down here my experience with two databases and hope it helps some.

first i defined the second database in drupal settings.php like this:

$databases['joomla15']['default'] = array(
  'database' => 'devJoomla15',
  'username' => 'xxxx',
  'password' => 'xxxx',
  'host' => 'localhost',
  'port' => '',
  'driver' => 'mysql',
  'prefix' => '',  
);

in my migration class (node migration) i used following SQLMap

    $this->map = new MigrateSQLMap($this->machineName,
        array('id' => array('type' => 'int',
                           'unsigned' => 1,
                           'length' => 11,
                           'not null' => TRUE,
                           'description' => 'Joomla content id',
                          )
        ),
        MigrateDestinationNode::getKeySchema()
      );

    // initiate connection to the joomla database and select the content table...
    $query = Database::getConnection('default', 'joomla15')
             ->select('jos_content', 'jc');
    
    // now add the fields for query based upon the description of Drupals Dynamic Database description
    // see here: drupal.org/node/310075#fields
    $query->addField('jc', 'id', 'id');
 
    // now we use a own count query and use again the joomla database for it
    // and select again the jos_content table...
    $count_query = Database::getConnection('default', 'joomla15')
      ->select('jos_content', 'jc');    
    $count_query->addExpression('COUNT(id)', 'cnt');

    // define the migration source and ensure that we dont use map_joinable because
    // we use two diffent databases and the map table is in the drupal databse...
    $this->source = new MigrateSourceSQL($query, array(), $count_query, array('map_joinable' => FALSE));

this way works perfekt for me. I can get the source from the Joomla Database and the mapping also works fine.

regards
Dennis

OK, I've updated http://drupal.org/node/1014558 based on input from @drewish and @BTMash - how does that look?

I still had quite a lot of trouble navigating this, so created an example, #1210064: Provide a cross-db migration example

Hello Mike,
I'm sorry, but I can't see any improvement on that page...
I found a solution for my problem only looking at this issue. I'm working on two DBs on the same server. Drupal 6.
#4 saved my day.
Thanks.

-ermannob