"SHOW GRANTS FOR CURRENT_USER()"] at /usr/bin/pt-online-schema-change line 4083.

Reported by yvonne on 2013-03-01
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

pt-online-schema-change give error on --exectue
but not on --dru-run
WHY?
]# pt-online-schema-change --version
pt-online-schema-change 2.1.9

Failed to SHOW GRANTS FOR CURRENT_USER(): DBD::mysql::db selectcol_arrayref failed: [34816] syntax error: syntax error near "CURRENT_USER"

 "SHOW GRANTS FOR CURRENT_USER()"] at /usr/bin/pt-online-schema-change line 4083.

# pt-online-schema-change -P3306 --alter "ADD COLUMN client_create_date DATETIME NULL , ADD COLUMN client_update_date DATETIME NULL" --alter-foreign-keys-method=auto -uxxxxx -pxxxx h=127.0.0.1,D=xspace,t=folder --lock-wait-time=360 --execute
Failed to SHOW GRANTS FOR CURRENT_USER(): DBD::mysql::db selectcol_arrayref failed: [34816] syntax error: syntax error near "CURRENT_USER"
LINE: SHOW GRANTS FOR CURRENT_USER()
                      ^ [for Statement "SHOW GRANTS FOR CURRENT_USER()"] at /usr/bin/pt-online-schema-change line 4083.

-----------------------------------------------------------------------

]# pt-online-schema-change -P3306 --alter "ADD COLUMN client_create_date DATETIME NULL , ADD COLUMN client_update_date DATETIME NULL" --alter-foreign-keys-method=auto -uxxxxx -pxxxx h=127.0.0.1,D=xspace,t=folder --lock-wait-time=360 --dry-run
No foreign keys reference `xspace`.`folder`; ignoring --alter-foreign-keys-method.
Starting a dry run. `xspace`.`folder` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table xspace._folder_new OK.
Altering new table...
Altered `xspace`.`_folder_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
Dropping new table...
Dropped new table OK.
Dry run complete. `xspace`.`folder` was not altered.
#

Daniel Nichter (daniel-nichter) wrote :

Which version of MySQL?

tags: added: crash privs pt-online-schema-change
Daniel Nichter (daniel-nichter) wrote :

This may be a duplicate of bug 1133230.

yvonne (mysql-girl) wrote :

pt-show-grants works

mysyl db version 5.1.64

Daniel Nichter (daniel-nichter) wrote :

I think "SHOW GRANTS FOR CURRENT_USER()" ran on a slave. What are the MySQL versions of your slaves?

yvonne (mysql-girl) wrote :

UPDATE
it was not running on a slave - I ran it on a master
since last week
I have upgraded mysql 5.1.61 to Percona 5.5.28
Still get the same error
BTW - pt-show-grants works fine

===================================

[root@sjcstgdbm06 6.34]# pt-online-schema-change -P3306 --alter "ADD COLUMN client_create_date DATETIME NULL" --alter-foreign-keys-method=auto -uxxxx -pxxxx h=127.0.0.1,D=ye,t=backup_test_folder_2012 --nocheck-replication-filters --lock-wait-time=360 --execute
Failed to SHOW GRANTS FOR CURRENT_USER(): DBD::mysql::db selectcol_arrayref failed: [34816] syntax error: syntax error near "CURRENT_USER"
LINE: SHOW GRANTS FOR CURRENT_USER()
                      ^ [for Statement "SHOW GRANTS FOR CURRENT_USER()"] at /usr/bin/pt-online-schema-change line 4083.

[root@sjcstgdbm06 6.34]# pt-online-schema-change --version
pt-online-schema-change 2.1.9

--------------
mysql Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1
Connection id: 1248017
Current database:
Current user: dbadmin@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.28-rel29.1-log Percona Server with XtraDB (GPL), Release rel29.1, Revision 334
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 2 days 17 hours 13 min 43 sec

yvonne (mysql-girl) wrote :

UPDATE
Found another pt tool that also doesn't work - similar error
# pt-slave-find -uxxxx -pxxxxx
Failed to SHOW GRANTS FOR CURRENT_USER(): DBD::mysql::db selectcol_arrayref failed: [34816] syntax error: syntax error near "CURRENT_USER"
LINE: SHOW GRANTS FOR CURRENT_USER()
                      ^ [for Statement "SHOW GRANTS FOR CURRENT_USER()"] at /usr/bin/pt-slave-find line 2127.

Yvonne, Is replication running?

Daniel Nichter (daniel-nichter) wrote :

Yvonne,

I don't think this is a bug in any tool, but some local MySQL issue. It works on stock MySQL 5.5 for me:

$ bin/pt-slave-find -h 127.1 -u msandbox -p msandbox -P 12345
127.1:12345
Version 5.5.29-log
Server ID 12345
Uptime 00:34 (started 2013-03-13T10:33:27)
Replication Is not a slave, has 1 slaves connected, is not read_only
...

We regularly test all the tools on MySQL 5.0 - 5.6 and this has never happened. So the evidence points to a local MySQL issue. I thought maybe a privileges problem, but http://dev.mysql.com/doc/refman/5.0/en/show-grants.html says:

"""
SHOW GRANTS requires the SELECT privilege for the mysql database, except to see the privileges for the current user.
"""

So "SHOW GRANTS FOR CURRENT_USER()" should always work.

It's a very odd problem: by all accounts it shouldn't happen, but it is. Since I feel confident that it's not a bug in the tools and I can't reproduce it, I'm going to mark this bug as Incomplete for now. Please try to debug locally and either isolate the cause or provide a way for me to re-create this error.

Changed in percona-toolkit:
status: New → Incomplete
tags: added: pt-slave-find
yvonne (mysql-girl) wrote :

confirmed - you was right - there was something wrong with my environment ! :(

when I tried with another super user [root] it worked so it's the user it self that has something wrong/missing

The only differences between the two super users accounts are

root has : | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

You can close this bug
thx
-y-

[root@ ~]# pt-slave-find -uroot -pxxxx
Cannot connect to h=10.30.28.84,p=...,u=root
Cannot connect to h=stg-rdb-v3.com,p=...,u=root
Localhost
Version 5.5.28-rel29.1-log
Server ID 513
Uptime 11+20:13:34 (started 2013-03-01T18:55:41)
Replication Is not a slave, has 2 slaves connected, is not read_only
Filters
Binary logging STATEMENT
Slave status
Slave mode STRICT
Auto-increment increment 2, offset 1
InnoDB version 1.1.8-rel29.1

[root@~]# pt-slave-find -udbadmin -pxxxxx
Failed to SHOW GRANTS FOR CURRENT_USER(): DBD::mysql::db selectcol_arrayref failed: [34816] syntax error: syntax error near "CURRENT_USER"
LINE: SHOW GRANTS FOR CURRENT_USER()
                      ^ [for Statement "SHOW GRANTS FOR CURRENT_USER()"] at /usr/bin/pt-slave-find line 2127.
[root@ ~]#

mysql root > SHOW GRANTS FOR CURRENT_USER();
+----------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 'CE99A9DAA' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

mysql dbadmin ((none))>> SHOW GRANTS FOR CURRENT_USER();
+------------------------------------------------------------------------------------------------------------------------------------+
| Grants for dbadmin@localhost |
+------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dbadmin'@'localhost' IDENTIFIED BY PASSWORD '*FC670BC4C' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Thanks for letting us know, Yvonne.

Changed in percona-toolkit:
status: Incomplete → Invalid
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers