Thesqldump error on common_schema

backupMySQLmysql-5.5mysqldump

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?

Best Answer

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.

Related Question