Love the new phpMyAdmin but....

[Gareth-AWD]

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?

 

[cPanelMichael]

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.

 

[PlotHost]

To manage your MySQL databases you can also use a software like MySQL Workbench or HeidiSQL . I don't like phpMyAdmin at all...

 

[rezman]

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.

 

[jimlongo]

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.

How can I do this so it doesn't get replaced?

 

[cPanelMichael]

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.

 

[jimlongo]

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

/* 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.

 

[jimlongo]

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.

 

[Infopro]

I'm not sure I'm clear on the direction you're heading. But, installing phpMyAdmin on an account is straight forward. Documentation is located here:
Installation - phpMyAdmin 4.1-dev documentation

 

[jimlongo]

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

 

[jimlongo]

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.

 

[kamm]

So I wanted to do this too...

1) From phpMyAdmin I ran the following queries:

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)...

/* 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?

 

[jimlongo]

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.

/* 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.

 

[cPanelKenneth]

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:

#!/bin/sh

cp -f /root/config_backup/config.inc.php /usr/local/cpanel/base/3rdparty/phpMyAdmin

4. Make the postupcp script executable:

chmod 0700 /usr/local/cpanel/scripts/postupcp

Note: I haven't tested the above. It might contain typos.

 

[WiredChris]

I just want anyone else looking into this that jimlongo's solution worked for me.