upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command denied

i use ZRM to do backups of my databases.

with v5.1.x, this works fine,

mysql-zrm-scheduler --now --backup-set manual --backup-level 0

to execute a manual backup.

i recently upgraded from v5.1.x -> v5.5.12,

mysqladmin -V
mysqladmin Ver 8.42 Distrib 5.5.12, for Linux on i686

now, at exec of that backup cmd, i see an ERROR @ console,

...
manual:backup:INFO: PHASE START: Creating raw backup
manual:backup:INFO: Command used for raw backup is
/usr/share/mysql-zrm/plugins/socket-copy.pl
--mysqlhotcopy=/usr/bin --host="localhost" --port="3306"
--socket="/var/cache/mysql/mysql.sock" --quiet mysql
"/var/mysql-bkup/manual/20110605131003" >
/var/cache/tmp/bZvaQFwQY2 2>&1
manual:backup:INFO: raw-databases=mysql
manual:backup:INFO: PHASE END: Creating raw backup
manual:backup:INFO: PHASE START: Creating logical backup
manual:backup:WARNING: The database(s) drupal6
performance_schema will be backed up in logical mode since they
contain tables that use a transactional engine.
manual:backup:INFO: Command used for logical backup is
"/usr/bin"/mysqldump --opt --extended-insert --create-options
--default-character-set=utf8 --routines --host="localhost"
--port="3306" --socket="/var/cache/mysql/mysql.sock" --databases
drupal6 performance_schema >
"/var/mysql-bkup/manual/20110605131003/backup.sql"
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to
user 'root'@'localhost' for table 'cond_instances' when using
LOCK TABLES
--> manual:backup:ERROR: mysqldump did not succeed.
Command used is "/usr/bin"/mysqldump --opt --extended-insert
--create-options --default-character-set=utf8 --routines
--host="localhost" --port="3306"
--socket="/var/cache/mysql/mysql.sock" --databases drupal6
performance_schema >
"/var/mysql-bkup/manual/20110605131003/backup.sql"manual:bac kup:INFO:
PHASE START: Cleanup
manual:backup:INFO: backup-status=Backup failed
...


reading up on the error at,

http://bugs.mysql.com/bug.php?id=33762
http://bugs.mysql.com/bug.php?id=49633

it looks to do with mysqldump itself. i modified in /etc/my.cnf

...
[mysqldump]
quick
quote-names
max_allowed_packet = 8M
+ skip-lock-tables
...

but that doesn't seem to make any difference.

something's changed between 5.1.x & 5.5.x.

what do i need to modify to get past this error?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

--------------enig3F5775E9F42C505E4C6EA174
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

have you checked you permissions-table if all privileges are active for r=
oot
and have you started "ymsql_upgrade" after all updates?

Am 05.06.2011 22:20, schrieb agd85@airpost.net:
> i use ZRM to do backups of my databases.
>=20
> with v5.1.x, this works fine,
>=20
> mysql-zrm-scheduler --now --backup-set manual --backup-level 0
>=20
> to execute a manual backup.
>=20
> i recently upgraded from v5.1.x -> v5.5.12,
>=20
> mysqladmin -V
> mysqladmin Ver 8.42 Distrib 5.5.12, for Linux on i686
>=20
> now, at exec of that backup cmd, i see an ERROR @ console,
>=20
> ...
> manual:backup:INFO: PHASE START: Creating raw backup
> manual:backup:INFO: Command used for raw backup is
> /usr/share/mysql-zrm/plugins/socket-copy.pl
> --mysqlhotcopy=3D/usr/bin --host=3D"localhost" --port=3D"3306"
> --socket=3D"/var/cache/mysql/mysql.sock" --quiet mysql=20
> "/var/mysql-bkup/manual/20110605131003" >
> /var/cache/tmp/bZvaQFwQY2 2>&1
> manual:backup:INFO: raw-databases=3Dmysql
> manual:backup:INFO: PHASE END: Creating raw backup
> manual:backup:INFO: PHASE START: Creating logical backup
> manual:backup:WARNING: The database(s) drupal6
> performance_schema will be backed up in logical mode since they
> contain tables that use a transactional engine.
> manual:backup:INFO: Command used for logical backup is
> "/usr/bin"/mysqldump --opt --extended-insert --create-options
> --default-character-set=3Dutf8 --routines --host=3D"localhost"
> --port=3D"3306" --socket=3D"/var/cache/mysql/mysql.sock" --databases
> drupal6 performance_schema >
> "/var/mysql-bkup/manual/20110605131003/backup.sql"
> mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to
> user 'root'@'localhost' for table 'cond_instances' when using
> LOCK TABLES
> --> manual:backup:ERROR: mysqldump did not succeed.
> Command used is "/usr/bin"/mysqldump --opt --extended-insert
> --create-options --default-character-set=3Dutf8 --routines
> --host=3D"localhost" --port=3D"3306"
> --socket=3D"/var/cache/mysql/mysql.sock" --databases drupal6
> performance_schema >
> "/var/mysql-bkup/manual/20110605131003/backup.sql"manual:bac kup:INFO:=

> PHASE START: Cleanup
> manual:backup:INFO: backup-status=3DBackup failed
> ...
>=20
>=20
> reading up on the error at,
>=20
> http://bugs.mysql.com/bug.php?id=3D33762
> http://bugs.mysql.com/bug.php?id=3D49633
>=20
> it looks to do with mysqldump itself. i modified in /etc/my.cnf
>=20
> ...
> [mysqldump]
> quick
> quote-names
> max_allowed_packet =3D 8M
> + skip-lock-tables
> ...
>=20
> but that doesn't seem to make any difference.
>=20
> something's changed between 5.1.x & 5.5.x.
>=20
> what do i need to modify to get past this error?
>=20

--=20

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm


--------------enig3F5775E9F42C505E4C6EA174
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3r5g8ACgkQhmBjz394AnlRxACbBu7z2Q/Rr3if5eSgz+u9 kB3T
desAnjPLha/fnmMZb9tr/KSi2AHDAcu8
=GeWe
-----END PGP SIGNATURE-----

--------------enig3F5775E9F42C505E4C6EA174--

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

hi,

On Sun, 05 Jun 2011 22:24 +0200, "Reindl Harald"
wrote:
> have you checked you permissions-table if all privileges are active for root

i've got,

mysql> show grants for 'root'@'localhost';
+----------------------------------------------------------- ------------------------------------------------------------ -----------------+
| Grants for root@localhost

|
+----------------------------------------------------------- ------------------------------------------------------------ -----------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED
BY PASSWORD '*3...4' WITH GRANT OPTION
|
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

|
+----------------------------------------------------------- ------------------------------------------------------------ -----------------+

and,

mysql> show grants for 'drupal_admin'@'localhost';
+----------------------------------------------------------- ------------------------------------------------------------ -------------------------------------------+
| Grants for drupal_admin@localhost

|
+----------------------------------------------------------- ------------------------------------------------------------ -------------------------------------------+
| GRANT USAGE ON *.* TO 'drupal_admin'@'localhost' IDENTIFIED BY
PASSWORD '*D...D'
|
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX,
ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `drupal6`.* TO
'drupal_admin'@'localhost' |
+----------------------------------------------------------- ------------------------------------------------------------ -------------------------------------------+
17 rows in set (0.00 sec)

are these sufficient? these permissions worked fine as far as i could
tell for the v5.1.x install i had.

> and have you started "ymsql_upgrade" after all updates?

yes, i'd already executed 'mysql_upgrade', following the instructions
here:
http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previo us-series.html

checking,

mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.5.12, use --force
if you still need to run mysql_upgrade


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

--------------enigFE75633F14656D9378F3457C
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

the grant statements does nobody interest
maybe use phpmyadmin for a clearer display

mysql> select * from mysql.user where user=3D'root' limit 1;
+-----------+------+---------------------------------------- ---+---------=
----+-------------+-------------+-------------+------------- +-----------+=
-------------+---------------+--------------+-----------+--- ---------+---=
--------------+------------+------------+--------------+---- --------+----=
-------------------+------------------+--------------+------ -----------+-=
-----------------+------------------+----------------+------ -------------=
--+--------------------+------------------+------------+---- ----------+--=
----------------------+----------+------------+------------- +------------=
--+---------------+-------------+-----------------+--------- -------------=
+--------+-----------------------+
| Host | User | Password | Select_p=
riv | Insert_priv | Update_priv |
Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Pro=
cess_priv | File_priv | Grant_priv |
References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | C=
reate_tmp_table_priv | Lock_tables_priv |
Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Sh=
ow_view_priv | Create_routine_priv |
Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Creat=
e_tablespace_priv | ssl_type | ssl_cipher
| x509_issuer | x509_subject | max_questions | max_updates | max_connecti=
ons | max_user_connections | plugin |
authentication_string |
+-----------+------+---------------------------------------- ---+---------=
----+-------------+-------------+-------------+------------- +-----------+=
-------------+---------------+--------------+-----------+--- ---------+---=
--------------+------------+------------+--------------+---- --------+----=
-------------------+------------------+--------------+------ -----------+-=
-----------------+------------------+----------------+------ -------------=
--+--------------------+------------------+------------+---- ----------+--=
----------------------+----------+------------+------------- +------------=
--+---------------+-------------+-----------------+--------- -------------=
+--------+-----------------------+
| localhost | root | ********* | Y | Y | Y =
| Y | Y | Y |
Y | Y | Y | Y | Y | Y =
| Y | Y | Y
| Y | Y | Y | Y =
| Y | Y
| Y | Y | Y | Y =
| Y | Y
| Y | Y | | | =
| | 0 |
0 | 0 | 0 | | =
|
+-----------+------+---------------------------------------- ---+---------=
----+-------------+-------------+-------------+------------- +-----------+=
-------------+---------------+--------------+-----------+--- ---------+---=
--------------+------------+------------+--------------+---- --------+----=
-------------------+------------------+--------------+------ -----------+-=
-----------------+------------------+----------------+------ -------------=
--+--------------------+------------------+------------+---- ----------+--=
----------------------+----------+------------+------------- +------------=
--+---------------+-------------+-----------------+--------- -------------=
+--------+-----------------------+
1 row in set (0.00 sec)



Am 05.06.2011 23:05, schrieb agd85@airpost.net:
>=20
> hi,
>=20
> On Sun, 05 Jun 2011 22:24 +0200, "Reindl Harald"
> wrote:
>> have you checked you permissions-table if all privileges are active fo=
r root
>=20
> i've got,
>=20
> mysql> show grants for 'root'@'localhost';
> +----------------------------------------------------------- ----------=
------------------------------------------------------------ -------+
> | Grants for root@localhost =20
> =20
> |
> +----------------------------------------------------------- ----------=
------------------------------------------------------------ -------+
> | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED
> BY PASSWORD '*3...4' WITH GRANT OPTION =20
> |
> | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION =20
> =20
> |
> +----------------------------------------------------------- ----------=
------------------------------------------------------------ -------+


--------------enigFE75633F14656D9378F3457C
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3r8s4ACgkQhmBjz394Anm3OwCfeqyAlGhSi7zFDAqDwRBB yhns
6ngAoJ1qY5K/RfOEoB7ee/poK1XiKAa/
=GO0E
-----END PGP SIGNATURE-----

--------------enigFE75633F14656D9378F3457C--

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

fwiw, others are seeing this. e.g., in addition to the two bugs i'd
already referenced,

http://www.directadmin.com/forum/showthread.php?p=202053

and one

http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with- mysqldump-SELECT-LOCK-TABL-command

claims a solution

"Add --skip-add-locks to your mysqldump command"

which, having added as i mentioned above, to the [mysqldump] section of
/etc/my.cnf, does NOT make a difference for me.


On Sun, 05 Jun 2011 23:19 +0200, "Reindl Harald"
wrote:
> the grant statements does nobody interest
> mysql> select * from mysql.user where user='root' limit 1;


and, my result for your cmd,


mysql> select * from mysql.user where user='root' limit 1;
+-----------+------+---------------------------------------- ---+-------------+-------------+-------------+-------------+ -------------+-----------+-------------+---------------+---- ----------+-----------+------------+-----------------+------ ------+------------+--------------+------------+------------ -----------+------------------+--------------+-------------- ---+------------------+------------------+----------------+- --------------------+--------------------+------------------ +------------+--------------+------------------------+------ ----+------------+-------------+--------------+------------- --+-------------+-----------------+----------------------+-- ------+-----------------------+
| Host | User | Password |
Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv |
Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv
| Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher |
x509_issuer | x509_subject | max_questions | max_updates |
max_connections | max_user_connections | plugin | authentication_string
|
+-----------+------+---------------------------------------- ---+-------------+-------------+-------------+-------------+ -------------+-----------+-------------+---------------+---- ----------+-----------+------------+-----------------+------ ------+------------+--------------+------------+------------ -----------+------------------+--------------+-------------- ---+------------------+------------------+----------------+- --------------------+--------------------+------------------ +------------+--------------+------------------------+------ ----+------------+-------------+--------------+------------- --+-------------+-----------------+----------------------+-- ------+-----------------------+
| localhost | root | *3......................................4 | Y
| Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y |
Y | Y | Y | Y | Y |
Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y
| Y | | | |
| 0 | 0 | 0 |
0 | | NULL |
+-----------+------+---------------------------------------- ---+-------------+-------------+-------------+-------------+ -------------+-----------+-------------+---------------+---- ----------+-----------+------------+-----------------+------ ------+------------+--------------+------------+------------ -----------+------------------+--------------+-------------- ---+------------------+------------------+----------------+- --------------------+--------------------+------------------ +------------+--------------+------------------------+------ ----+------------+-------------+--------------+------------- --+-------------+-----------------+----------------------+-- ------+-----------------------+
1 row in set (0.06 sec)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

--------------enig8372C7F3EBB792F7D8D61765
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

hm - bad

i would use a replication slave and stop him for consistent backups
because dumb locks are not really a good solution independent
if this works "normally"

Am 05.06.2011 23:26, schrieb agd85@airpost.net:
> fwiw, others are seeing this. e.g., in addition to the two bugs i'd
> already referenced,
>=20
> http://www.directadmin.com/forum/showthread.php?p=3D202053
>=20
> and one
>=20
> http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with- mysqldump-=
SELECT-LOCK-TABL-command
>=20
> claims a solution
>=20
> "Add --skip-add-locks to your mysqldump command"
>=20
> which, having added as i mentioned above, to the [mysqldump] section of=

> /etc/my.cnf, does NOT make a difference for me.
>=20
>=20
> On Sun, 05 Jun 2011 23:19 +0200, "Reindl Harald"
> wrote:
>> the grant statements does nobody interest
>> mysql> select * from mysql.user where user=3D'root' limit 1;
>=20
>=20
> and, my result for your cmd,
>=20
>=20
> mysql> select * from mysql.user where user=3D'root' limit 1;
> +-----------+------+---------------------------------------- ---+-------=
------+-------------+-------------+-------------+----------- --+----------=
-+-------------+---------------+--------------+-----------+- -----------+-=
----------------+------------+------------+--------------+-- ----------+--=
---------------------+------------------+--------------+---- -------------=
+------------------+------------------+----------------+---- -------------=
----+--------------------+------------------+------------+-- ------------+=
------------------------+----------+------------+----------- --+----------=
----+---------------+-------------+-----------------+------- -------------=
--+--------+-----------------------+
> | Host | User | Password |
> Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
> Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
> Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |=

> Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
> Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv =
|
> Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_pri=
v
> | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher |
> x509_issuer | x509_subject | max_questions | max_updates |
> max_connections | max_user_connections | plugin | authentication_string=

> |
> +-----------+------+---------------------------------------- ---+-------=
------+-------------+-------------+-------------+----------- --+----------=
-+-------------+---------------+--------------+-----------+- -----------+-=
----------------+------------+------------+--------------+-- ----------+--=
---------------------+------------------+--------------+---- -------------=
+------------------+------------------+----------------+---- -------------=
----+--------------------+------------------+------------+-- ------------+=
------------------------+----------+------------+----------- --+----------=
----+---------------+-------------+-----------------+------- -------------=
--+--------+-----------------------+
> | localhost | root | *3......................................4 | Y =
=20
> | Y | Y | Y | Y | Y =

> | Y | Y | Y | Y | Y |=

> Y | Y | Y | Y | Y |=

> Y | Y | Y | Y =
=20
> | Y | Y | Y | Y =
=20
> | Y | Y | Y | Y =
=20
> | Y | | | | =
=20
> | 0 | 0 | 0 | =
=20
> 0 | | NULL |
> +-----------+------+---------------------------------------- ---+-------=
------+-------------+-------------+-------------+----------- --+----------=
-+-------------+---------------+--------------+-----------+- -----------+-=
----------------+------------+------------+--------------+-- ----------+--=
---------------------+------------------+--------------+---- -------------=
+------------------+------------------+----------------+---- -------------=
----+--------------------+------------------+------------+-- ------------+=
------------------------+----------+------------+----------- --+----------=
----+---------------+-------------+-----------------+------- -------------=
--+--------+-----------------------+
> 1 row in set (0.06 sec)
>=20
>=20

--=20

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm


--------------enig8372C7F3EBB792F7D8D61765
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3r9SQACgkQhmBjz394AnlzCQCfcLyekmm9z5BASqxXZxPg /UXt
nD0An1U6JhtXrY7OX6AJPa6SA2ecKDJo
=vSOU
-----END PGP SIGNATURE-----

--------------enig8372C7F3EBB792F7D8D61765--

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

--------------enig0C3FBD335EF409FD746E78BB
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

BTW
WHY is everybody ansering to the list AND the author of the last post?
this reults in get every message twice :-(

Am 05.06.2011 23:26, schrieb agd85@airpost.net:
> fwiw, others are seeing this. e.g., in addition to the two bugs i'd
> already referenced,
>=20
> http://www.directadmin.com/forum/showthread.php?p=3D202053
>=20
> and one
>=20
> http://qa.lampcms.com/q122897/Can-t-backup-mysql-table-with- mysqldump-=
SELECT-LOCK-TABL-command
>=20
> claims a solution
>=20
> "Add --skip-add-locks to your mysqldump command"
>=20
> which, having added as i mentioned above, to the [mysqldump] section of=

> /etc/my.cnf, does NOT make a difference for me.
>=20
>=20
> On Sun, 05 Jun 2011 23:19 +0200, "Reindl Harald"
> wrote:
>> the grant statements does nobody interest
>> mysql> select * from mysql.user where user=3D'root' limit 1;
>=20
>=20
> and, my result for your cmd,
>=20
>=20
> mysql> select * from mysql.user where user=3D'root' limit 1;
> +-----------+------+---------------------------------------- ---+-------=
------+-------------+-------------+-------------+----------- --+----------=
-+-------------+---------------+--------------+-----------+- -----------+-=
----------------+------------+------------+--------------+-- ----------+--=
---------------------+------------------+--------------+---- -------------=
+------------------+------------------+----------------+---- -------------=
----+--------------------+------------------+------------+-- ------------+=
------------------------+----------+------------+----------- --+----------=
----+---------------+-------------+-----------------+------- -------------=
--+--------+-----------------------+
> | Host | User | Password |
> Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv |
> Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
> Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |=

> Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
> Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv =
|
> Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_pri=
v
> | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher |
> x509_issuer | x509_subject | max_questions | max_updates |
> max_connections | max_user_connections | plugin | authentication_string=

> |
> +-----------+------+---------------------------------------- ---+-------=
------+-------------+-------------+-------------+----------- --+----------=
-+-------------+---------------+--------------+-----------+- -----------+-=
----------------+------------+------------+--------------+-- ----------+--=
---------------------+------------------+--------------+---- -------------=
+------------------+------------------+----------------+---- -------------=
----+--------------------+------------------+------------+-- ------------+=
------------------------+----------+------------+----------- --+----------=
----+---------------+-------------+-----------------+------- -------------=
--+--------+-----------------------+
> | localhost | root | *3......................................4 | Y =
=20
> | Y | Y | Y | Y | Y =

> | Y | Y | Y | Y | Y |=

> Y | Y | Y | Y | Y |=

> Y | Y | Y | Y =
=20
> | Y | Y | Y | Y =
=20
> | Y | Y | Y | Y =
=20
> | Y | | | | =
=20
> | 0 | 0 | 0 | =
=20
> 0 | | NULL |
> +-----------+------+---------------------------------------- ---+-------=
------+-------------+-------------+-------------+----------- --+----------=
-+-------------+---------------+--------------+-----------+- -----------+-=
----------------+------------+------------+--------------+-- ----------+--=
---------------------+------------------+--------------+---- -------------=
+------------------+------------------+----------------+---- -------------=
----+--------------------+------------------+------------+-- ------------+=
------------------------+----------+------------+----------- --+----------=
----+---------------+-------------+-----------------+------- -------------=
--+--------+-----------------------+
> 1 row in set (0.06 sec)
>=20
>=20

--=20

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm


--------------enig0C3FBD335EF409FD746E78BB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3r9VsACgkQhmBjz394AnlX1wCfU7jUkOs8EJ1+TEHW/WWu Q5Qn
Bt4AnRbBJWbCniaUjQwJgNiTB8PggiwJ
=8SUD
-----END PGP SIGNATURE-----

--------------enig0C3FBD335EF409FD746E78BB--

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

On Sun, 05 Jun 2011 23:30 +0200, "Reindl Harald"
wrote:
> BTW
> WHY is everybody ansering to the list AND the author of the last post?
> this reults in get every message twice :-(

Reply -> sends to ONLY the From == h.reindl@thelounge.net
Reply to all sends to BOTH the From == h.reindl@thelounge.net AND the
list.

I suppose if the list manager software, or your client were configured
differently ...


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

HowTo using mailing-lists

--------------enigCB416126AC37A956002F99FE
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable


Am 05.06.2011 23:46, schrieb agd85@airpost.net:
> On Sun, 05 Jun 2011 23:30 +0200, "Reindl Harald"
> wrote:
>> BTW
>> WHY is everybody ansering to the list AND the author of the last post?=

>> this reults in get every message twice :-(
>=20
> Reply -> sends to ONLY the From == h.reindl@thelounge.net
> Reply to all sends to BOTH the From == h.reindl@thelounge.net AND t=
he
> list.
>=20
> I suppose if the list manager software, or your client were configured
> differently ...

and "reply to list" replies only to the mailing list

i got burned down from Wietse Venema himself for using
"reply all" on the postfix-mailing list some times
by accident



--------------enigCB416126AC37A956002F99FE
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3r+dkACgkQhmBjz394AnlFzwCaA5TO95U8MgZzIg5OqT+A KJt4
GVwAn0K1Lf9mJetBsIs96xfO6MNIWPnt
=t+CH
-----END PGP SIGNATURE-----

--------------enigCB416126AC37A956002F99FE--

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

On Sun, 05 Jun 2011 23:29 +0200, "Reindl Harald"
wrote:
> i would use a replication slave and stop him for consistent backups
> because dumb locks are not really a good solution independent
> if this works "normally"

unfortunately, i have no idea what that means.

something's apparently broken with mysqldump -- enough so that lots of
people are seeing and reporting this same error after the 5.1 -> 5.5
upgrade.

why would setting up a replication slave be necessary or a good solution
to the problem?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

--------------enig7A7BCE85AFE8E10FBC5409B1
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 05.06.2011 23:49, schrieb agd85@airpost.net:
>=20
> On Sun, 05 Jun 2011 23:29 +0200, "Reindl Harald"
> wrote:
>> i would use a replication slave and stop him for consistent backups
>> because dumb locks are not really a good solution independent
>> if this works "normally"
>=20
> unfortunately, i have no idea what that means.
>=20
> something's apparently broken with mysqldump -- enough so that lots of
> people are seeing and reporting this same error after the 5.1 -> 5.5
> upgrade.
>=20
> why would setting up a replication slave be necessary or a good solutio=
n
> to the problem?

because there is no lock on any production table?
have fun using mysqldump with really hughe databases :-)

a replication slave is synchron, you can stop the slave, copy
the whole datadir and after starting the slave it will
make all changes from the binary log


--------------enig7A7BCE85AFE8E10FBC5409B1
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3r+qAACgkQhmBjz394AnmjWwCfa8LWXmXCaK473gGf8UA2 cnPw
u50An3+3jeOvYCObN2EvGw7CFltaMq3G
=J9gH
-----END PGP SIGNATURE-----

--------------enig7A7BCE85AFE8E10FBC5409B1--

Re: HowTo using mailing-lists

> and "reply to list" replies only to the mailing list
>
> i got burned down from Wietse Venema himself for using
> "reply all" on the postfix-mailing list some times
> by accident

there's no Reply To List command in this client.

i have no idea who Wietse Venema is.

do you REALLY have a need to have this diatribe ON list?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

i still have no idea why this is necessary.

there seems to be a but, problem, misconfiguration, etc.

wouldn't it make some sense to try to FIX it, rather than setting up a
completely different server?

perhaps someone with an idea of the problem and its solution will be
able to chime in.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: HowTo using mailing-lists

--------------enig911EA4054864B05C296CE4DE
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 05.06.2011 23:52, schrieb agd85@airpost.net:
>> and "reply to list" replies only to the mailing list
>>
>> i got burned down from Wietse Venema himself for using
>> "reply all" on the postfix-mailing list some times
>> by accident
>=20
> there's no Reply To List command in this client.

so please remove the sender and reply only to the list

> i have no idea who Wietse Venema is

you know postfix?
http://www.porcupine.org/wietse/

> do you REALLY have a need to have this diatribe ON list?

YES because in need not all mails twice in my archive and
if have to look which one i use because only the one sent
to the list contains the mail-headers to enable "reply to list"

Mailing-List: contact mysql-help@lists.mysql.com; run by ezmlm
List-ID:
Precedence: bulk
List-Help:
List-Unsubscribe: s.mysql.com>
List-Post:
List-Archive: http://lists.mysql.com/mysql/225174
Delivered-To: mailing list mysql@lists.mysql.com


--------------enig911EA4054864B05C296CE4DE
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3r+/8ACgkQhmBjz394AnlDSwCfYvcpu5CXr3klvIj80U6w sLNt
vBAAmwQwIjjq+Jpt2To2FSogEfXOzzYG
=8vrf
-----END PGP SIGNATURE-----

--------------enig911EA4054864B05C296CE4DE--

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

--------------enigA5BDFAA274E249A406A35D01
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 05.06.2011 23:55, schrieb agd85@airpost.net:
> i still have no idea why this is necessary.

take it or not

it is a professional solution which works for
databses with 20 GB every day here with rsync
without interrupt/lock mysqld a second

and it is much faster

> there seems to be a but, problem, misconfiguration, etc.
> wouldn't it make some sense to try to FIX it, rather than setting up a
> completely different server?

it takes 5 minutes starting a replication salve on the same machine
with its own socket/port

> perhaps someone with an idea of the problem and its solution will be
> able to chime in.

wait until it is fixed or think about a better solution
which will work in the future


--------------enigA5BDFAA274E249A406A35D01
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3r/McACgkQhmBjz394Ann0NwCfZJ7w2lqGFsZbGBU90Z/x WfP3
aH0An3joQMC4OrPEyDZpTgMbRjTr1HEX
=gagV
-----END PGP SIGNATURE-----

--------------enigA5BDFAA274E249A406A35D01--

Re: to the list AND the author

>>>> 2011/06/05 23:30 +0200, Reindl Harald >>>>
BTW
WHY is everybody ans[w]ering to the list AND the author of the last post?
<<<<<<<<
Because it is a damn' nuisance to enter "answer all" and therupon move the list-email-address after "To" (as I now did). It would be easier if the messages contained "Reply-to: mysql@lists.mysql.com" (or maibe something else: maibe a new protocol better fit for lists is generally supported). The message is not so composed that the list is considered the sender whom the answer is due.

And it would have been better to write a separate message for this complaint, instead of including a completely irrelevant message.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command d

I haven't bothered to look for the "bug", but it seems to me to be quite reasonable default behaviour to lock the whole lot when you're dumping transactional tables - it ensures you dump all tables from the same consistent view.

I would rather take this up with the ZRM people - it should "just work". 3.3 came out last week, you may want to have a look at wether it's already been adressed there.

Harald's solution is, as usual, technically superior; but he keeps having trouble understanding people not wanting to change their entire setup because their solution isn't optimal :-)


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

On Mon, 06 Jun 2011 12:44 +0200, "Johan De Meersman"
wrote:
>
> I haven't bothered to look for the "bug", but it seems to me to be quite
> reasonable default behaviour to lock the whole lot when you're dumping
> transactional tables - it ensures you dump all tables from the same
> consistent view.

thanks for the comment.

> I would rather take this up with the ZRM people - it should "just work".

this,

http://bugs.mysql.com/bug.php?id=61414

suggests the same. so, i've already started that discussion as well,

http://forums.zmanda.com/showthread.php?t=3703

Excluding 'performance_schema' appears to eliminate the error. And it
seems does NOT cause a reliability-of-the-backup problem.

> 3.3 came out last week, you may want to have a look at wether it's
> already been adressed there.

I believe that's an Amanda 3.3 release you're referring to. ZRM is still
at 2.2,

http://www.zmanda.com/download-zrm.php
"ZRM for MySQL, Version 2.2 is the Latest Stable Release"

and, i've MySQL-zrm-2.2.0-1.noarch installed.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142: SELECT,LOCK TABL command d

----- Original Message -----
> From: agd85@airpost.net
>
> Excluding 'performance_schema' appears to eliminate the error. And it
> seems does NOT cause a reliability-of-the-backup problem.

Hah, no, backing that up is utterly pointless. Never noticed it doing that.

It's basically a virtual schema that contains realtime information about the database, intended to replace a lot of "show tables" parsing and similar mayhem with simple select statements.


> I believe that's an Amanda 3.3 release you're referring to. ZRM is
> still at 2.2,

No, I do mean 3.3. Apparently the free downloadable version is quite a bit behind the commercial one. Maybe that's why I never noticed it backing up the performance schema, too :-)

They're not really that expensive, either, it might well be worth it to grab licenses and support.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: upgraded from 5.1->5.5. now getting a mysqldump ERROR "1142:SELECT,LOCK TABL command de

On Mon, 06 Jun 2011 18:54 +0200, "Johan De Meersman"
wrote:
> > Excluding 'performance_schema' appears to eliminate the error. And it
> > seems does NOT cause a reliability-of-the-backup problem.
>
> Hah, no, backing that up is utterly pointless.

that's a useful/final confirmation. thx.

> No, I do mean 3.3. Apparently the free downloadable version is quite a
> bit behind the commercial one. Maybe that's why I never noticed it
> backing up the performance schema, too :-)

i didn't catch that ZRM's commercial version was at 3.3! thx.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org