CODEWITHSUNDEEP
mysqlsqlphpmyadmin
AngryAce
AngryAce

Reputation: 45

#1227 - Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation

I am following the new boston mysql tutorials, and at the very beginning he is writing SHOW DATABASES. In my case I get the following error

"#1227 - Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation".

I am very new to phpmyadmin and I have googled how to grant a permission but I am unlucky

Upvotes: 2

Views: 38463

Answers (2)

Jaime Montoya
Jaime Montoya

Reputation: 7701

In my case, this is the syntax I was using to import the database:

mysql -u jaimemontoya -pMyPasswordHere -h [name_here].ondigitalocean.com -P [port_number_here] -D cloned_db < cloned_db.sql

This was the error:

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

The root cause of the problem was that in my cloned_db.sql file, I had the following lines:

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'aef25hke-65h7-35jk-s54k-4567g3f6437e:3-69538';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

I commented out those lines and executed again mysql -u jaimemontoya -pMyPasswordHere -h [name_here].ondigitalocean.com -P [port_number_here] -D cloned_db < cloned_db.sql. The database was imported successfully.

Upvotes: 1

Harry
Harry

Reputation: 11648

You need to have that privilege granted to the user ie

GRANT SHOW DATABASES ON *.* TO $username_here

The docs for this can be found here

http://dev.mysql.com/doc/refman/5.7/en/grant.html

Someone here also had a similar problem with PHPMYADMIN.

how to add super privileges to mysql database?

Depending on your username you can also use this from the console

GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password_asljk8902314';
flush privileges;

Please make sure your username is correct.

Upvotes: 2

Related Questions