FIX: "Access denied...when using LOCK TABLES"

[boodle]

Error:
"mysqldump: Got error: 1044: Access denied for user 'XXX'@'localhost' to database 'XXX' when using LOCK TABLES"

find line in script:

# Database dump function
dbdump () {
mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2
return 0
}

change to:

# Database dump function
dbdump () {
mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT --skip-opt $1 > $2
return 0
}

Here is the explaination in brief from (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html) (worth the read).
p.s. you can selectively turn options on or off.

To get a list of the options your version of mysqldump supports, execute mysqldump --help.

Some mysqldump options are shorthand for groups of other options. --opt and --compact fall into this category. For example, use of --opt is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. Note that all of the options that --opt stands for also are on by default because --opt is on by default.

To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:

    *

      To select the effect of --opt except for some features, use the --skip option for each feature. For example, to disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (As of MySQL 5.0, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)
    *

      To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.

When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself.

mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). --opt (and hence --quick) is enabled by default as of MySQL 5.0 to enable memory buffering, use --skip-quick.

If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, you should not use the --opt or --extended-insert option. Use --skip-opt instead.

boodle

[boodle]

Can also do it like this: (using --skip-lock-tables)

find line in script: 

# Database dump function 
dbdump () { 
mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2 
return 0 
} 

change to: 

# Database dump function 
dbdump () { 
mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT --skip-lock-tables $1 > $2

return 0 
} 

[solaceten]

Hi

I also get the lock tables error

I tried both fixes above and then I get this error

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `CLIENT_STATISTICS`': Access denied; you need the PROCESS,SUPER privilege for this operation (1227)

Any advice?

Thanks

[fullmoonguru]

Sorry if this seems like a dumb question but which script is this in?  I assume it's mysqldump but I can't find the location in Ubuntu 12.04.

[avibodha]

or you can do it this way:

(around line .438)
parse_configuration () {
    # OPT string for use with mysqldump ( see man mysqldump )
    opt=( '-quote-names'  '-opt'  '-skip-lock-tables' )

    # OPT string for use with mysql (see man mysql )
    mysql_opt=(  )

    # OPT string for use with mysqldump fullschema
    opt_fullschema=( '-all-databases'   '-routines'   '-no-data'   '-skip-lock-tables'  )

[ramonchis]

I found the same issue and solved it by adding the following privileges to the user

GRANT SELECT ON . TO 'dbbackup'@'localhost';
GRANT FILE ON . TO 'dbbackup'@'localhost';
GRANT SHOW VIEW ON . TO 'dbbackup'@'localhost';
GRANT LOCK TABLES ON . TO 'dbbackup'@'localhost';

And then

FLUSH PRIVILEGES