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

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.

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

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

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

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.

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.

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'"

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

You use this command in phpMyAdmin SQL Part:

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

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. //

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?

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.

1. Open the config.inc.php file from C:\xampp\phpmyadmin

2. Put the "//" characters in config.inc.php at the start of below line:

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

   Example: // $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';

3. Reload your phpmyadmin at localhost.

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.

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

1. 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;

2. and refresh session (private tab browser or other)

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

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.