Love the new phpMyAdmin but....

Gareth-AWD

Well-Known Member
Jul 3, 2008
195
13
68
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?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,201
363
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.
 

rezman

Well-Known Member
Feb 3, 2011
45
0
56
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.
 

jimlongo

Well-Known Member
Mar 20, 2008
242
20
68
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?
 
Last edited:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,201
363
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

Well-Known Member
Mar 20, 2008
242
20
68
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.
 

jimlongo

Well-Known Member
Mar 20, 2008
242
20
68
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.
 

jimlongo

Well-Known Member
Mar 20, 2008
242
20
68
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

Well-Known Member
Mar 20, 2008
242
20
68
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

Well-Known Member
Jan 17, 2004
46
1
158
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?
 

jimlongo

Well-Known Member
Mar 20, 2008
242
20
68
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.
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,607
76
308
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.