Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I tried to take the backup of common_schema in Server version: 5.5.44 MySQL Community Server (GPL).
mysqldump -u root -p common_schema > common_schema_bkup.sql
I got the following error:
"mysqldump: Got error: 1356: View 'common_schema._bare_grantee_grants' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES"
How can I rectify this?
Perhaps you should use --single-transaction
mysqldump --single-transaction -u root -p common_schema > common_schema_bkup.sql
Why ? According to the MySQL Documentation on mysqldump, you need the following rights:
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the
--single-transactionoption is not used. Certain options might require other privileges as noted in the option descriptions.To reload a dump file, you must have the same privileges needed to create each of the dumped objects by issuing CREATE statements manually.
Please note the common_schema is comprised of one table and 27 views. You must have the necessary grants in order to be able to dump them, whether or not you dump with --single-transaction. This might include having the CREATE VIEW and SHOW VIEW grants.
I think the problem rests with mysqldump itself. Many metacommands are written around the dump of the definitions that could be interfering with rendering output that can logically represented. In some cases, a mysqldump of common_schema could not be reimported.
Your best chance would be to import common_schema from scratch. Right from the common_schema Documentation, it says on troubleshooting installation:
Since installation is merely an import act, you should only expect trouble if schema generation is unable to execute on your server.
- Are you executing on a 5.0 MySQL server? This version is not supported.
- Were errors reported during installation process?
- What was common_schema's last message during install?
- What is the output of SELECT * FROM common_schema.status?
Since unique metadata is not in common_schema, you should be able to reimport it rather that risk backing up with mysqldump with the possibility of not reloading it.
_bare_grantee_grants': View 'common_schema._bare_grantee_grants' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)
SELECT USER() AttemptedToLogin,CURRENT_USER() AllowedToLogin;. What is the output ??? I just want to make sure you have full privileges.
Jul 24, 2015 at 2:51
SELECT COUNT(1) table_count,engine FROM information_schema.tables WHERE table_schema='common_schema' GROUP BY engine; What is the output ???
Jul 24, 2015 at 2:54
For some reason, one of the views is lost. You should re-install common_schema. Or maybe you don't have all proper access rights - in that case, just check your rights.
