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 18:05	Modified:	3 Feb 2009 14:08
Reporter:	Hema Sridharan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.1,6.0 BZR	OS:	Linux
Assigned to:	Davi Arnaut	CPU Architecture:	Any

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.

It is helpful to know how to avoid now, but I think this is an issue because a user shouldn't have to know this special trick to just to use -A. 

Thanks,

Eric