mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'debian-sys-maint'@'localhost' for table 'cond_instances' when using LOCK TABLES

[James]

Release: 12.04
automysqlbackup:
  Installed: 2.6+debian-2
  Candidate: 2.6+debian-2
  Version table:
 *** 2.6+debian-2 0
        500 http://us.archive.ubuntu.com/ubuntu/ precise/universe amd64 Packages
        100 /var/lib/dpkg/status

After a remove --purge, reinstall of automysqlbackup, and configure in /etc/defaults/automysqlbackup, I am emailed the following error when cron runs /usr/sbin/automysqlbackup:

mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'debian-sys-maint'@'localhost' for table 'cond_instances' when using LOCK TABLES

it seems that the default on ubuntu is to use the user debian-sys-maint to run this backup, however debian-sys-maint doesn't have full permissions. Some say that these tables are not necessary to backup and recommend the flag " --skip-add-locks" OR "--skip-lock-tables":

http://serverfault.com/questions/268566/cant-backup-the-mysql-table-with-mysqldump-select-lock-tabl-command-denied-for

I managed to get the error to go away by adding "--skip-lock-tables" to /usr/sbin/automysqlbackup:

        if [ -z "${USERNAME}" -o -z "${PASSWORD}" ] ; then
                mysqldump --defaults-file=/etc/mysql/debian.cnf $NEWOP\
T --skip-lock-tables $1 > $2
        else
                mysqldump --user=$USERNAME --password=$PASSWORD --host\
=$DBHOST --skip-lock-tables $NEWOPT $1 > $2
        fi
        return 0

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Nate Carlson (natecarlson)]

Correct solutions appears to be to exclude the 'performance_schema' database from mysqldump.

[Jérôme Drouet (jerome-drouet)]

to ignore 'performance_schema' database dump, replace in file /etc/default/automysqlbackup the line :
DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | tr \\\r\\\n ,\ `
with :
DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | grep -v ^performance_schema$ | tr \\\r\\\n ,\ `

automysqlbackup version 2.6+debian-2 on ubuntu 12.04 precise (mysql 5.5.22-0ubuntu1)

[Pander (pander)]

Did you use the passwords in /etc/mysql/debian.cnf from when the database was initially created? These can change when reinstalling a system or moving a database to other systems.

[Jan Groenewald (jan-aims)]

Is this not a general mysql bug? Or is the behaviour intentially different from that on lucid?
(I'm not using automysqlbackup, just a cron script running mysqldump on every database.
I get the same error on precise, but not on lucid)

[Jan Groenewald (jan-aims)]

Is --skip-lock-tables not an unsafe solution that might produce corrupt backups?
Is it not better to simply stop mysql, mysldump, then start, if you prioritize backups
over 247 uptime?

[Jan Groenewald (jan-aims)]

Solved this by ecluding performance_schema (along with already exlcuded information_schema) from the backups.

[Andrew Schulman (andrex)]

If this is a bug, it's a bug in mysqldump, not automysqldump. It happens when automysqldump runs mysqldump performance_schema.

[Nate Carlson (natecarlson)]

@Andrex Yeah, it's well documented that it is because of the performance_schema table.. I however disagree that it is a bug in mysqldump; it is trying to dump that table as requested, and getting denied (someone may very well want to dump the current stats out of that table, and if they want to, they can do it with --skip-lock-tables).. for the case of automysqlbackup, I'm still of the opinion that the correct solution is to simply exclude the performance_schema table (alongside information_schema).

[Thomas Goirand (thomas-goirand)]

Hi,

There's absolutely no reason why this bug is still open, with comments from users. This has been fixed a long time ago in Debian, and should be also in Ubuntu. Perhaps this is just affecting users of Ubuntu 10.04, in which case I would advise to upgrade to the latest version of automysqlbackup from Debian SID (it will also work with any version of Ubuntu normally).

For the above reasons, I'm asking for the closing of this bug.

Thomas

[Jan Groenewald (jan-aims)]

This bug persists for me on 12.04

On 1 February 2013 07:33, Thomas Goirand <email address hidden> wrote:

> Hi,
>
> There's absolutely no reason why this bug is still open, with comments
> from users. This has been fixed a long time ago in Debian, and should be
> also in Ubuntu. Perhaps this is just affecting users of Ubuntu 10.04, in
> which case I would advise to upgrade to the latest version of
> automysqlbackup from Debian SID (it will also work with any version of
> Ubuntu normally).
>
> For the above reasons, I'm asking for the closing of this bug.
>
> Thomas
>
> --
> You received this bug notification because you are a member of AIMS,
> which is subscribed to the bug report.
> https://bugs.launchpad.net/bugs/995587
>
> Title:
> mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user
> 'debian-sys-maint'@'localhost' for table 'cond_instances' when using
> LOCK TABLES
>
> Status in “automysqlbackup” package in Ubuntu:
> Confirmed
>
> Bug description:
> Release: 12.04
> automysqlbackup:
> Installed: 2.6+debian-2
> Candidate: 2.6+debian-2
> Version table:
> *** 2.6+debian-2 0
> 500 http://us.archive.ubuntu.com/ubuntu/ precise/universe amd64
> Packages
> 100 /var/lib/dpkg/status
>
>
>
> After a remove --purge, reinstall of automysqlbackup, and configure in
> /etc/defaults/automysqlbackup, I am emailed the following error when cron
> runs /usr/sbin/automysqlbackup:
>
> mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user
> 'debian-sys-maint'@'localhost' for table 'cond_instances' when using
> LOCK TABLES
>
> it seems that the default on ubuntu is to use the user debian-sys-
> maint to run this backup, however debian-sys-maint doesn't have full
> permissions. Some say that these tables are not necessary to backup
> and recommend the flag " --skip-add-locks" OR "--skip-lock-tables":
>
> http://serverfault.com/questions/268566/cant-backup-the-mysql-table-
> with-mysqldump-select-lock-tabl-command-denied-for
>
> I managed to get the error to go away by adding "--skip-lock-tables"
> to /usr/sbin/automysqlbackup:
>
>
> if [ -z "${USERNAME}" -o -z "${PASSWORD}" ] ; then
> mysqldump --defaults-file=/etc/mysql/debian.cnf $NEWOP\
> T --skip-lock-tables $1 > $2
> else
> mysqldump --user=$USERNAME --password=$PASSWORD --host\
> =$DBHOST --skip-lock-tables $NEWOPT $1 > $2
> fi
> return 0
>
> To manage notifications about this bug go to:
>
> https://bugs.launchpad.net/ubuntu/+source/automysqlbackup/+bug/995587/+subscriptions
>
> --
> Mailing list: https://launchpad.net/~aims
> Post to : <email address hidden>
> Unsubscribe : https://launchpad.net/~aims
> More help : https://help.launchpad.net/ListHelp
>

--
  .~.
  /V\ Jan Groenewald
 /( )\ www.aims.ac.za
 ^^-^^

[Thomas Goirand (thomas-goirand)]

On Fri Feb  1 2013 02:27:21 PM CST, Jan Groenewald <email address hidden> wrote:

> This bug persists for me on 12.04

hi,

please post you /etc/default/automysqlbackup, and
tell which version of automysqlbackup you use.

Thomas

[Jan Groenewald (jan-aims)]

Sorry, I am not using automysqlbackup, we have a few line script that does
it, and I somehow thought this was a general mysql (mysqldump, to be
precise) bug.

On 1 February 2013 09:14, Thomas Goirand <email address hidden> wrote:

> On Fri Feb 1 2013 02:27:21 PM CST, Jan Groenewald <email address hidden>
> wrote:
>
> > This bug persists for me on 12.04
>
> hi,
>
> please post you /etc/default/automysqlbackup, and
> tell which version of automysqlbackup you use.
>
> Thomas
>
> --
> You received this bug notification because you are a member of AIMS,
> which is subscribed to the bug report.
> https://bugs.launchpad.net/bugs/995587
>
> Title:
> mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user
> 'debian-sys-maint'@'localhost' for table 'cond_instances' when using
> LOCK TABLES
>
> Status in “automysqlbackup” package in Ubuntu:
> Confirmed
>
> Bug description:
> Release: 12.04
> automysqlbackup:
> Installed: 2.6+debian-2
> Candidate: 2.6+debian-2
> Version table:
> *** 2.6+debian-2 0
> 500 http://us.archive.ubuntu.com/ubuntu/ precise/universe amd64
> Packages
> 100 /var/lib/dpkg/status
>
>
>
> After a remove --purge, reinstall of automysqlbackup, and configure in
> /etc/defaults/automysqlbackup, I am emailed the following error when cron
> runs /usr/sbin/automysqlbackup:
>
> mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user
> 'debian-sys-maint'@'localhost' for table 'cond_instances' when using
> LOCK TABLES
>
> it seems that the default on ubuntu is to use the user debian-sys-
> maint to run this backup, however debian-sys-maint doesn't have full
> permissions. Some say that these tables are not necessary to backup
> and recommend the flag " --skip-add-locks" OR "--skip-lock-tables":
>
> http://serverfault.com/questions/268566/cant-backup-the-mysql-table-
> with-mysqldump-select-lock-tabl-command-denied-for
>
> I managed to get the error to go away by adding "--skip-lock-tables"
> to /usr/sbin/automysqlbackup:
>
>
> if [ -z "${USERNAME}" -o -z "${PASSWORD}" ] ; then
> mysqldump --defaults-file=/etc/mysql/debian.cnf $NEWOP\
> T --skip-lock-tables $1 > $2
> else
> mysqldump --user=$USERNAME --password=$PASSWORD --host\
> =$DBHOST --skip-lock-tables $NEWOPT $1 > $2
> fi
> return 0
>
> To manage notifications about this bug go to:
>
> https://bugs.launchpad.net/ubuntu/+source/automysqlbackup/+bug/995587/+subscriptions
>
> --
> Mailing list: https://launchpad.net/~aims
> Post to : <email address hidden>
> Unsubscribe : https://launchpad.net/~aims
> More help : https://help.launchpad.net/ListHelp
>

--
  .~.
  /V\ Jan Groenewald
 /( )\ www.aims.ac.za
 ^^-^^

[greg0r (ljunchpad)]

I've got the same issue with version 3.0rc6. Allthough I used the following in automysqlbackup.conf
CONFIG_table_exclude=( 'cond_instances' )

Any ideas?

By the way thank you for this nice piece of sortware. It really helps so much.

[Pander (pander)]

Thomas, it is installed out of the box version 2.6+debian-2 with /etc/default/automysqlbackup

# By default, the Debian version of automysqlbackup will use:
# mysqldump --defaults-file=/etc/mysql/debian.cnf
# but you might want to overwrite with a specific user & pass.
# To do this, simply edit bellow.

# Username to access the MySQL server e.g. dbuser
#USERNAME=`grep user /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`

# Username to access the MySQL server e.g. password
#PASSWORD=`grep password /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`

# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost

# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
# Note that it's absolutely normal that the db named "mysql" is not in this
# list, as it's added later by the script. See the MDBNAMES directives below
# in this file (advanced options).
# This is ONLY a convenient default, if you don't like it, don't complain
# and write your own.
# The following is a quick hack that will find the names of the databases by
# reading the mysql folder content. Feel free to replace by something else.
# DBNAMES=`find /var/lib/mysql -mindepth 1 -maxdepth 1 -type d | cut -d'/' -f5 | grep -v ^mysql\$ | tr \\\r\\\n ,\ `
# This one does a list of dbs using a MySQL statement.
DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | tr \\\r\\\n ,\ `

# Backup directory location e.g /backups
# Folders inside this one will be created (daily, weekly, etc.), and the
# subfolders will be database names. Note that backups will be owned by
# root, with Unix rights 0600.
BACKUPDIR="/var/lib/automysqlbackup"

# Mail setup
# What would you like to be mailed to you?
# - log : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
# - quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT="quiet"

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see
# docs])
MAXATTSIZE="4000"

# Email Address to send mail to? (<email address hidden>)
MAILADDR="root"

# ============================================================
# === ADVANCED OPTIONS ( Read the doc's below for details )===
#=============================================================

# List of DBBNAMES for Monthly Backups.
MDBNAMES="mysql $DBNAMES"

# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)
DBEXCLUDE=""

# Include CREATE DATABASE in backup?
CREATE_DATABASE=yes

# Separate backup directory and file for each DB? (yes or no)
SEPDIR=yes

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
DOWEEKLY=6

# Choose Compression type. (gzip or bzip2)
COMP=gzip

# Compress communications between backup server and MySQL server?
COMMCOMP=no

# Additionally keep a copy of the most recent backup in a seperate
# directory.
LATEST=no

# The maximum size of the buffer for client/server communication. e.g. 16MB
# (maximum is 1GB)
MAX_ALLOWED_PACKET=

# For connections to localhost. Sometimes the Unix socket file must...

[Pander (pander)]

Thomas, I think an Ubuntu upgrade was responsible for some errors that have already be fixed expect this one is still a remainder of that. Which right should user debian-sys-maint have on table cond_instances? then I can compare it and probably fix it.

[Thomas Goirand (thomas-goirand)]

root@host /var/lib/mysql# ls -lah performance_schema/cond_instances.frm
-rw-rw---- 1 mysql mysql 8.5K Jun 10 03:13 performance_schema/cond_instances.frm

[Carsten Schmitz (cschmitz)]

Adding this to the DBEXCLUDE configuration setting solved this issue for me:

# List of DBNAMES to EXLUCDE if DBNAMES are set to all (must be in " quotes)
DBEXCLUDE="information_schema performance_schema"

[Mike Taylor (miketay)]

I can confirm this is still an issue. Deckard's solution helped, but it only looks at the DBEXCLUDE parameter if you are also setting the configuration parameter DBNAMES line to "all" in the /etc/default/automysqlbackup file.

By default, the configuration file has these defaults:

 DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | tr \\\r\\\n ,\ `

 DBEXCLUDE="information_schema"

They could be changed to this to fix the issue:

 DBNAMES="all"

 DBEXCLUDE="information_schema performance_schema"