Bug #61414 upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL com
Submitted: 5 Jun 2011 22:22 Modified: 6 Jun 2011 5:23
Reporter: ag d Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:5.5.12 OS:Linux (x86_64, opensuse 11.4, pkg)
Assigned to: CPU Architecture:Any

[5 Jun 2011 22:22] ag d
i'd originally posted this @ the list,

"upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command denied to user 'root'@'localhost' for table'cond_instances' ... "

where the discussion led to setting up a second server / replication slave rather than addressing the problem. having seen the issue is a couple of other places, thought i'd post here, and hope to actually narrow down or resolve, rather than avoid, the problem.

i use ZRM to do backups of my databases.

with v5.1.x, this works fine,

  mysql-zrm-scheduler --now --backup-set manual --backup-level 0

to execute a manual backup.

i recently upgraded from v5.1.x -> v5.5.12,

 mysqladmin -V
  mysqladmin  Ver 8.42 Distrib 5.5.12, for Linux on i686

now, at exec of that backup cmd, i see an ERROR @ console,

        manual:backup:INFO: PHASE START: Creating raw backup
        manual:backup:INFO: Command used for raw backup is
        --mysqlhotcopy=/usr/bin --host="localhost" --port="3306"
        --socket="/var/cache/mysql/mysql.sock" --quiet  mysql 
        "/var/mysql-bkup/manual/20110605131003" >
        /var/cache/tmp/bZvaQFwQY2 2>&1
        manual:backup:INFO: raw-databases=mysql
        manual:backup:INFO: PHASE END: Creating raw backup
        manual:backup:INFO: PHASE START: Creating logical backup
        manual:backup:WARNING: The database(s) drupal6
        performance_schema will be backed up in logical mode since they
        contain tables that use a transactional engine.
        manual:backup:INFO: Command used for logical backup is
        "/usr/bin"/mysqldump --opt --extended-insert --create-options
        --default-character-set=utf8 --routines --host="localhost"
        --port="3306" --socket="/var/cache/mysql/mysql.sock" --databases
        drupal6 performance_schema  >
        mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to
        user 'root'@'localhost' for table 'cond_instances' when using
-->     manual:backup:ERROR: mysqldump did not succeed.
         Command used is "/usr/bin"/mysqldump --opt --extended-insert
         --create-options --default-character-set=utf8 --routines
         --host="localhost" --port="3306"
         --socket="/var/cache/mysql/mysql.sock" --databases drupal6
         performance_schema  >
         PHASE START: Cleanup
        manual:backup:INFO: backup-status=Backup failed

reading up on the error at,


it looks to do with mysqldump itself.  i modified in /etc/my.cnf

        max_allowed_packet = 8M
+       skip-lock-tables

but that doesn't seem to make any difference.

something's changed between 5.1.x & 5.5.x.

what do i need to modify to get past this error?

How to repeat:

mysql-zrm-scheduler --now --backup-set manual --backup-level 0
[6 Jun 2011 4:26] Valeriy Kravchuk
Is there any way with ZRP to skip dumping of the performance_schema database?

Please, send also the output of:

/usr/bin/mysqldump --version
[6 Jun 2011 4:50] ag d

> Is there any way with ZRP to skip dumping of the performance_schema database?

yes, there is.

adding to

+   exclude-pattern="performance_schema"


  mysql-zrm-scheduler --now --backup-set manual --backup-level 0


    /usr/bin/mysql-zrm started successfully

with no apparent ERRORs.

so, seems by excluding it, takes care of the error.

of course, it's not then backed up.  i'm not at all certain what that means for the state of the backup, or my ability to successfully restore to a fully-working state.  i suspect since it's there, it *should* be backed up, right?

likely figuring out how to back it up correctly is probably the right solution ...

to that end,

> Please, send also the output of:
> /usr/bin/mysqldump --version

/usr/bin/mysqldump --version
  mysqldump  Ver 10.13 Distrib 5.5.12, for Linux (i686)
[6 Jun 2011 5:05] Valeriy Kravchuk
http://dev.mysql.com/doc/refman/5.5/en/performance-schema-restrictions.html explains why you get error message with LOCK TABLES. http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html explains what exact data this database contains:

"Tables in the performance_schema database are views or temporary tables that use no persistent on-disk storage"

Personally I see no big need to dump these data. The fact that ZRM tries to backup it by default with --opt option of mysqldump is a bug in ZRP, and this will never work:

macbook-pro:5.5 openxs$ bin/mysqldump -uroot performance_schema
-- MySQL dump 10.13  Distrib 5.5.14, for osx10.5 (i386)
-- Host: localhost    Database: performance_schema
-- ------------------------------------------------------
-- Server version	5.5.14-debug

/*!40101 SET NAMES utf8 */;
/*!40103 SET TIME_ZONE='+00:00' */;
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

One should use --skip-lock-tables option with mysqldump to dump performance_schema. Please, check and report ZRM bug for their current behavior.
[6 Jun 2011 5:20] ag d
thanks for the references.

and, for the call that it's a ZRM issue.  i'll get this info into a queue over there.

[6 Jun 2011 5:23] Valeriy Kravchuk
I do not see any obvious MySQL bug in this case.