Copying contents of a MySQL table to a table in another (local) database
- by Philip Eve
I have two MySQL databases for my site - one is for a production environment and the other, much smaller, is for a testing/development environment. Both have identical schemas (except when I am testing something I intend to change, of course). A small number of the tables are for internationalisation purposes:
TransLanguage - non-English languages
TransModule - modules (bundles of phrases for translation, that can be loaded individually by PHP scripts)
TransPhrase - individual phrases, in English, for potential translation
TranslatedPhrase - translations of phrases that are submitted by volunteers
ChosenTranslatedPhrase - screened translations of phrases.
The volunteers who do translation are all working on the production site, as they are regular users.
I wanted to create a stored procedure that could be used to synchronise the contents of four of these tables - TransLanguage, TransModule, TransPhrase and ChosenTranslatedPhrase - from the production database to the testing database, so as to keep the test environment up-to-date and prevent "unknown phrase" errors from being in the way while testing. My first effort was to create the following procedure in the test database:
CREATE PROCEDURE `SynchroniseTranslations` ()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN
DELETE FROM `TransLanguage`;
DELETE FROM `TransModule`;
INSERT INTO `TransLanguage` SELECT * FROM `PRODUCTION_DB`.`TransLanguage`;
INSERT INTO `TransModule` SELECT * FROM `PRODUCTION_DB`.`TransModule`;
INSERT INTO `TransPhrase` SELECT * FROM `PRODUCTION_DB`.`TransPhrase`;
INSERT INTO `ChosenTranslatedPhrase` SELECT * FROM `PRODUCTION_DB`.`ChosenTranslatedPhrase`;
END
When I try to run this, I get an error message: "SELECT command denied to user 'username'@'localhost' for table 'TransLanguage'". I also tried to create the procedure to work the other way around (that is, to exist as part of the data dictionary for the production database rather than the test database). If I do it that, way, I get an identical message except it tells me I'm denied the DELETE command rather than SELECT.
I have made sure that my user has INSERT, DELETE, SELECT, UPDATE and CREATE ROUTINE privileges on both databases. However, it seems as though MySQL is reluctant to let this user exercise its privileges on both databases at the same time. How come, and is there a way around this?
