#1142 - SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs'

Z

20

123

Hopefully someone can help me, for I have queried the web with no success or concrete answer to this error. I’m using Windows and Xampp. Here is the error I am getting after I have recently imported the database into phpmyadmin…

#1142 - SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs'

After I import the database, everything is fine in phpmyadmin until I log out and then log back in, this is where the problem lies. When I click on any of the tables from the imported database, I get the following errors…

SELECT 'prefs'
FROM 'phpmyadmin'.'pma_table_uiprefs'
WEHRE 'username' = 'root'
AND 'db_name' = 'afdb'
AND 'table_name' = 'role'

#1142 - SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs'

Thank you again for taking time to read my post and hopefully someone can help me with this error.

Zigzag answered 25/8, 2013 at 1:30 Comment(1)

My guess is that the user has privileges to at least the SELECT command is denied and needs to be modified by logging in with admin access to modify user privileges to allow SELECT command – Whited 25/8, 2013 at 1:51

S

364

I stumble upon this issue and I solved it just by logging out of phpMyAdmin and in again.

EXPLANATION

Take a look at the error message query:

SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs'

This happens due to MySQL denying access to user "" (blank) at server localhost. The default setting is to block all requests from anonymous users.

By logging out we force phpMyAdmin to "forget" the current user and let us input the login credentials for the MySQL server.

Sexuality answered 11/9, 2017 at 8:31 Comment(0)

A

165

This Will Surely help you

1.open PhpMyAdmin.

2.on the left side under the PhpMyAdmin logo click on second icon(Empty Session Data).

3.that's it.

Amphicoelous answered 6/1, 2020 at 6:34 Comment(0)

H

37

The pma_table_uiprefs table contains user preferences. In phpMyAdmin's config.inc.php, access to this table (and other tables in the configuration storage) is done via the control user. In your case, the controluser parameter is empty, therefore the query fails.

For a short-term fix, put the "//" characters in config.inc.php at the start of this line:

    $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';

then log out and log back in.

For a long-term fix, correctly set up the configuration storage, see http://docs.phpmyadmin.net/en/latest/setup.html#phpmyadmin-configuration-storage

Hollowell answered 25/8, 2013 at 11:24 Comment(5)

Marc, Thank you for the link and a solution. I’ve tried several ways to set up the storage with no success (though it was set up correctly with my other database before I decided to import a different database)--I’m also new to using phpmyadmin and mysql. I finally went with commenting it out “//” which is working for me. The extra features added would be nice, but for now I can get along with just the minimum. --Ben – Zigzag 27/8, 2013 at 13:24

In my case I emptied the pmadb key to make it work. Thanks! – Kenlee 14/11, 2016 at 14:45

@Kenlee Sorry for the late reply, but I'm having a similar open issue. Could you provide a reference where I could read more about this? – Ferebee 8/1, 2019 at 17:25

@DanielScott there is nothing to read about. i simply emptied the phpmyadmin table. that's it! – Kenlee 6/2, 2019 at 14:36

@Kenlee Did you read the reference I wrote about in my answer above? – Hollowell 7/2, 2019 at 12:16

V

14

There is another way of solving this.

Following the instructions in http://docs.phpmyadmin.net/en/latest/setup.html#phpmyadmin-configuration-storage:

1.- I opned xampp\phpMyAdmin\config.inc.php file and found the following lines:

/* User for advanced features */\\

$cfg['Servers'][$i]['controluser'] = 'pma';

$cfg['Servers'][$i]['controlpass'] = '';

2.- Then I observed in phpmyadmin the following tables:

DATABASE: phpmyadmin

TABLES: pma_bookmark pma_column_info pma_designer_coords pma_history pma_pdf_pages pma_recent pma_relation pma_table_coords pma_table_info pma_table_uiprefs pma_tracking pma_userconfig

3.- I run this sql statement:

 GRANT SELECT , INSERT , UPDATE , DELETE ON phpmyadmin.* TO  `pma`@`localhost` IDENTIFIED BY  ''

And it worked.

Vetchling answered 2/4, 2014 at 13:50 Comment(3)

I had to comment out the lines "$cfg['Servers'][$i]['controluser'] = 'pma';" and "$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';" in order to get phpmyadmin to load...then used yer query...uncommented out the lines and refreshed...worked like a charm. – Tollefson 20/12, 2016 at 6:51

Worked for me too. I got this error after I tried to create a db with the same name as an already existing one and phpmyadmin froze my whole browser. – Polson 23/1, 2020 at 22:16

So I just executed this sql statement and now im not able to use phpmyadmin at all. It wont start and does give me no information on how to fix. – Redden 27/1, 2020 at 8:49

R

13

It say your user is blank, need to setup user on /phpMyAdmin/config.inc.php.

Add user to the line

$cfg['Servers'][$i]['controluser'] = 'root';

Rudnick answered 27/5, 2021 at 1:51 Comment(0)

P

8

simply logout from PhpMyAdmin..

Patrilineal answered 5/10, 2021 at 9:1 Comment(0)

A

5

Open the config.inc.php file from C:\xampp\phpmyadmin
Put the "//" characters in config.inc.php at the start of below line:

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';

Example: // $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
Reload your phpmyadmin at localhost.

Annotation answered 17/9, 2013 at 5:11 Comment(0)

D

5

On ubuntu, try dpkg-reconfigure phpmyadmin and recreate the phpmyadmin database. I installed using ansible and this was not done.

Delftware answered 23/12, 2015 at 15:48 Comment(0)

F

4

If you use XAMPP Path ( $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin'; ) C:\xampp\phpmyadmin\config.inc.php (Probably XAMPP1.8 at Line Number 34)

Another Solution: I face same type problem "#1142 - SELECT command denied to user ''@'localhost' for table 'pma_recent'"

open phpmyadmin==>setting==>Navigation frame==> Recently used tables==>0(set the value 0) ==> Save

Fingerprint answered 29/8, 2013 at 5:52 Comment(0)

C

3

I had the same problem and it might look so easy but it solved my problem. I have tried all the solutions recommended here but there was no problem just a day ago. So I thought the problem might be about the Session data. I tried to stop and run apache and mysql services but it didn't work also. Then I realized there are buttons on phpMyAdmin just below its logo on the left side. The button next to "Home"; "Empty Session Data" solved all of my problems.

Crabbe answered 5/10, 2019 at 7:32 Comment(0)

F

3

Just log out of phpMyAdmin, that is all you need to do

Forthwith answered 5/11, 2019 at 10:39 Comment(0)

C

2

Another option is to disable this functionality. If we allow access on the server only for reading Disable pmadb

add config for server

$cfg['Servers'][$i]['userconfig'] =false;
$cfg['Servers'][$i]['pmadb'] = false;
$cfg['Servers'][$i]['bookmarktable'] = false;
$cfg['Servers'][$i]['relation'] = false;
$cfg['Servers'][$i]['table_info'] = false;
$cfg['Servers'][$i]['table_coords'] = false;
$cfg['Servers'][$i]['pdf_pages'] = false;
$cfg['Servers'][$i]['column_info'] = false;
$cfg['Servers'][$i]['history'] = false;
$cfg['Servers'][$i]['table_uiprefs'] = false;
$cfg['Servers'][$i]['tracking'] = false;
$cfg['Servers'][$i]['designer_coords'] = false;
$cfg['Servers'][$i]['userconfig'] = false;
$cfg['Servers'][$i]['recent'] = false;

and refresh session (private tab browser or other)

Cymophane answered 19/3, 2019 at 2:53 Comment(0)

R

2

Try this before anything else - 'clear your cache'. I had the same issue. I was instructed to clear my cache. It worked.

Ruhnke answered 28/3, 2019 at 16:0 Comment(0)

S

2

You use this command in phpMyAdmin SQL Part:

GRANT SELECT , INSERT , UPDATE , DELETE ON phpmyadmin.* TO  `pma`@`localhost` IDENTIFIED BY  ''

Smashandgrab answered 19/2, 2020 at 7:41 Comment(0)

V

2

This is old question , but recently i have solved the problem. The solution is :

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Run this command line in your terminal and provide your 'root' password, and hopefully you can solve the problem. To know more, please visit https://dev.mysql.com/doc/refman/8.0/en/mysql-tzinfo-to-sql.html

Vizierate answered 26/4, 2021 at 5:10 Comment(0)

C

2

I had this same issue after hosting an app, and I solved it by giving the required rights to the database user in the Cpanel. The example is in the image below. be sure to choose the appropriate rights.

Caretaker answered 24/7, 2021 at 10:14 Comment(0)

H

1

I know this may sound absurd, but I solved a similar issue by creating the database as "phpMyAdmin" not "phpmyadmin" (note case) - perhaps there is something about case-sensitivity under Windows?

Hodgepodge answered 30/5, 2014 at 5:45 Comment(0)

R

1

Commenting out this line worked for me: $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin'; But I found several config.inc.php files on my computer because I had a couple installations of MySQL and php haha. I changed it by finding the one under Xampp by just clicking on the config button under Apache for the Xampp control panel then commenting out the line. //

Ringer answered 30/7, 2014 at 22:30 Comment(0)

G

1

One way to resolve this is to login to your root user in phpmyadmin, go to the users tab, find and select the specific user that can't access the select and other queries . Then tick the all checkboxes and just click go. Now that user can select, update and whatever they want.

Garda answered 1/7, 2016 at 15:20 Comment(0)

G

1

My database hosting company had disabled the option for clearing sessions so the above solutions did not work for me. What worked is creating a new user and giving that new user privileges to the database. It worked for me. The old user and password still failed to work but the new created user and password worked

Get answered 6/4, 2021 at 6:8 Comment(0)

EXPLANATION

Recommended topics

Hot tags