#1142 - SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs'
MysqlPhpmyadminMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
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
Solution 4 - Mysql
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.
Solution 5 - Mysql
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';
Solution 6 - Mysql
-
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.
Solution 7 - Mysql
On ubuntu, try dpkg-reconfigure phpmyadmin and recreate the phpmyadmin database. I installed using ansible and this was not done.
Solution 8 - Mysql
simply logout from PhpMyAdmin..
Solution 9 - Mysql
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
Solution 10 - Mysql
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.
Solution 11 - Mysql
Just log out of phpMyAdmin, that is all you need to do
Solution 12 - Mysql
Try this before anything else - 'clear your cache'. I had the same issue. I was instructed to clear my cache. It worked.
Solution 13 - Mysql
You use this command in phpMyAdmin SQL Part:
GRANT SELECT , INSERT , UPDATE , DELETE ON phpmyadmin.* TO `pma`@`localhost` IDENTIFIED BY ''
Solution 14 - Mysql
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
Solution 15 - Mysql
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.
Solution 16 - Mysql
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?
Solution 17 - Mysql
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. //
Solution 18 - Mysql
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.
Solution 19 - Mysql
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)
Solution 20 - Mysql
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