0

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?

| improve this question | |
  • 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 '14 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 – williamsowen Jul 16 '14 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 '14 at 4:18
2

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.

| improve this answer | |
  • Oh, the FEDERATED engine is usually disabled, turn it on, if AWS lets you. – Astrayus Jul 16 '14 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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