Last updated September 15, 2012. Created by mikeryan on January 4, 2011.
Edited by axel.rutz, naught101, pferlito, drewish. Log in to edit this page.
If your source data is in a database supported by the Drupal database API, such as MySQL or Postgres, and your web server has access to that database, you can migrate directly from that database with the Migrate module.
Defining the connection
First, you have to tell Drupal how to connect to the source database. The simplest way is to define the connection in your settings.php file. For example, with Drupal 7 you might add this to settings.php:
<?php
$databases['for_migration']['default'] = array(
'driver' => 'mysql',
'database' => 'migration_database',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'prefix' => '',
);
?>With Drupal 6, it may look like:
<?php
$db_url['for_migration'] = 'mysqli://username:password@localhost:3306/migration_database';
?>Alternatively, you may define the connection at runtime in your migration code (place this in your migration class):
<?php
Database::addConnectionInfo('for_migration', 'default', array(
'driver' => 'mysql',
'database' => 'migration_database',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'prefix' => '',
));
?>Querying the external database
Once you've defined the connection, when you define the query to be passed to MigrateSourceSQL, you need to tell the query what connection to use. Normally, the query would be built off db_select() using the default connection - to use an alternate connection replace your db_select() call with:
<?php
$query = Database::getConnection('default', 'for_migration')
->select('source_table', 'st')
...
?>Notice a gotcha here: Database::getConnection() has its arguments the other way round: $target as second.
If you're defining an explicit count query for the source, apply the same pattern to that as well.
Map tables
The migrate map table, which tracks the mappings between source IDs and destination IDs, is created by default in the Drupal (destination) database. There are performance advantages if the Migrate module can create queries joining your source query directly to the map table - this can be accomplished in two ways:
1. If it's possible to make joins between the Drupal database and your source database (e.g., under MySQL, if they are on the same server, your connections use the same username, and they're not prefixed), do nothing - it will just work.
2. Otherwise, you can instruct Migrate to create the map table in the source database instead of the destination (Drupal) database).
If neither of these cases applies, you must tell the source class not to try to join the map table:
<?php
$this->source = new MigrateSourceSQL($query, array(), NULL, array('map_joinable' => FALSE));
?>In the second case, when you construct the MigrateSQLMap object, you need to tell it explicitly what connection on which to create it by specifying it in the fourth argument:
<?php
$this->map = new MigrateSQLMap($this->machineName,
array(
'source_id' => array('type' => 'varchar',
'length' => 255,
'not null' => TRUE,
'description' => 'Source ID',
)
),
MigrateDestinationNode::getKeySchema(),
'for_migration'
);
?>
Comments
To connect to an external database
When connecting to an external database, you can define it in settings.php using the same methodology as your default database (though give your target a different name). As an example (note that this is for Drupal 7):
$databases['default']['default'] = array(
'driver' => 'mysql',
'database' => 'main_database',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'prefix' => '',
);
$databases['default']['for_migration'] = array(
'driver' => 'mysql',
'database' => 'migration_database',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'prefix' => '',
);
And to connect with it, you use
$query = Database::getConnection('for_migration', 'default');Note that the getConnection is actually flipped on getting the connection information.
Another nice way (barely
Another nice way (barely different) is the getConnection method of MigrateSQLMap.
<?php
$this->map = new MigrateSQLMap(
$this->machineName,
array(...),
MigrateDestinationXXXX::getSchema(),
'for_migration' // defined the way you described it
);
$query = $this->map
->getConnection()
->select(..., ...)
->fields(..., array(
...
));
?>
Only this one worked for me
This method worked best for me with tables on the same database, but drupal tables prefixed and source tables not prefixed.
Declared a new database connection like:
$databases['for_migration']['default'] = array(...)
For more discussion on this...
See #946306: Problems migrating between different databases
Good to see progress on the
Good to see progress on the documentaton.
Just a typo: A quote is missing in the Drupal 6 example $db_url['for_migration].
$databases['default']['for_mi
$databases['default']['for_migration'] = array('driver' => 'mysql',
'database' => 'migration_database',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'prefix' => '',
);
should be(?):
$databases['for_migration']['default'] = array('driver' => 'mysql',
'database' => 'migration_database',
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'prefix' => '',
);
Chinese drupal tutorials Think in Drupal
i believe this is the correct
i believe this is the correct format for the connections, it works for me
settings.php
$databases['default']['default'] = array('driver' => 'mysql',
'database' => 'drupaldb',
'username' => 'root',
'password' => 'secret',
'host' => 'localhost',
'prefix' => '',
);
$databases['default']['ioflegacy'] = array(
'driver' => 'mysql',
'database' => 'a_legacy_db',
'username' => 'root',
'password' => 'secret',
'host' => 'localhost',
'prefix' => '',
);
and then i ran this query on the old legacy db
$result = db_query("SELECT name FROM users", array(), array("target" => "ioflegacy"));
foreach ($result as $record) {
dpm($record) ;
}
Joining map table between two databases
Don't think that's correct:
1. If it's possible to make joins between the Drupal database and your source database (e.g., under MySQL, if they are on the same server, your connections use the same username, and they're not prefixed), do nothing - it will just work
The map table is created in the Drupal (destination) DB, but since the source query doesn't add the schema name to the joined table, the query expects that table to be in the same DB as source tables. You need to add the DB name either to source query or add it in the join statement in MigrateSourceSQL.
Or did I miss something?
migration using dbtng_migrator module
I used the following in settings.php and this worked (from oracle to mysql). Testing the dbtng_migrator module
$databases['mig_to_mysql']['default'] = array (
'database' => 'mysql1_dev',
'username' => 'mysql1_dev',
'password' => 'mysql1_dev',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => false,
);
$databases['default']['default'] = array (
'database' => 'XE',
'username' => 'drupal',
'password' => 'drupal',
'host' => 'localhost',
'port' => '1521',
'driver' => 'oracle',
'prefix' => '',
);
Has anyone attempted to query
Has anyone attempted to query from a mysql to a sql?
$databases['mig_to_mysql']['default'] = array (
'database' => 'mysql1_dev',
'username' => 'mysql1_dev',
'password' => 'mysql1_dev',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => false,
);
$databases['default']['default'] = array (
'database' => 'XE',
'username' => 'drupal',
'password' => 'drupal',
'host' => 'localhost',
'port' => '1521',
'driver' => 'sql',
'prefix' => '',
);
I don't think you missed anything...
...because I've been banging my head against this for an hour or so. My setup meets all the conditions of case 1 -- same server, both online, same username, same password, not prefixed -- and it most certainly does not "just work." I get an error saying the column for the ON clause of the left join is not found, and when you look at the query, it indeed has no schema name in front of it, so I don't see any way that it could work. Other than setting map_joinable to false, I see no simple way to fix this.
"Alternatively, you may
"Alternatively, you may define the connection at runtime in your migration code (place this in your migration class):"
- where - in the constructor ?
*-pike
yes in the constructor
also a word of caution: I'm trying to get cross-DB migrate to work with source:postgres and destination:mysql on a WAMP server. And i had issues with the postgresql drivers that come with apache/php. So if you keep getting database errors, you might want to debug your drivers, starting in php.ini and apache.conf / httpd.conf files.
same here...
Cross-Database migration just doesn't work unfortunately. Either the Map table is created with no destid1 field or it creates no map table at all.