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?

  • can these 2 databases use a shared db username and password ? If yes, you can change your query to like: create or replace view myshop.myshop_migrate_stock_commerce as select * from myshop_migrate_db.stock_commerce eg in your query you just supply the DB names literally, this is supported by mysql. Of course if you can share a db user/pass then there's no need for the View ...
    – tenken
    Jul 16, 2014 at 3:32
  • thanks for the feedback - by share db user/pass, do you mean match them up so they're both the same on each db? (apologies - lacking in mysql knowhow!). If so, I've tried this.. but just get the following error: Base table or view not found: 1146 Table 'myshop_migrate.stock_commerce' doesn't exist.. Not sure if what I'm trying to achieve is doable. I can't alter connections whilst querying, so can't pluck data from both.. Annoying Jul 16, 2014 at 4:08
  • no i'm saying if you have database1 and database2 just re-use a single database user between the two of them: grant all priviledges on database1.* to USERNAME@localhost; grant all priviledges on database2.* to USERNAME@localhost;. Here is an example to create a db user for Debian debuntu.org/…
    – tenken
    Jul 16, 2014 at 4:18

1 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 )
DEFAULT CHARSET=same of myshop

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.

  • Oh, the FEDERATED engine is usually disabled, turn it on, if AWS lets you.
    – Astrayus
    Jul 16, 2014 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.