MySQL Bugs: #21527: mysqldump incorrectly tries to LOCK TABLES on the information

Bug #21527	mysqldump incorrectly tries to LOCK TABLES on the information_schema database.
Submitted:	8 Aug 2006 23:12	Modified:	30 Aug 2006 20:43
Reporter:	Iggy Galarza	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.0BK	OS:	Linux (Suse Linux)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
When attempting to complete a full backup with a command like:

iggy@rolltop:/src/mysql-5.0-maint/client$ ./mysqldump -S ../mysql-test/var/tmp/master.sock -u root --port=10020 --all-databases --add-drop-table > dump.sql

mysqldump incorrectly tries to LOCK TABLES on the information_schema database, and gives this error messages mid-backup:

mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

Note - This behaviour was discovered while working on bug#21424. The steps to reproduce are the same.

mysql> select @@version;
+------------------+
| @@version        |
+------------------+
| 5.0.25-debug-log |
+------------------+

How to repeat:
#Do As Root
CREATE DATABASE myDB;
use myDB;

GRANT CREATE VIEW, SELECT ON myDB.* TO
        'create'@'localhost' IDENTIFIED BY 'create';

CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (30);

#do as user 'create'
CREATE VIEW v1 (c1) AS SELECT * FROM t1;

./mysqldump -S ../mysql-test/var/tmp/master.sock -u root --port=10020 --all-databases --add-drop-table > dump.sql

Suggested fix:
Before each database's views are dumped, mysqldump should verify that it isn't in the information_schema database.

Thank you for the bug report. Verified as described.

miguel@hegel:~/dbs/5.0> bin/mysqldump --all-databases --add-drop-table > dump.sql -uroot
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

I have discovered the same behaviour on Windows (with 5.0.24).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10938

ChangeSet@1.2254, 2006-08-28 12:41:02-04:00, iggy@rolltop.ignatz42.dyndns.org +3 -0
  Bug #21527 mysqldump incorrectly tries to LOCK TABLES on the information_schema database.
      
  init_dumping now accepts a function pointer to the table or view specific init_dumping function.  This allows both tables and views to use the init_dumping function.

OK to push, as discussed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10957

ChangeSet@1.2254, 2006-08-28 17:48:06-04:00, iggy@rolltop.ignatz42.dyndns.org +3 -0
  Bug #21527 mysqldump incorrectly tries to LOCK TABLES on the information_schema database.
      
  init_dumping now accepts a function pointer to the table or view specific init_dumping function.  This allows both tables and views to use the init_dumping function.

Available in 5.0.25 and 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.

mysqldump incorrectly tried to use LOCK TABLES for tables in the
INFORMATION_SCHEMA database.

mysqldump on xubuntu 6.06 also fails with 

mysqldump: Got error: 1044: Access denied for user: 'myuser' to database 'mydatabase' when using LOCK TABLES

when using it to try to dump a database from an old version of mysql server

CLIENT

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i486) using readline 5.1

SERVER

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 4.0.17-standard-log |
+---------------------+
1 row in set (0.00 sec)

Has this bug made it again into newer releases?

# /usr/local/mysql/bin/mysql --version
/usr/local/mysql/bin/mysql  Ver 14.12 Distrib 5.0.33, for pc-linux-gnu (i686) using  EditLine wrapper

When doing

# /usr/local/mysql/bin/mysqldump --default-character-set=latin1 --all-databases --complete-insert --user=root --password=... > mysql.dump

I receive this error message.

have you tried "--skip-lock-tables"

mysqldump -h myhost -u myuser -p --no-data --skip-lock-tables mydb mytable ?

It works with "--skip-lock-tables"
Is this normal, or a bug?

It's normal.
Because the user you try to dump doens't have permission to lock a table.

It is the MySQL root user...

This "fix" completely disabled dumping of the INFORMATION_SCHEMA database. Bug #33762 deals with this issue now.