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?
The answer to this question is extremely simple, disproportionately to the amount of time I spent typing it up. My problem was merely a case-sensitivity issue. That's right, I capitalised in the names of the databases where I should not have. MySQL's error messages, stating that I was denied permission to carry out the commands I had issued rather than informing me that the databases I was trying to access did not exist, misled me as to the nature of the problem. I leave the question up in case it is somehow instructive to someone, somewhere.