Enabling Linked Tables in phpMyAdmin

This is what I was running when I got this to work:

  • WAMP Server 2.0
  • Apache 2.0.63
  • PHP 5.2.8
  • MySQL 5.1.36

Step 1: Edit and run create_tables.sql

This sql file is included with phpMyAdmin, in a wamp installation it can be found at: C:\wamp\apps\phpmyadmin3.2.0.1\scripts\create_tables.sql

On many Linux systems that file can be located at /etc/phpmyadmin/config.inc.php

There are two sections at the top which are usually commented out – one to create a phpmyadmin database, and one to create a phpmyadmin user. I usually just create the database myself and I prefer to use the following command in the next step to create the user. So, after creating the database, I usually just run the file as it originally came.

Step 2: Create a control user (controluser):

You’re on your own if you used the script above to create your control user, and you probably know what you’re doing. If you don’t, however, I recommend these mysql commands:

CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'pmapass';
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'phpmyadmin'@'localhost';

Step 3: Edit config.inc.php:

Add these lines to your config.inc.php file and then you should be good to go!

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$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]['designer_coords'] = 'pma_designer_coords';
$cfg['Servers'][$i]['tracking'] = 'pma_tracking';

$cfg['Servers'][$i]['controluser']   = 'phpmyadmin';
$cfg['Servers'][$i]['controlpass']   = 'pmapass';

Troubleshooting:

If you’re getting the message “The additional features for working with linked tables have been deactivated. To find out why click here.” or something similar, it probably means you don’t have the pmadb, controluser, and/or controlpass set up correctly to corresponding users in the database.

If you’re getting a message saying  “not OK [ Documentation ]” but the first line ($cfg[‘Servers’][$i][‘pmadb’]) says OK, you either need to adjust the table names, or overcome something more complex. For me, after I switched to Apache 2.0 from 2.2 it started working.

ps: After installing, I was getting this to work fine with firefox 3.5, however, it was a no-go on google chrome.

Good luck.

Also see:

Related Posts:

  • No Related Posts
This entry was posted in Tech Tips, Web Development and tagged , . Bookmark the permalink.

19 Responses to Enabling Linked Tables in phpMyAdmin

  1. Babsmuler says:

    Used on XAMPP, great tip! It worked excellent and is so easy to follow compared to the other reads that have been getting me even more confused! Millions thanks!

  2. Andrew says:

    I have re-installed mysql, reinstalled phpmyadmin, I am on apache 2.2.3 (not sure how to go backwards to 2.0. I am on phpmyadmin 3.4.5, mysql 5.5 php 5.3.8

    I have given ALL PRIVILAGES to user pma on the phpmyadmin database.

    The site is behind a firewall so I can’t give access (easily).

    The message I get is:

    $cfg['Servers'][$i]['pmadb'] ...     not OK [ Documentation ]
    $cfg['Servers'][$i]['relation'] ...     not OK [ Documentation ]
    

    General relation features: Disabled

    $cfg['Servers'][$i]['table_info'] ...    not OK [ Documentation ]
    

    Display Features: Disabled

    $cfg['Servers'][$i]['table_coords'] ...  not OK [ Documentation ]
    $cfg['Servers'][$i]['pdf_pages'] ...    not OK [ Documentation ]
    

    Creation of PDFs: Disabled

    $cfg['Servers'][$i]['column_info'] ...   not OK [ Documentation ]
    

    Displaying Column Comments: Disabled Browser transformation: Disabled

    $cfg['Servers'][$i]['bookmarktable'] ...     not OK [ Documentation ]
    

    Bookmarked SQL query: Disabled

    $cfg['Servers'][$i]['history'] ...   not OK [ Documentation ]
    

    SQL history: Disabled

    $cfg['Servers'][$i]['designer_coords'] ...   not OK [ Documentation ]
    

    Designer: Disabled

    $cfg['Servers'][$i]['tracking'] ...  not OK [ Documentation ]
    

    Tracking: Disabled

    $cfg['Servers'][$i]['userconfig'] ...    not OK [ Documentation ]
    

    User preferences: Disabled

    My config.inc.php is

    
    <?php
    /* vim: set expandtab sw=4 ts=4 sts=4: */
    /**
     * phpMyAdmin sample configuration, you can use it as base for
     * manual configuration. For easier setup you can use setup/
     *
     * All directives are explained in Documentation.html and on phpMyAdmin
     * wiki .
     *
     * @package phpMyAdmin
     */
    
    /*
     * This is needed for cookie based authentication to encrypt password in
     * cookie
     */
    
    /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
    $cfg['blowfish_secret'] = 'melovecookies'; 
    
    /*
     * Servers configuration
     */
    $i = 0;
    
    /*
     * First server
     */
    $i++;
    /* Authentication type */
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    /* Server parameters */
    $cfg['Servers'][$i]['host'] = 'localhost';
    $cfg['Servers'][$i]['connect_type'] = 'tcp';
    $cfg['Servers'][$i]['compress'] = false;
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysql';
    $cfg['Servers'][$i]['AllowNoPassword'] = false;
    
    /*
     * phpMyAdmin configuration storage settings.
     */
    
    /* User used to manipulate with storage */
     $cfg['Servers'][$i]['controluser'] = 'pma';
     $cfg['Servers'][$i]['controlpass'] = 'passwordthatiusehere';
    
    /* Storage database and tables */
     $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
     $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';
    /* Contrib / Swekey authentication */
    // $cfg['Servers'][$i]['auth_swekey_config'] = '/etc/swekey-pma.conf';
    
    /*
     * End of servers configuration
     */
    
    /*
     * Directories for saving/loading files from server
     */
    $cfg['UploadDir'] = '';
    $cfg['SaveDir'] = '';
    
    /**
     * Defines whether a user should be displayed a "show all (records)"
     * button in browse mode or not.
     * default = false
     */
    //$cfg['ShowAll'] = true;
    
    /**
     * Number of rows displayed when browsing a result set. If the result
     * set contains more rows, "Previous" and "Next".
     * default = 30
     */
    //$cfg['MaxRows'] = 50;
    
    /**
     * Use graphically less intense menu tabs
     * default = false
     */
    //$cfg['LightTabs'] = true;
    
    /**
     * disallow editing of binary fields
     * valid values are:
     *   false  allow editing
     *   'blob' allow editing except for BLOB fields
     *   'all'  disallow editing
     * default = blob
     */
    //$cfg['ProtectBinary'] = 'false';
    
    /**
     * Default language to use, if not browser-defined or user-defined
     * (you find all languages in the locale folder)
     * uncomment the desired line:
     * default = 'en'
     */
    //$cfg['DefaultLang'] = 'en';
    //$cfg['DefaultLang'] = 'de';
    
    /**
     * default display direction (horizontal|vertical|horizontalflipped)
     */
    //$cfg['DefaultDisplay'] = 'vertical';
    
    
    /**
     * How many columns should be used for table display of a database?
     * (a value larger than 1 results in some information being hidden)
     * default = 1
     */
    //$cfg['PropertiesNumColumns'] = 2;
    
    /**
     * Set to true if you want DB-based query history.If false, this utilizes
     * JS-routines to display query history (lost by window close)
     *
     * This requires configuration storage enabled, see above.
     * default = false
     */
    //$cfg['QueryHistoryDB'] = true;
    
    /**
     * When using DB-based query history, how many entries should be kept?
     *
     * default = 25
     */
    //$cfg['QueryHistoryMax'] = 100;
    
    /*
     * You can find more configuration options in Documentation.html
     * or here: http://wiki.phpmyadmin.net/pma/Config
     */
    $cfg['AllowUserDropDatabase'] = true;
    ?>
    
  3. Just to help others:

    In my case, nothing on this page worked on my Debian (Linux) installation. So I simply reinstalled the package (aptitude remove phpmyadmin, php install phpmyadmin). I then reloaded the phpmyadmin page in the web browser and had to grant full privileges to the phpmyadmin user for it to fully work with no errors.

  4. I had an issue with the phpmyadmin not recognizing the options. It turned out that the settings are cached within the php session, so re-logging fixed the case for me.

  5. S Briggs says:

    Took me 2days but finally worked and showed all OK Don’t know the functions but don’t like seeing any ERRORS – thanks guys.

    But one more.. I added $cfg[‘Servers’][$i][‘tracking’] =

  6. Arild says:

    Hi guys, I must be doing something wrong, because I cannot get this to work. I have made the foreign keys, but I need to get the select field thing to work.

    I am on GoDaddy, but have uploaded the phpmyAdmin as well (on my ftp). From GoDaddys control center I have access to a phpmyAdmin with not as many functions.

    I have contact with the database I created from both places, and rights to do most stuff (but the create new database thing is as many others not allowed).

    In config.inc.php I have to define mydbname.db.xxxxx.hostedresource.com and not @localhost

    I am complete novice on this parts so my questions may seem a little strange. As for the second database with the various pmtables (had to call it phpmyAdmin222 from GoDaddys control center, the only place I can create new dbs) – do I have to be logged in to BOTH this db and my db at the same time? I can not get that to work in config.inc.php…

    And this controluser, for the phpmyAdmin222 database, I cannot get it to work. Do I define a password for him or is he predefined with the password actually being the word ‘secret’? I have tried both that and my own password for this user, but I always get Connection for controluser as defined in your configuration failed.

    Do I have to make the user pma in a script and then grant him access in config.inc.php on next log in?

    I have combined from about 4 tutorials/blogposts/comments but I still seem to not quite ‘get’ the aha moment for this whole connecting thing, if you know what I mean!

    Hope someone can help me or push me in the right direction!

  7. Pingback: phpMyAdmin | Warren Tang's Blog

  8. Quidagis says:

    “If you

  9. karthik says:

    Thanks a lot… Uncommented the controluser and passwd entry in config then I log out

    It Works fine after that

    Cheers Karthik

  10. Anonymous says:

    Missing one line from config file: $cfg[‘Servers’][$i][‘tracking’] = ‘pma_tracking’;

  11. boardtc says:

    Thanks for the writeup and links. I was banging my head of the wall with it not working in FF3.5 (cleared cache as well) and then I tried it in chrome and the

  12. Will says:

    I had to play with it a lot to get it to work, and to tell you the truth it wasn’t really that great when I did get it to work. I tried to document my steps as best I could here, but the truth is that after I got it to work and found out it wasn’t that great I stopped playing with it.

    Just make sure you create the extra database and user correctly, set permissions correctly, and then give that information to phpMyAdmin in the config file.

  13. Roy Vincent says:

    Is this the step to enable the relation view in phpmyadmin in wamp? i thinks its not working at me after following the steps…. i restart my wamp and after all the things I did nothings happen… relation view still not showing up :(

  14. Anonymous says:

    “If you

  15. Anonymous says:

    i have that massege in trouble shoot and not know how i work with at all please help

Leave a Reply

Your email address will not be published. Required fields are marked *