4

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?

  • Run this query and paste the output here: select * from common_schema.sql_grants; – Federico Razzoli Jul 23 '15 at 22:21
  • @FedericoRazzoli I get this error -- ERROR 1146 (42S02): Table 'common_schema.sql_grants' doesn't exist – tesla747 Jul 24 '15 at 2:49
  • It has been deleted or you don't have access rights. – Federico Razzoli Jul 26 '15 at 14:33
2

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-transaction option 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.

UPDATE 2015-07-24 23:09 EST

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.

  • Okay I will try it and update but just wanted to clarify that I'm trying this with root user and it has all the privileges. – tesla747 Jul 23 '15 at 18:03
  • I'm getting the same error -- mysqldump: Couldn't execute 'SHOW FIELDS FROM _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) – tesla747 Jul 24 '15 at 2:49
  • Please connect to MySQL and run SELECT USER() AttemptedToLogin,CURRENT_USER() AllowedToLogin;. What is the output ??? I just want to make sure you have full privileges. – RolandoMySQLDBA Jul 24 '15 at 2:51
  • +------------------+----------------+ | AttemptedToLogin | AllowedToLogin | +------------------+----------------+ | root@localhost | root@localhost | +------------------+----------------+ 1 row in set (0.00 sec) – tesla747 Jul 24 '15 at 2:52
  • Run SELECT COUNT(1) table_count,engine FROM information_schema.tables WHERE table_schema='common_schema' GROUP BY engine; What is the output ??? – RolandoMySQLDBA Jul 24 '15 at 2:54
0

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.

  • I'm tryiny it from root user. It has all the rights. – tesla747 Jul 26 '15 at 15:07
  • Being root doesn't mean that you have all the rights: it is possible that you accidentally revoked some rights from root. But, as I said, the alternative is that you don't have that view anymore. Reinstall Common Schema. – Federico Razzoli Jul 26 '15 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.