Create a MySQL view table on one database but fill with results from another database

Question

I have 2 databases: myshop (the Drupal db) and myshop_migrate (the Migrate db). To make things tricky - both databases reside on different AWS instances.

I need to create a mysql view on myshop to hold a SELECT * query from the myshop_migrate database.

Currently, I have:

db_set_active('myshop_migrate_db'); //reference migrate db
$query = db_query('CREATE OR REPLACE VIEW myshop_migrate_stock_commerce AS SELECT * FROM myshop_migrate.stock_commerce');

• but this just creates the VIEW table on the myshop_migrate database (as the current active db is referencing myshop_migrate)..

db_set_active(); //default drupal db
$query = db_query('CREATE OR REPLACE VIEW myshop_migrate_stock_commerce AS SELECT * FROM myshop_migrate.stock_commerce');

• This wont work because myshop_migrate.stock_commerce doesn't exist on the myshop database!

Completely lost on this. Can I somehow store the result of the SELECT * from myshop_migrate in a variable? Switch the active db, create a view table on myshop, then import the variable into the table?

Answer 1

Since that view is only a SELECT on that table, try using a FEDERATED table instead.

I never had the chance to use it so far, but it should go like this:

CREATE TABLE myshop_migrate ( exactly like myshop table )
ENGINE=FEDERATED
DEFAULT CHARSET=same of myshop
CONNECTION='mysql://user@host:9306/myshop_migrated/stock_commerce';

where user and host are those of the remote server.

create it from phpmyadmin or mysql workbench, just to have additional options and hints that drupal can't give you.