Technical Analysis and Practical Guide to Resolving 'pma_table_uiprefs doesn't exist' Error in phpMyAdmin

Problem Background and Error Analysis

When using phpMyAdmin to manage MySQL databases, users may encounter the following error message:

SELECT `prefs`
FROM `phpmyadmin`.`pma_table_uiprefs`
WHERE `username` = 'root'
AND `db_name` = 'symfony'
AND `table_name` = 'users'

MySQL reports: #1146 - Table 'phpmyadmin.pma_table_uiprefs' doesn't exist

This error indicates that phpMyAdmin failed to access the configuration storage table pma_table_uiprefs because it does not exist in the specified database. Configuration storage tables are key components for phpMyAdmin to save metadata such as user interface preferences, bookmarks, and history. Their absence prevents some advanced features from functioning properly.

Function and Structure of Configuration Storage Tables

phpMyAdmin's configuration storage feature allows persisting user-specific settings in the database rather than relying on sessions or local storage. These tables typically have prefixes like pma_ or pma__ and include but are not limited to:

• pma_table_uiprefs: Stores table display preferences (e.g., column sorting, row limit)
• pma_bookmark: Saves SQL query bookmarks
• pma_history: Records SQL query history
• pma_recent: Tracks recently accessed tables

When phpMyAdmin detects that relevant features are enabled, it automatically queries these tables. If a table is missing, error #1146 is thrown. This often occurs after a fresh installation without proper initialization of configuration storage, or during upgrades when table structures are not synchronized.

Solution 1: Import Official SQL Files (Recommended)

According to phpMyAdmin official documentation, the most standard solution is to import predefined SQL files to create all necessary configuration storage tables. Specific steps are as follows:

1. Locate the SQL file in the terminal: locate create_tables.sql. On Ubuntu systems, this file is usually at /usr/share/doc/phpmyadmin/examples/create_tables.sql.gz.
2. Decompress and import the SQL file. This can be done via phpMyAdmin's import feature or using command-line tools: gunzip -c /usr/share/doc/phpmyadmin/examples/create_tables.sql.gz | mysql -u root -p phpmyadmin. This creates a database named phpmyadmin (if it doesn't exist) and generates all configuration tables within it.
3. Modify the configuration file /etc/phpmyadmin/config.inc.php to ensure table name prefixes match those created. For example, change lines 81-92 from pma_bookmark to pma__bookmark (note the double underscores). Other table names should be adjusted accordingly.

This method ensures completeness and compatibility of table structures, suitable for most scenarios.

Solution 2: Reconfigure phpMyAdmin Package

For phpMyAdmin installed via package managers (e.g., apt), system tools can be used for reconfiguration:

sudo dpkg-reconfigure phpmyadmin

During the interactive configuration process, select "yes" when asked to reinstall the phpMyAdmin database. This automatically creates or repairs configuration storage tables. This method is particularly suitable for Ubuntu/Debian systems as it handles dependencies and file permissions, avoiding errors that may arise from manual operations. Users report successfully resolving the issue on Ubuntu 13.10 with this method, speculating that the original installation might have mistakenly selected not to create the database.

Solution 3: Manually Modify Configuration File

If only temporary disabling of configuration storage is needed, or if table name mismatch is the sole issue, the configuration file can be adjusted manually:

// Modify in /etc/phpmyadmin/config.inc.php
$cfg['Servers'][$i]['table_uiprefs'] = 'pma_table_uiprefs';
// Change to
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';

After modification, restart the web server (e.g., Apache) for changes to take effect: sudo service apache2 restart. However, this method only applies when table name prefixes are inconsistent; if tables do not exist at all, they must be created using one of the first two methods.

Practical Recommendations and Troubleshooting

When choosing a solution, prioritize method 1 or 2 as they are more comprehensive. If issues persist, check the following aspects:

• Database user permissions: Ensure the MySQL user used by phpMyAdmin has access to the phpmyadmin database.
• Configuration file paths: These may vary across systems, e.g., in CentOS it might be at /etc/phpMyAdmin/config.inc.php.
• Version compatibility: Ensure SQL files match the phpMyAdmin version to avoid table structure conflicts.

By systematically analyzing error causes and implementing appropriate fixes, users can restore full phpMyAdmin functionality, enhancing database management efficiency.