Installing phpMyAdmin configuration storage

In addition to basic MySQL databases maintenance, phpMyAdmin offers advanced features that we will discover in the following chapters. These features require the installation of the phpMyAdmin configuration storage.

Goal of the configuration storage

The configuration storage consists of a set of tables that are used behind the scene by phpMyAdmin. They hold metadata which contains information to support special features such as query bookmarks and data transformation. Moreover, for tables using a storage engine that does not support foreign keys, relations between tables are kept in this configuration storage. The metadata is generated and maintained by phpMyAdmin on the basis of our actions from the interface.

Location of the configuration storage

There are two possible places to store these tables:

• A user's database—to facilitate every web developer owning a database to benefit from these features.

• A dedicated database called  pmadb (phpMyAdmin database). In a multi-user installation, this database may be accessible to a number of users while keeping the metadata private.

As this storage does not exist by default and because the phpMyAdmin team wants to promote it, the interface displays the following notice message on the home page:

This message can be disabled with the following parameter (which, by default, is set to FALSE):

$cfg['PmaNoRelation_DisableWarning'] = TRUE;

Performing the installation

The previous error message is displayed even if only a part of the configuration storage is lacking. Of course, on a fresh installation, all parts are lacking—our database has not yet heard of phpMyAdmin and needs to be outfitted with this configuration storage. Following the here link in the previous screenshot brings up a panel explaining that the pmadb, and the tables that are supposed to be a part of it, are either missing or undefined.

It's important to realize that the configuration storage will be functional only if the following two conditions are met:

• Proper definitions are present in config.inc.php

• The corresponding tables (and maybe the database) are created

To create the necessary structure that matches our current version of phpMyAdmin, a command file called create_tables.sql is available in the scripts sub-directory of the phpMyAdmin installation directory. However, we should not blindly execute it before understanding the possible choices—single-user installation or multi-user installation.

Note

In subsequent chapters, we will assume that the multi-user installation has been chosen.

Installing for a single user

Even if we are entitled to only one database by the system administrator, we can still use all the advanced features of phpMyAdmin. In this setup, we will use our existing database to store the metadata tables.

We need to modify a local copy of the scripts/create_tables.sql file to populate our database with all the needed tables. They will have the prefix pma_ to make them easily recognizable. We need to remove the following lines:

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

This is done because we won't be using a phpmyadmin database but our own. Next, we should open our own database in phpMyAdmin. We are now ready to execute the script. There are two ways of doing this:

• As we already have the script in our editor, we can just copy the lines and paste them in the query box of the SQL page. More details on this in Chapter 11.

• Another way is to use the import technique shown in Chapter 7. We select the create_tables.sql script that we just modified.

After the creation, the navigation panel shows us the special pma_ tables along with our normal tables.

It is now time to adjust all the configuration storage related parameters in config.inc.php. This can be done easily with the setup script as seen in this chapter, or by pasting the appropriate lines from the config.sample.inc.php file. The database is our own and the table names are the ones that have just been created:

$cfg['Servers'][$i]['pmadb']            = 'mydatabase';
$cfg['Servers'][$i]['bookmarktable']    = 'pma_bookmark';
$cfg['Servers'][$i]['relation']         = 'pma_relation';
$cfg['Servers'][$i]['table_info']       = 'pma_table_info';
$cfg['Servers'][$i]['table_coords']     = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages']        = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info']      = 'pma_column_info';
$cfg['Servers'][$i]['history']          = 'pma_history';
$cfg['Servers'][$i]['tracking']         = 'pma_tracking';
$cfg['Servers'][$i]['designer_coords']  = 'pma_designer_coords';
$cfg['Servers'][$i]['userconfig']       = 'pma_userconfig';

Note

As table names are case sensitive, we must use the same names as the tables created by the installation script. We are free to change the table names (see the right-hand part of the configuration directives listed) provided we change them accordingly in the database.

The pmadb and each table have a specific function as listed next:

Function	Description	Explained in
pmadb	Defines the database where all tables are located	This chapter
bookmarktable	Contains the query bookmarks	Chapter 14
relation	Defines inter-table relations, as used in many of the phpMyAdmin's features	Chapter 10
table_info	Contains the display field	Chapter 10
table_coords and pdf_pages	Contains the metadata necessary for drawing a schema of the relations in a PDF format	Chapter 15
column_info	Used for column-commenting and MIME-based transformations	Chapter 16
history	Contains SQL query history information	Chapter 11
tracking	Contains the metadata and the actual SQL statements related to the tracked tables	Chapter 18
designer_coords	Holds the coordinates used by the Designer feature	Chapter 10
userconfig	Holds the user's preferences	Chapter 3

Between each phpMyAdmin version, the infrastructure may be enhanced—the changes are explained in Documentation.html. This is why phpMyAdmin has various checks to ascertain the structure of the tables. If we know we are using the latest structure, $cfg['Servers'][$i]['verbose_check'] can be set to FALSE to avoid checks, thereby slightly increasing phpMyAdmin's speed.

Installing for multiple users

In this setup, we will have a distinct database—pmadb—to store the metadata tables. Our control user will have specific rights to this database. Each user will work with his/her login name and password which will be used to access his/her databases. However, whenever phpMyAdmin itself accesses pmadb to obtain some metadata, it will use the control user's privileges.

Note

Setting a multi-user installation is possible only for a MySQL system administrator who has the privileges of assigning rights to another user (here, the pma user).

We first ensure that the control user pma has been created and that its definition in config.inc.php is appropriate. We then copy scripts/create_tables.sql to our local workstation and edit it. We replace the following lines:

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

with these, removing the comment characters (double-dash):

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

We then execute this script by importing it (refer to Chapter 7). The net effect is to create the phpmyadmin database, assign proper rights to user pma, and populate the database with all the necessary tables.

The last step is to adjust all the parameters in config.inc.php that relate to relational features. Please refer to the Installing for a single user section, except for the database name in the pmadb parameter, which will be as shown in the following code snippet:

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';

The installation is now complete. We will test the features in the coming sections and chapters. We can do a quick check by logging out of phpMyAdmin, then logging in and displaying the home page; the warning message should be gone.