Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

phpMyAdmin shows #1142 SELECT command denied on pma__table_uiprefs

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

Short answer

pma__table_uiprefs belongs to phpMyAdmin's configuration storage, and the account that reads it has no SELECT right there. Either the tables are missing, or the control user has no grants on that database. Create the tables from sql/create_tables.sql, grant the control user rights on them, or switch that feature off in config.inc.php.

  • Applies to phpMyAdmin 5.2, MySQL 8.0 and 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Check whether the configuration storage database exists and what the account in the error may do. Run this as an admin user in the mysql client.

    SHOW DATABASES LIKE 'phpmyadmin';
    SHOW GRANTS FOR 'pma'@'localhost';
  2. 2Match what you see, then jump to its fix

    1. 1SHOW GRANTS shows nothing for that database, or the account in the error is an empty userThe control user has no rights on the configuration storage databaseFix
    2. 2SHOW TABLES in the storage database is empty, or the database does not existThe configuration storage tables were never createdFix
    3. 3A single-user install that never configured advanced featuresYou do not need the feature that uses the tableFix

What does the error look like?

  • #1142 - SELECT command denied to user ''@'localhost' for table 'pma__table_uiprefs'
  • ER_TABLEACCESS_DENIED_ERROR: %s command denied to user '%s'@'%s' for table '%s'

Cause 1 of 3

The control user has no rights on the configuration storage database

1Check

The control user (controluser and controlpass in config.inc.php) reads and writes the pma__ tables. Without it, phpMyAdmin falls back to the normal login's own parameters. Confirm which control user the config names.

grep -nE 'controluser|pmadb' /etc/phpmyadmin/config.inc.php

2Fix

Create the control user and grant it SELECT, INSERT, UPDATE and DELETE on the storage database only. The statements below are the MySQL 8.0 and later form from the phpMyAdmin docs; put the user and password in config.inc.php.

CREATE USER 'pma'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'choose-a-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';

3Verify

Reload phpMyAdmin and open a database; the error is gone.

SHOW GRANTS FOR 'pma'@'localhost';

Confirmed by docs.phpmyadmin.net

Did this fix it?

Cause 2 of 3

The configuration storage tables were never created

1Check

List the tables. A working storage database holds the pma__ tables the config names, including table_uiprefs.

SHOW DATABASES LIKE 'phpmyadmin';
SHOW TABLES FROM phpmyadmin LIKE 'pma__%';

2Fix

Import sql/create_tables.sql from the phpMyAdmin directory, or open the Operations tab, where zero configuration offers to create the tables. You may need to adjust the script for your database name, and the table names in config.inc.php must match.

mysql -u root -p < sql/create_tables.sql

3Verify

The pma__ tables are listed.

SHOW TABLES FROM phpmyadmin LIKE 'pma__%';

Confirmed by docs.phpmyadmin.net

Did this fix it?

Cause 3 of 3

You do not need the feature that uses the table

1Check

Each feature is enabled by setting its table name; the docs say setting a feature to false disables it. For a single user, a control user is not required.

grep -n "table_uiprefs" /etc/phpmyadmin/config.inc.php

2Fix

Set the feature to false instead of granting rights, if you do not want remembered table preferences.

$cfg['Servers'][$i]['table_uiprefs'] = false;

3Verify

Reload phpMyAdmin; browsing tables no longer queries that table.

grep -n "table_uiprefs" /etc/phpmyadmin/config.inc.php

Confirmed by docs.phpmyadmin.net

Did this fix it?

How do I stop it coming back?

  • Give the control user rights on the configuration database only, never on user data.
  • Re-check the storage tables after a phpMyAdmin upgrade; the docs list upgrade scripts for older versions.
  • Keep controluser and controlpass in config.inc.php, not in the shared login.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.