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?
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;