Bug #26121 mysqldump includes LOCK TABLES general_log WRITE
Submitted: 6 Feb 2007 19:06 Modified: 24 Apr 2007 1:29
Reporter: Sarah Sproehnle Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Logging Severity:S3 (Non-critical)
Version:5.1.15-BK, 5.1.14-beta OS:Linux (Linux, Windows)
Assigned to: Kristofer Pettersson CPU Architecture:Any

[6 Feb 2007 19:06] Sarah Sproehnle
Description:
I used mysqldump with the following command:
mysqldump --all-databases

During restore, I get an error:
"ERROR 1542 (HY000) at line 173: You can't write-lock a log table. Only read access is possible"

The part of the dumpfile causes the error is:

--
-- Dumping data for table `general_log`
--

LOCK TABLES `general_log` WRITE;

How to repeat:
mysqldump --all-databases > dump.sql
mysql <dump.sql
[7 Feb 2007 12:14] Valeriy Kravchuk
Thank you for a bug report. Verified with latest 5.1.15-BK on Linux:

openxs@suse:~/dbs/5.1> bin/mysqldump -uroot --all-databases | grep general_log
-- Table structure for table `general_log`
DROP TABLE IF EXISTS `general_log`;
CREATE TABLE `general_log` (
-- Dumping data for table `general_log`
LOCK TABLES `general_log` WRITE;
/*!40000 ALTER TABLE `general_log` DISABLE KEYS */;
INSERT INTO `general_log` VALUES ('2006-12-30 12:19:19','root[root] @ localhost
[]',1,0,'Query','set names binary'),('2006-12-30 12:19:19','root[root] @ localho
st []',1,0,'Query','select _koi8r\'test\''),('2006-12-30 12:19:19','root[root] @
 localhost []',1,0,'Query','flush logs'),('2006-12-30 12:19:19','root[root] @ lo
calhost []',1,0,'Query','select * from general_log'),('2006-12-30 12:19:19','roo
t[root] @ localhost []',1,0,'Quit',''),('2006-12-30 13:06:42','root[root] @ loca
lhost []',2,0,'Connect','root@localhost on '),('2006-12-30 13:06:42','root[root]
 @ localhost []',2,0,'Query','SHOW VARIABLES LIKE \'pid_file\''),('2006-12-30 13
:06:42','root[root] @ localhost []',2,0,'Shutdown','');
/*!40000 ALTER TABLE `general_log` ENABLE KEYS */;

So, we do have this LOCK TABLES `general_log` WRITE; in the dump.
[5 Apr 2007 12:43] Alexander Barkov
The patch looks ok.
Consider adding "rm" for the temporary file,
like other tests do.
[5 Apr 2007 13:53] Chuck Bell
Patch tested and works well.
[23 Apr 2007 7:29] Bugs System
Pushed into 5.1.18-beta
[24 Apr 2007 1:29] Paul Dubois
Noted in 5.1.18 changelog.

mysqldump could not dump the log tables.
[27 Apr 2007 12:33] 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/25603

ChangeSet@1.2512, 2007-04-27 14:50:01+02:00, tomas@whalegate.ndb.mysql.com +2 -0
  Bug#26121 mysqldump includes LOCK TABLES general_log WRITE
  (backport to telco 6.1)
  - Giving the directive '--all-databases' to mysqldump caused 
    an attempt to lock and dump log tables which don't support this
    operation.
  - With this patch the log tables are excluded from the set of
    databases tables to dump.