How do I enable cross-database joins in mysql?

Question

I am trying to port some data over from my production database to my sandbox using a query like this:

INSERT `dbsandbox`.`SomeTable`(Field1, Field2, Field3)
SELECT t.Field1, t.Field2, t.Field3
FROM `dbprod`.`SomeTable` t;

When I attempt this cross-database join I get the following error:

ERROR 1142 (42000): SELECT command denied to user 'myusername'@'server.domain.tdl' for table 'SomeTable'

The user in question has permission to the tables in question for both databases. I have tried this in both the unix mysql client and the windows MySQL Query Browser application with the same result.

What am I missing?

Answer 1

It sounds like a permissions problem. Often user permissions are set up on a database in a database fashion, so the user for the destination doesn't have access to the source.

First make sure that you can do the select from the source database.

SELECT t.Field1, t.Field2, t.Field3
FROM `dbprod`.`SomeTable` t;

Does that work? If not, then you need to grant the user select permissions on the source db.

Both databases are on/in the same server right?

Answer 2

It turns out it was a permissions problem. The source database required a password for the username I was using in order to access any tables. The target only required the username be on the localhost.

Even though I launch the MySQL client using a password every time within the context of a cross-database query another connection is attempted. This connection does not remember that I originally authenticated against the client with a password so connecting from the sandbox to the production database failed. Apparently explicitly stating the database name for a table sometimes implies the need for another connection, as well.

The answer was to initiate the query from with the production database, refer to the local tables without the database qualifier, then insert across to the sandbox database tables. This time it worked:

USE dbprod

INSERT dbsandbox.SomeTable(Field1, Field2, Field3) SELECT t.Field1, t.Field2, t.Field3 FROM SomeTable t;

Answer 3

Are the 2 databases on different servers? I am trying to do the same but I how do I connect to 2 databases on different servers?

i have it working if the databases are on the same server.

Thanks.