Reputation: 1405

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

Upvotes: 126

Answers (20)

HabteSoft

Reputation: 121

simply logout from PhpMyAdmin..

Upvotes: 8

Chetam Okafor

Reputation: 573

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.

Upvotes: 2

Hix.botay

Reputation: 433

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

Upvotes: 13

Azharul Islam Somon

Reputation: 117

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

Upvotes: 2

ERIC NDERITU

Reputation: 94

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

Upvotes: 1

Dhruv

Reputation: 2090

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.

Upvotes: 169

Bijeesh Raghavan

Reputation: 69

You use this command in phpMyAdmin SQL Part:

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

Upvotes: 2

Ebuka

Reputation: 634

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

Upvotes: 3

Mustafa Ozbalci

Reputation: 115

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.

Upvotes: 3

Don199

Reputation: 167

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

Upvotes: 3

F00x

Reputation: 124

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)

Upvotes: 2

josemmo

Reputation: 7093

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.

Upvotes: 374

Ashok P

Reputation: 11

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.

Upvotes: 1

Manel Clos

Reputation: 1835

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

Upvotes: 5

user3893489

Reputation: 11

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

Upvotes: 1

Nick

Reputation: 75

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?

Upvotes: 1

Andrew F.

Reputation: 465

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.

Upvotes: 14

matinict

Reputation: 2490

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

Upvotes: 4

Amal E Thomas

Reputation: 51

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.

Upvotes: 5

Marc Delisle

Reputation: 8997

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

Upvotes: 37

#1142 - SELECT command denied to user &#39;&#39;@&#39;localhost&#39; for table &#39;pma_table_uiprefs&#39;

Answers (20)

EXPLANATION

Related Questions

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