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

[8 Aug 2006 23:12] Iggy Galarza
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.
[9 Aug 2006 18:57] Miguel Solorzano
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
[14 Aug 2006 21:06] Markus Popp
I have discovered the same behaviour on Windows (with 5.0.24).
[28 Aug 2006 16:40] Bugs System
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.
[28 Aug 2006 17:18] Timothy Smith
OK to push, as discussed.
[28 Aug 2006 21:47] Bugs System
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.
[29 Aug 2006 14:31] Iggy Galarza
Available in 5.0.25 and 5.1.12
[30 Aug 2006 20:43] Paul Dubois
Noted in 5.0.25, 5.1.12 changelogs.

mysqldump incorrectly tried to use LOCK TABLES for tables in the
INFORMATION_SCHEMA database.
[15 Nov 2006 11:55] clive darra
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)
[31 Jan 2007 7:05] Florian Effenberger
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.
[3 May 2007 11:37] clive darra
have you tried "--skip-lock-tables"

mysqldump -h myhost -u myuser -p --no-data --skip-lock-tables mydb mytable ?
[3 May 2007 15:11] Florian Effenberger
It works with "--skip-lock-tables"
Is this normal, or a bug?
[25 Feb 2008 12:52] Tiago Fischer
It's normal.
Because the user you try to dump doens't have permission to lock a table.
[25 Feb 2008 12:53] Florian Effenberger
It is the MySQL root user...
[1 May 2008 21:16] Jim Winstead
This "fix" completely disabled dumping of the INFORMATION_SCHEMA database. Bug #33762 deals with this issue now.