MySQL Bugs: #61414: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL com

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:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	5.5.12	OS:	Linux (x86_64, opensuse 11.4, pkg)
Assigned to:		CPU Architecture:	Any

Description:
i'd originally posted this @ the list,

http://lists.mysql.com/mysql/225163
"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
        /usr/share/mysql-zrm/plugins/socket-copy.pl
        --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  >
        "/var/mysql-bkup/manual/20110605131003/backup.sql"
        mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to
        user 'root'@'localhost' for table 'cond_instances' when using
        LOCK TABLES
-->     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  >
         "/var/mysql-bkup/manual/20110605131003/backup.sql"manual:backup:INFO:
         PHASE START: Cleanup
        manual:backup:INFO: backup-status=Backup failed
        ...

reading up on the error at,

 http://bugs.mysql.com/bug.php?id=33762
 http://bugs.mysql.com/bug.php?id=49633

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

        ...
        [mysqldump]
        quick
        quote-names
        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:
exec

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

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

Please, send also the output of:

/usr/bin/mysqldump --version

hi,

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

yes, there is.

adding to

 /etc/mysql-zrm/manual/mysql-zrm.conf
    ...
    all-databases=1
+   exclude-pattern="performance_schema"
    ...

then,

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

returns,

    ...
    /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)

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 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
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.

thanks for the references.

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

cheers.

I do not see any obvious MySQL bug in this case.