MySQL Bugs: #37748: mysqldump shows misleading error messages when backingup mysql database

Bug #37748	mysqldump shows misleading error messages when backingup mysql database
Submitted:	30 Jun 2008 20:05	Modified:	3 Feb 15:08
Reporter:	Hema Sridharan
Status:	Not a Bug
Category:	Client	Severity:	S3 (Non-critical)
Version:	5.1,6.0 BZR	OS:	Linux
Assigned to:	Davi Arnaut	Target Version:	5.1.32, 6.0+
Triage:	Needs Triage: D3 (Medium)

Description:
1) While trying to backup mysql database using mysqldump, shows the following error
message:
mysqldump: Got error: 1556: You can't use locks with log tables. when using LOCK TABLES

How to repeat:
 ndbdev@ndb17:/data1>
 mysqldump --databases mysql > /data1/backup_dmp/mysql-db.sql -u root --port=9306
--socket=/data2/bzr6.04june/mysql-6.0-backup/mysql-test/var/tmp/master.sock

Suggested fix:
Workaround could be: Using option --lock-tables=0.

But to restore the instances back again, gives the error as shown below.

ndbdev@ndb17:/data1> mysql -u root < /data1/backup_dmp/mysql-db.sql -u root --port=9306
--socket=/data2/bzr6.04june/mysql-6.0-backup/mysql-test/var/tmp/master.sock
ERROR 1556 (HY000) at line 361: You can't use locks with log tables.

Thank you for the report.

Verified as described. Probably duplicate of bug #34306.

Test case:

select 1;

--exec $MYSQL_DUMP mysql general_log

Options:

--log-output=TABLE --log

I ran into similar problem but it have to do with loading the data back into Mysql. When I
try to load data from a mysqldump taken from another machine (same  os and hardware), I
receive this error:

[root@green old]# mysql -u root -p directory < slave_blue.sql
Enter password:
ERROR 1556 (HY000) at line 2537: You can't use locks with log tables.

How do I resolve this problem ?
How do I load data into this server if I cannot get around the problem of ERROR 1556
(HY000)and is there anything in my my.cnf that may have triggger this bug ?

Log tables do not quite belong to mysql database - in future they will be moved to
performance_schema database (see Bug#31764).

When you issue 
mysqldump mysql
log tables are skipped automatically. 
This works with any fairly recent mysqldump (i.e. you need mysqldump from 5.1
installation, the installation which first ships log tables).

When you try mysqldup mysql general_log you need to specify --skip-lock-tables, since
LOCK TABLE statement is not supported with log tables. This is documented, and therefore
this bit of behavior is not a bug.

Please re-triage, and better yet, re-verify: I don't see what can be fixed in this case.

The automatic skipping of log tables in the dump was added in scope of the fix for
Bug#26121

Closing as Not a Bug after the last comments from Konstantin. If one wants to dump a log
table, it's necessary to specify the --skip-lock-tables option.