From: Rick James
Date: February 4 2013 9:07pm
Subject: RE: Mysqldump routines dump, problem with lock tables.
List-Archive: http://lists.mysql.com/mysql/228917
Message-Id: <582AFBFC517D194489EF570FE21694CF13609CD3@GQ1-EX10-MB03.y.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

Do not try to dump or reload information_schema.  It is derived meta inform=
ation, not real tables.

> -----Original Message-----
> From: Rafa=B3 Radecki [mailto:radecki.rafal@stripped]
> Sent: Monday, February 04, 2013 12:17 AM
> To: mysql@stripped
> Subject: Mysqldump routines dump, problem with lock tables.
>=20
> Hi All.
>=20
> I use:
>=20
> # rpm -qa | grep -i percona-server-server
> Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64
>=20
> My system:
>=20
> # uname -a;cat /etc/redhat-release
> Linux prbc01.mg.local 2.6.32-279.19.1.el6.centos.plus.x86_64 #1 SMP Wed
> Dec 19 06:20:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux Red Hat
> Enterprise Linux Server release 6.3 (Santiago)
>=20
> I have a backup script which at some point calls:
>=20
> mysqldump --default-character-set=3Dutf8 --routines --no-data --no-
> create-info --skip-triggers -S /mysql/database.sock -u backup -pxxx
> database
>=20
> and I have error:
>=20
> mysqldump: Got error: 1045: Access denied for user 'yyy'@'zzz' (using
> password: YES) when using LOCK TABLES
>=20
> So I thinke that mysqldump locks the table (--add-locks) by default.
>=20
> But for this user:
>=20
> mysql> show grants for yyy@'zzz';
> +----------------------------------------------------------------------
> -----------------------------------------------------------------------
> ---+
> | Grants for backup@localhost
>=20
>    |
> +----------------------------------------------------------------------
> -----------------------------------------------------------------------
> ---+
> | GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW ON *.* TO 'yyy'@'zzz'
> IDENTIFIED BY PASSWORD ... |
>                                       |
> +----------------------------------------------------------------------
> -----------------------------------------------------------------------
> ---+
> 2 rows in set (0.00 sec)
>=20
> So why is this error showing?
> When I add --single-transaction to mysqldump everything is ok. But I
> would like to have this table locked because:
>=20
> mysql> SELECT ENGINE
>     -> FROM information_schema.TABLES
>     -> WHERE TABLE_SCHEMA =3D 'information_schema'
>     -> AND TABLE_NAME =3D 'routines';
> +--------+
> | ENGINE |
> +--------+
> | MyISAM |
> +--------+
>=20
> so information_schema.tables is myisam.
>=20
> So why do I get the error about LOCK TABLES?
>=20
> Best regards,
> Rafal Radecki.
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql