The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Love the new phpMyAdmin but....

Discussion in 'Database Discussions' started by Gareth-AWD, Aug 14, 2013.

  1. Gareth-AWD

    Gareth-AWD Well-Known Member

    Joined:
    Jul 3, 2008
    Messages:
    144
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    London, UK
    cPanel Access Level:
    Root Administrator
    I love the new phpmyadmin 4, 3.5 was a dog to use with it often allowing only 1 query before no longer responding.

    It's annoying it only shows a small amount of database and tables before you have to go to the next page. You can change it in settings but it's only for the session.

    I know you change in the config but how do you stop cpanel over-riding it on future updates?
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,762
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    You will notice the following text at the top of the "Settings" page within phpMyAdmin:

    "Your preferences will be saved for current session only. Storing them permanently requires phpMyAdmin configuration storage."

    You can click on the "phpMyAdmin configuration storage" link within phpMyAdmin to see the instructions for enabling the ability to permanently store changes.

    Thank you.
     
  3. PlotHost

    PlotHost Well-Known Member

    Joined:
    Apr 29, 2011
    Messages:
    253
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    US
    cPanel Access Level:
    Root Administrator
    Twitter:
    To manage your MySQL databases you can also use a software like MySQL Workbench or HeidiSQL . I don't like phpMyAdmin at all...
     
  4. rezman

    rezman Well-Known Member

    Joined:
    Feb 3, 2011
    Messages:
    45
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    USA
    cPanel Access Level:
    Root Administrator
    There are so many things about this new phpMyAdmin that make rage that I don't even know where to start.

    My biggest are:
    - When using Save to browser's storage it no longer gives a quick "Load from storage" link when coming back to phpMyAdmin.
    - Settings > Main Panel > Browser Mode > Iconic table operations: Setting this to "Yes" so it's icons only will also set all the tabs along the top, which aren't part of the table structure or table browse mode, to icons only as well.
    - The left frame tree structure. Hate it.


    Sure this is mostly on the phpMyAdmin developers and not so much on Cpanel. I just had to vent a little somewhere. This is also why on my other servers I run an older version of phpMyAdmin and have it locked down so tight that even the NSA can't see it.
     
  5. jimlongo

    jimlongo Well-Known Member

    Joined:
    Mar 20, 2008
    Messages:
    145
    Likes Received:
    2
    Trophy Points:
    18
    I spent about an hour reading phpMyAdmin pages and found the instructions and file for editing the configuration of phpMyAdmin.

    By editing the file at /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php you can adjust the appearance and default behaviour of phpMyAdmin. It took me a while but after a bunch of editing I got my phpMyAdmin close to back to the way it used to be, actually improved on some default behaviours.

    Then I found that that file got replaced - I'm guessing by the overnight cPanel update. :mad:

    How can I do this so it doesn't get replaced?
     
    #5 jimlongo, Aug 22, 2013
    Last edited: Aug 22, 2013
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,762
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    It's likely better to install a local copy of phpMyAdmin onto an account, as opposed to modifying the default configuration of phpMyAdmin through the edit of /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php. Otherwise, cPanel updates will modify the file. While you could edit the file attributes so it's not writable, this is not a good idea due to the potential for issues when new versions of phpMyAdmin are included with cPanel.

    Thank you.
     
  7. jimlongo

    jimlongo Well-Known Member

    Joined:
    Mar 20, 2008
    Messages:
    145
    Likes Received:
    2
    Trophy Points:
    18
    Okay I'm willing to try using the configuration storage, but I wish there was better instructions, it's a little vague.

    I have the create-tables.sql, it tells me at the top I have to create a user 'pma' and set up this user in the same config.inc.php file.
    So I go to that file and uncomment the following
    Code:
    /* User used to manipulate with storage */
     $cfg['Servers'][$i]['controlhost'] = '';
     $cfg['Servers'][$i]['controluser'] = 'pma';
     $cfg['Servers'][$i]['controlpass'] = 'pmapass';
    
    /* 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]['table_uiprefs'] = 'pma__table_uiprefs';
     $cfg['Servers'][$i]['tracking'] = 'pma__tracking';
     $cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
     $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
     $cfg['Servers'][$i]['recent'] = 'pma__recent';

    First question I have is this file is going to be replaced again. So aren't I still faced with the issue of losing my configuration?

    Secondly it asks me at the top of the create-tables.sql to create this user pma.
    When I click on create user in the Users tab of phpMyAdmin I get an empty page.

    Finally if I get this user created and run the create-tables.sql does anyone know where the instructions on how to set up this configuration are?

    Thanks for any insight here.
     
  8. jimlongo

    jimlongo Well-Known Member

    Joined:
    Mar 20, 2008
    Messages:
    145
    Likes Received:
    2
    Trophy Points:
    18
    Okay setup the user

    GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';
    GRANT SELECT (
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    Execute_priv, Repl_slave_priv, Repl_client_priv
    ) ON mysql.user TO 'pma'@'localhost';
    GRANT SELECT ON mysql.db TO 'pma'@'localhost';
    GRANT SELECT ON mysql.host TO 'pma'@'localhost';
    GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO 'pma'@'localhost';

    run the create-tables.sql which has one error in the pma_tracking table


    this also causes an error
    GRANT SELECT, INSERT, UPDATE, DELETE ON <pma_db>.* TO 'pma'@'localhost';

    Think I have to go look for a step by step on how to do this.
    Shouldn't be so hard. Cpanel should have some documentation since I assume this is something most people are going to want.
     
  9. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,465
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
  10. jimlongo

    jimlongo Well-Known Member

    Joined:
    Mar 20, 2008
    Messages:
    145
    Likes Received:
    2
    Trophy Points:
    18
    I only want a central phpMyAdmin that I can access all databases on all accounts. Currently i use the one located in WHM > SQL > phpMyAdmin
     
  11. jimlongo

    jimlongo Well-Known Member

    Joined:
    Mar 20, 2008
    Messages:
    145
    Likes Received:
    2
    Trophy Points:
    18
    I think there should be an easier way to setup storage configuration for the WHM>SQL Services>phpMyAdmin. The phpMyAdmin instructions for this aren't very clear, and don't seem to work properly with cPanel.

    If you think that's a good idea vote up this feature request.
     
  12. kamm

    kamm Active Member

    Joined:
    Jan 17, 2004
    Messages:
    38
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    So I wanted to do this too...

    1) From phpMyAdmin I ran the following queries:

    Code:
    GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'examplePassword';
    GRANT SELECT ( Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv,
    Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv,
    Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv,
    Repl_client_priv ) ON mysql.user TO 'pma'@'localhost'; 
    GRANT SELECT ON mysql.db TO 'pma'@'localhost'; 
    GRANT SELECT ON mysql.host TO 'pma'@'localhost'; 
    GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO 'pma'@'localhost';
    2. Updated the config.inc.php found here:
    /usr/local/cpanel/base/3rdparty/phpMyAdmin
    uncommenting these variables (leaving the rest of the file as it was)...
    Code:
    /* User used to manipulate with storage */
     $cfg['Servers'][$i]['controlhost'] = '';
     $cfg['Servers'][$i]['controluser'] = 'pma';
     $cfg['Servers'][$i]['controlpass'] = 'examplePassword'; 
    
    /* 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]['table_uiprefs'] = 'pma__table_uiprefs';
     $cfg['Servers'][$i]['tracking'] = 'pma__tracking';
     $cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
     $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
     $cfg['Servers'][$i]['recent'] = 'pma__recent';
    3. Ran the queries from create_tables.sql found here:
    /usr/local/cpanel/base/3rdparty/phpMyAdmin/examples

    ...after all that I found that I still had:
    Your preferences will be saved for current session only. Storing them permanently requires phpMyAdmin configuration storage.

    ... and phpMyAdmin throws the following error when doing a basic query.
    #1142 - SELECT command denied to user 'pma'@'localhost' for table 'pma__table_uiprefs'

    What did I do wrong?
     
  13. jimlongo

    jimlongo Well-Known Member

    Joined:
    Mar 20, 2008
    Messages:
    145
    Likes Received:
    2
    Trophy Points:
    18
    I wish I could tell you how I finally got this to work.
    I pretty much started over first deleting the user pma and the database phpmyadmin.

    Then created a user pma
    CREATE USER 'pma'@'localhost' IDENTIFIED BY 'mypassword';

    Then I edited the create_tables.sql to remove the double underscores (pma__userconfig change to pma_userconfig, etc., ) it just made the database flatter. That sql file sets the privileges for the user pma.

    Then I edited config.inc.php to pretty much what you have. (add localhost to firstline and remove the double underscores for all the table names.

    Code:
    /* User used to manipulate with storage */
     $cfg['Servers'][$i]['controlhost'] = 'localhost';
     $cfg['Servers'][$i]['controluser'] = 'pma';
     $cfg['Servers'][$i]['controlpass'] = 'mypassword';
    
    /* 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]['table_uiprefs'] = 'pma_table_uiprefs';
     $cfg['Servers'][$i]['tracking'] = 'pma_tracking';
     $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
     $cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';
     $cfg['Servers'][$i]['recent'] = 'pma_recent';

    For the longest time I was trying to manually add $cfg settings to the pma_userconfig table without any success, BUT FINALLY for no reason I can explain - editing the settings within phpmyAdmin stored them in the pma_userconfig table and they are saved and loaded on each login.

    I will note this was a very frustrating exercise and for the longest time I would get configuration errors that wouldn't allow me to login to phpmyAdmin, had to keep commenting out the config section in config.inc.php and trying over, it seems very simple now that it's setup, but it wasn't easy getting it to work.

    Good luck.
     
  14. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,460
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    You can use the scripts/postupcp hook to restore your custom phpMyAdmin configuration file.

    1. Make your desired changes to /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php
    2. Copy /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php to a safe location, such as /root/config_backup
    3. Create /usr/local/cpanel/scripts/postupcp with the following contents:
    Code:
    #!/bin/sh
    
    cp -f /root/config_backup/config.inc.php /usr/local/cpanel/base/3rdparty/phpMyAdmin
    
    4. Make the postupcp script executable:
    Code:
    chmod 0700 /usr/local/cpanel/scripts/postupcp
    
    Note: I haven't tested the above. It might contain typos.
     
  15. WiredChris

    WiredChris Member
    PartnerNOC

    Joined:
    Aug 7, 2013
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    I just want anyone else looking into this that jimlongo's solution worked for me.
     
Loading...

Share This Page