I can connect to the database and see the information_schema and my test and production database schema and can browse them.
If I perform a database schema compare, I can select the test and live databases for comparison.
When I start the comparison a status box displays as follows:
Gathering information...Server 'localhost'.database 'my.testdomain.com'. Gathering information on users...
This is then overlayed with and error message as follows:
SELECT command denied to user 'myuserid@localhost' for table 'db' Quest.Compare.Schema.MySQL.DBModel
I have attempted the same with the V 5.0 Beta and receive the same error.
My suspicion is that the schema compare is relying on a SQL request that is no authorized in a shared hosting environment. Can anyone confirm?
If I am correct, would there be a way for you to provide a schema compare that does not rely on such access (even if that might limit the extent of the comparison, for example not comparing authorizations)
I can confirm that it is a user-privilege problem. I tried to do a schema-compare between a local development and a remote production enviroment using my default mysqlusers which are used by php and got the same error you all got.
After reading this thread i tried the same using local and remote root users and succeeded.
Next step is figuring out the differences in the users privs of both users. I'll get back on this soon i hope.
Using MySQL Workbench i toyed around with the admin privileges of the mysql user i also use with php and narrowed the privileges down to the following 5:
- CREATE USER - GRANT OPTION - RELOAD - SHOW DATABASES * Modify access control
One of these, or a combination of these provide you with the necessary privs to do a schema compare.
I put my money on the GRANT AND Modify access control options however derived from their names i still don't have a clue as to why a user should have modify access control privs to do a schema compare since you're not really modifying anything.