Re: ERROR: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

[Johan Daine]

I got the same problem, resolved usiong the '--single-transaction' option of
mysqldump. Seems not to be documented on older versions (see below)
johan@nostradamus:~$ mysql -p dev
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21061 to server version: 4.1.11-Debian_4sarge5-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> Bye
johan@nostradamus:~$ mysqldump --single-transaction -u bxcms -l -p dev > foo
Enter password:
johan@nostradamus:~$ head foo
-- MySQL dump 10.9
--
-- Host: localhost Database: dev
-- ------------------------------------------------------
-- Server version 4.1.11-Debian_4sarge5-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

johan@nostradamus:~$ mysqldump|grep single
johan@nostradamus:~$ mysqldump --version
mysqldump Ver 10.9 Distrib 4.1.11, for pc-linux-gnu (i386)


OTOH
on Debian Etch
[johand@horus]~ $mysqldump --version
mysqldump Ver 10.10 Distrib 5.0.24, for pc-linux-gnu (i486)
[johand@horus]~ $mysqldump --help|grep -- '--single'
whole dump. Automatically turns --single-transaction and
--lock-all-tables on, unless --single-transaction is
forget to read about --single-transaction below). In all
--single-transaction

Hope this helps
Johan