phpmyadmin.pma_table_uiprefs doesn't exist

Question

I searched the internet but cannot find anything related to this specific error/table. It pops up when I try to view a table in phpMyAdmin. I am logged in as root and the installation (under ubuntu 13.10) of phpMyAdmin is fresh and untouched so far.

Here is the whole 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

Is the installation just broken or am I missing something?

Answer 1

2022 Solution

Execute the below SQL commands in SQL tab : the below commands create new database and setup all missing information you need.

-- --------------------------------------------------------
-- SQL Commands to set up the pmadb as described in the documentation.
--
-- This file is meant for use with MySQL 5 and above!
--
-- This script expects the user pma to already be existing. If we would put a
-- line here to create them too many users might just use this script and end
-- up with having the same password for the controluser.
--
-- This user "pma" must be defined in config.inc.php (controluser/controlpass)
--
-- Please don't forget to set up the tablenames in config.inc.php
--

-- --------------------------------------------------------

--
-- Database : `phpmyadmin`
--
CREATE DATABASE IF NOT EXISTS `phpmyadmin`
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE phpmyadmin;

-- --------------------------------------------------------

--
-- Privileges
--
-- (activate this statement if necessary)
-- GRANT SELECT, INSERT, DELETE, UPDATE, ALTER ON `phpmyadmin`.* TO
--    'pma'@localhost;

-- --------------------------------------------------------

--
-- Table structure for table `pma__bookmark`
--

CREATE TABLE IF NOT EXISTS `pma__bookmark` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `dbase` varchar(255) NOT NULL default '',
  `user` varchar(255) NOT NULL default '',
  `label` varchar(255) COLLATE utf8_general_ci NOT NULL default '',
  `query` text NOT NULL,
  PRIMARY KEY  (`id`)
)
  COMMENT='Bookmarks'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__column_info`
--

CREATE TABLE IF NOT EXISTS `pma__column_info` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `db_name` varchar(64) NOT NULL default '',
  `table_name` varchar(64) NOT NULL default '',
  `column_name` varchar(64) NOT NULL default '',
  `comment` varchar(255) COLLATE utf8_general_ci NOT NULL default '',
  `mimetype` varchar(255) COLLATE utf8_general_ci NOT NULL default '',
  `transformation` varchar(255) NOT NULL default '',
  `transformation_options` varchar(255) NOT NULL default '',
  `input_transformation` varchar(255) NOT NULL default '',
  `input_transformation_options` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `db_name` (`db_name`,`table_name`,`column_name`)
)
  COMMENT='Column information for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__history`
--

CREATE TABLE IF NOT EXISTS `pma__history` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `username` varchar(64) NOT NULL default '',
  `db` varchar(64) NOT NULL default '',
  `table` varchar(64) NOT NULL default '',
  `timevalue` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `sqlquery` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`,`db`,`table`,`timevalue`)
)
  COMMENT='SQL history for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__pdf_pages`
--

CREATE TABLE IF NOT EXISTS `pma__pdf_pages` (
  `db_name` varchar(64) NOT NULL default '',
  `page_nr` int(10) unsigned NOT NULL auto_increment,
  `page_descr` varchar(50) COLLATE utf8_general_ci NOT NULL default '',
  PRIMARY KEY  (`page_nr`),
  KEY `db_name` (`db_name`)
)
  COMMENT='PDF relation pages for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__recent`
--

CREATE TABLE IF NOT EXISTS `pma__recent` (
  `username` varchar(64) NOT NULL,
  `tables` text NOT NULL,
  PRIMARY KEY (`username`)
)
  COMMENT='Recently accessed tables'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__favorite`
--

CREATE TABLE IF NOT EXISTS `pma__favorite` (
  `username` varchar(64) NOT NULL,
  `tables` text NOT NULL,
  PRIMARY KEY (`username`)
)
  COMMENT='Favorite tables'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__table_uiprefs`
--

CREATE TABLE IF NOT EXISTS `pma__table_uiprefs` (
  `username` varchar(64) NOT NULL,
  `db_name` varchar(64) NOT NULL,
  `table_name` varchar(64) NOT NULL,
  `prefs` text NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`username`,`db_name`,`table_name`)
)
  COMMENT='Tables'' UI preferences'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__relation`
--

CREATE TABLE IF NOT EXISTS `pma__relation` (
  `master_db` varchar(64) NOT NULL default '',
  `master_table` varchar(64) NOT NULL default '',
  `master_field` varchar(64) NOT NULL default '',
  `foreign_db` varchar(64) NOT NULL default '',
  `foreign_table` varchar(64) NOT NULL default '',
  `foreign_field` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`master_db`,`master_table`,`master_field`),
  KEY `foreign_field` (`foreign_db`,`foreign_table`)
)
  COMMENT='Relation table'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__table_coords`
--

CREATE TABLE IF NOT EXISTS `pma__table_coords` (
  `db_name` varchar(64) NOT NULL default '',
  `table_name` varchar(64) NOT NULL default '',
  `pdf_page_number` int(11) NOT NULL default '0',
  `x` float unsigned NOT NULL default '0',
  `y` float unsigned NOT NULL default '0',
  PRIMARY KEY  (`db_name`,`table_name`,`pdf_page_number`)
)
  COMMENT='Table coordinates for phpMyAdmin PDF output'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__table_info`
--

CREATE TABLE IF NOT EXISTS `pma__table_info` (
  `db_name` varchar(64) NOT NULL default '',
  `table_name` varchar(64) NOT NULL default '',
  `display_field` varchar(64) NOT NULL default '',
  PRIMARY KEY  (`db_name`,`table_name`)
)
  COMMENT='Table information for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__tracking`
--

CREATE TABLE IF NOT EXISTS `pma__tracking` (
  `db_name` varchar(64) NOT NULL,
  `table_name` varchar(64) NOT NULL,
  `version` int(10) unsigned NOT NULL,
  `date_created` datetime NOT NULL,
  `date_updated` datetime NOT NULL,
  `schema_snapshot` text NOT NULL,
  `schema_sql` text,
  `data_sql` longtext,
  `tracking` set('UPDATE','REPLACE','INSERT','DELETE','TRUNCATE','CREATE DATABASE','ALTER DATABASE','DROP DATABASE','CREATE TABLE','ALTER TABLE','RENAME TABLE','DROP TABLE','CREATE INDEX','DROP INDEX','CREATE VIEW','ALTER VIEW','DROP VIEW') default NULL,
  `tracking_active` int(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`db_name`,`table_name`,`version`)
)
  COMMENT='Database changes tracking for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__userconfig`
--

CREATE TABLE IF NOT EXISTS `pma__userconfig` (
  `username` varchar(64) NOT NULL,
  `timevalue` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `config_data` text NOT NULL,
  PRIMARY KEY  (`username`)
)
  COMMENT='User preferences storage for phpMyAdmin'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__users`
--

CREATE TABLE IF NOT EXISTS `pma__users` (
  `username` varchar(64) NOT NULL,
  `usergroup` varchar(64) NOT NULL,
  PRIMARY KEY (`username`,`usergroup`)
)
  COMMENT='Users and their assignments to user groups'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__usergroups`
--

CREATE TABLE IF NOT EXISTS `pma__usergroups` (
  `usergroup` varchar(64) NOT NULL,
  `tab` varchar(64) NOT NULL,
  `allowed` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`usergroup`,`tab`,`allowed`)
)
  COMMENT='User groups with configured menu items'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__navigationhiding`
--

CREATE TABLE IF NOT EXISTS `pma__navigationhiding` (
  `username` varchar(64) NOT NULL,
  `item_name` varchar(64) NOT NULL,
  `item_type` varchar(64) NOT NULL,
  `db_name` varchar(64) NOT NULL,
  `table_name` varchar(64) NOT NULL,
  PRIMARY KEY (`username`,`item_name`,`item_type`,`db_name`,`table_name`)
)
  COMMENT='Hidden items of navigation tree'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__savedsearches`
--

CREATE TABLE IF NOT EXISTS `pma__savedsearches` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `username` varchar(64) NOT NULL default '',
  `db_name` varchar(64) NOT NULL default '',
  `search_name` varchar(64) NOT NULL default '',
  `search_data` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `u_savedsearches_username_dbname` (`username`,`db_name`,`search_name`)
)
  COMMENT='Saved searches'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__central_columns`
--

CREATE TABLE IF NOT EXISTS `pma__central_columns` (
  `db_name` varchar(64) NOT NULL,
  `col_name` varchar(64) NOT NULL,
  `col_type` varchar(64) NOT NULL,
  `col_length` text,
  `col_collation` varchar(64) NOT NULL,
  `col_isNull` boolean NOT NULL,
  `col_extra` varchar(255) default '',
  `col_default` text,
  PRIMARY KEY (`db_name`,`col_name`)
)
  COMMENT='Central list of columns'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__designer_settings`
--

CREATE TABLE IF NOT EXISTS `pma__designer_settings` (
  `username` varchar(64) NOT NULL,
  `settings_data` text NOT NULL,
  PRIMARY KEY (`username`)
)
  COMMENT='Settings related to Designer'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `pma__export_templates`
--

CREATE TABLE IF NOT EXISTS `pma__export_templates` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `export_type` varchar(10) NOT NULL,
  `template_name` varchar(64) NOT NULL,
  `template_data` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_user_type_template` (`username`,`export_type`,`template_name`)
)
  COMMENT='Saved export templates'
  DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Source : https://raw.githubusercontent.com/phpmyadmin/phpmyadmin/master/sql/create_tables.sql

Answer 2

For Window User >> Just go to localhost phpmyadmin and just click this button.session_clear

Answer 3

Ubuntu STEP 1 : type create_tables.sql phpmyadmin and select the github link STEP 2 : Download the create_tables.sql file STEP 3 : Import it on your phpmyadmin using import button on navbar, upload the file. CHEERS!! Work is Done!!

Answer 4

For ubuntu me help - sudo dpkg-reconfigure phpmyadmin

Answer 5

Commented out whole config section(/etc/phpmyadmin/config.inc.php):

from:

/* Optional: Advanced phpMyAdmin features */

to:

/* Optional: Advanced phpMyAdmin features

I just needed simple acces to data ... This problem shouldn't be blocking for phpmyadmin displaying tables ...

Answer 6

I had similar problems with phpMyAdmin after changing Mysql InnoDB setting to innodb_file_per_table = 1
to move InnoDB tables into separate files.

None of the other answers helped in my case, neither sudo dpkg-reconfigure phpmyadmin nor importing create_tables.sql. Both failed.

What helped was making sure no default-storage-engine, default-tmp-storage-engine and innodb_file_format is enforced in my.cnf

After restarting MySQL and removing+reinstalling phpMyAdmin there are no more errors.

Answer 7

Into phpmyadmin database's create that table, there miskta on name of that table it may be pma_table_uiprefs and not pma__table_uiprefs

CREATE TABLE IF NOT EXISTS pma_table_uiprefs ( username varchar(64) NOT NULL, db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, prefs text NOT NULL, last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (username,db_name,table_name) ) COMMENT='Tables'' UI preferences' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Answer 8

Clear your cookies

When using PHPMyAdmin configured with multiple databases, one having the phpmyadmin table and another not having it; phpmyadmin will store preferences for the database with the table in your cookies then try to load them with the database that doesn't have the table.

To test, try using an incognito window.

Answer 9

I use Windows 7 Xampp's version of phpmyadmin and none of the above, or below if this post gets upvoted, answers worked. I have tried uninstalling Xampp and upgrading to a higher version, manually changing values in the config folder, importing .sql files from some github page, and even viewing youtube videos but none of the suggestions worked.

Solution:

Delete EVERYTHING in the C:\xampp\phpMyAdmin folder > go to https://www.phpmyadmin.net/ and download the latest version > extract everything to the C:\xampp\phpMyAdmin folder and your problem is solved.

^^^^^ Read this if you don't want to waste hours searching Google for tons of failed solutions. It's far simpler too! ^^^^^^

Answer 10

A really simple solution is to edit /etc/phpmyadmin/config.inc.php and put

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

It just manages the UI and who needs the UI if its interfering with display of actual data.

cheers

Answer 11

I found a solution to fix this.

Edit your /etc/phpmyadmin/config.inc.php file.

Find:

if (!empty($dbport) || $dbserver != 'localhost') {
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['port'] = $dbport;
    }

Add after:

$cfg['Servers'][$i]['pmadb'] = null; // Apurba

Restart your apache service and try. Hope it helps. Thanks.

Answer 12

To elaborate on multiple reply here above, there is an error in the config.inc.php shipped, where a "_" is missing, which is what this reply sais.

To fix that, you can add a "pma__table_uiprefs.inc.php" in the "/etc/phpmyadmin/conf.d" folder, leaving main configuration files untouched...

<?php foreach($cfg['Servers'] as $i => $v) { $cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs'; }

Answer 13

Steps:

• Just download create_table.sql from GitHub and save that file in your system.
• Then go to your phpMyAdmin.
• And click on Import from upper tab.
• At last select create_table.sql and upload that.

After all it works for me and hopefully work for you.

Answer 14

I had the same problem with latest Lampp with MariaDB on Ubuntu Server 14.04, and @iceberg's solution worked very well:

You just change following line in /etc/phpmyadmin/config.inc.php

$ Cfg ['Servers'] [$ i] ['table_uiprefs'] = ‘pma_table_uiprefs’;

to

$ cfg ['Servers'] [$ i] ['pma__table_uiprefs'] = ‘pma__table_uiprefs’;

And restart apache server with command,

sudo service apache2 restart

Lampp config.inc.php is located in /opt/lampp/phpmyadmin/config.inc.php and to make it work, I also needed to change

$ cfg ['Servers'] [$ i] ['tracking'] = ‘pma__tracking’;

to

$ cfg ['Servers'] [$ i] ['pma__tracking'] = ‘pma__tracking’;

and restart the lampp:

sudo /opt/lampp/lampp stop
sudo /opt/lampp/lampp start

Answer 15

You are missing at least one of the phpMyAdmin configuration storage tables, or the configured table name does not match the actual table name.

See http://docs.phpmyadmin.net/en/latest/setup.html#phpmyadmin-configuration-storage.

A quick summary of what to do can be:

1. On the shell: locate create_tables.sql.
2. import /usr/share/doc/phpmyadmin/examples/create_tables.sql.gz using phpMyAdmin.
3. open /etc/phpmyadmin/config.inc.php and edit lines 81-92: change pma_bookmark to pma__bookmark and so on.

Answer 16

I just located the create_tables.sql, saved to my desktop, opened phpMyAdmin, selected the import tab, selected the create_tables.sql, clicked ok

Answer 17

You just need to change the table names as per the error in - /etc/phpmyadmin/config.inc.php

$ Cfg ['Servers'] [$ i] ['table_uiprefs'] = ‘pma_table_uiprefs’;

to

$ Cfg ['Servers'] [$ i] ['table_uiprefs'] = ‘pma__table_uiprefs’;

assign the table names accordingly and everything will be fine.

For me,sudo dpkg-reconfigure phpmyadmin caused more trouble when I chose the option to re-install the database

Answer 18

in linux os such as Debian or Ubutu you can simply try this ways, for first time remove phpmyadmin with --purge parameter:

sudo apt-get remove --purge phpmyadmin

then install again

sudo apt-get install phpmyadmin      

thats work fine :)

Answer 19

Try sudo dpkg-reconfigure phpmyadmin

To Replace config file /etc/phpmyadmin/config-db.php with new version

Answer 20

you should reconfigure the phpmyadmin On terminal:

• sudo dpkg-reconfigure phpmyadmin

Answer 21

I came across this same problem on Ubuntu 13.10. I didn't want to hack PHP files, because normally phpMyAdmin works out of the box after installing the package from Ubuntu repositories. Instead I ran:

sudo dpkg-reconfigure phpmyadmin

During reconfigure, I said "yes" to reinstalling the phpMyAdmin database. Afterwards, the problem was gone. I have a vague memory of answering "No" to that question at some earlier time, during an install or upgrade. That is probably why the problem occurred in the first place.

Answer 22

I had to change this rows:

$cfg['Servers'][$i]['pma__bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['pma__relation'] = 'pma__relation';
$cfg['Servers'][$i]['pma__table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['pma__table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pma__pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['pma__column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['pma__history'] = 'pma__history';
$cfg['Servers'][$i]['pma__table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['pma__designer_coords'] = 'pma__designer_coords';
$cfg['Servers'][$i]['pma__tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['pma__userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['pma__recent'] = 'pma__recent';
$cfg['Servers'][$i]['pma__table_uiprefs'] = 'pma__table_uiprefs';

add: "pma__" to ['bookmarktable'] and "_" to 'pma_bookmark'

Answer 23

You just change following line in /etc/phpmyadmin/config.inc.php

$ Cfg ['Servers'] [$ i] ['table_uiprefs'] = ‘pma_table_uiprefs’;

to

$ cfg ['Servers'] [$ i] ['pma__table_uiprefs'] = ‘pma__table_uiprefs’;

And restart apache server with command,

sudo service apache2 restart