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?
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 '14 at 3:32Base 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 – williamsowen Jul 16 '14 at 4:08database1
anddatabase2
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 '14 at 4:18