whm "Manage Databases" does not see database copied in phpMyAdmin

[danfbach]

Hello,

I recently copied a database in phpMyAdmin using the "Operations" interface.
After copying, I intended to use whm's "manage databases" interface to adjust the permissions on the copied database as it was only to be use for development purposes. Unfortunately, the database does not show. I tried copying using a different naming scheme and it still doesn't show.
I can't tell if this is a bug or something I'm doing wrong - it seems like a bug.
Is there a script that i can run to update whm's database list? The copied databases do not show in any of the database management interfaces.

These are the options i have set when copying the database.

 

[Spirogg]

Hello,

I recently copied a database in phpMyAdmin using the "Operations" interface.
After copying, I intended to use whm's "manage databases" interface to adjust the permissions on the copied database as it was only to be use for development purposes. Unfortunately, the database does not show. I tried copying using a different naming scheme and it still doesn't show.
I can't tell if this is a bug or something I'm doing wrong - it seems like a bug.
Is there a script that i can run to update whm's database list? The copied databases do not show in any of the database management interfaces.

These are the options i have set when copying the database.

View attachment 77449

have you tried to restart MySQL or MariaDB which ever one you use ?

• Home /Restart Services /SQL Server (MySQL)

maybe it needs it so you can see the database ?

 

[danfbach]

I did already try restart mariadb using
systemctl restart mariadb
No change.
However, it looks like phpMyAdmin failed to copy the grants/privileges on the database so manually updating them through terminal made it possible for me to use the copied database
But since root has privilege to all databases, and i assume whm is using the root login in the background, the grants shouldn't really matter for this, right?

 

[Spirogg]

However, it looks like phpMyAdmin failed to copy the grants/privileges on the database so manually updating them through terminal made it possible for me to use the copied database

seems that little check mark (Adjust Privileges) should of done this for you, but did not ?

6.39 What is the “Adjust privileges” option when renaming, copying, or moving a database, table, column, or procedure?
When renaming/copying/moving a database/table/column/procedure, MySQL does not adjust the original privileges relating to these objects on its own. By selecting this option, phpMyAdmin will adjust the privilege table so that users have the same privileges on the new items.

For example: A user ‘bob’@’localhost’ has a ‘SELECT’ privilege on a column named ‘id’. Now, if this column is renamed to ‘id_new’, MySQL, on its own, would not adjust the column privileges to the new column name. phpMyAdmin can make this adjustment for you automatically.

Notes:

[LIST]
[*]While adjusting privileges for a database, the privileges of all database-related elements (tables, columns and procedures) are also adjusted to the database’s new name.
[*]Similarly, while adjusting privileges for a table, the privileges of all the columns inside the new table are also adjusted.
[*]While adjusting privileges, the user performing the operation musthave the following privileges:
[LIST]
[*]SELECT, INSERT, UPDATE, DELETE privileges on following tables: mysql.`db`, mysql.`columns_priv`, mysql.`tables_priv`, mysql.`procs_priv`
[*]FLUSH privilege (GLOBAL)
[/LIST]
[/LIST]
Thus, if you want to replicate the database/table/column/procedure as it is while renaming/copying/moving these objects, make sure you have checked this option.

wonder if this is a bug in phpmyadmin ?
@cPRex

But since root has privilege to all databases, and i assume whm is using the root login in the background, the grants shouldn't really matter for this, right?

I would think you are correct, but I would wait for @cPRex to reply to get that correct.

 

[andrew.n]

How have you copied the database exactly? Was this from WHM - PHPMyAdmin - Operations or from cPanel - PHPMyAdmin - Operations? If this was done from WHM directly that is not the right way to do. You should create the database from inside the cPanel account then login to PHPMyAdmin and import the sql file there.

P.S.: you can use the Transfer Tool to only transfer databases of an account

 

[danfbach]

How have you copied the database exactly? Was this from WHM - PHPMyAdmin - Operations or from cPanel - PHPMyAdmin - Operations? If this was done from WHM directly that is not the right way to do. You should create the database from inside the cPanel account then login to PHPMyAdmin and import the sql file there.

P.S.: you can use the Transfer Tool to only transfer databases of an account

I did it from whm - phpMyAdmin, did not create it in the cpanel account first. However, i feel that i have done it this way previously and is has worked...perhaps I'm mistaken.

Also, I wasn't transferring the account, just needed to make a local copy of the current database.

 

[cPRex]

I would not expect a database created in this manner *at the WHM level* to show up in the cPanel interface for the user. Can you try doing it as the cPanel user? I recently tested that as part of my work on another case and that does let the database show up and be used inside cPanel.

 

[danfbach]

Okay, so I created the database in cpanel. Then when I try to use the database copy operation from phpMyAdmin I get the error:

#1007 - Can't create database 'leecom_xcart_dev_3'; database exists

I didn't really care if it showed in cpanel. I was trying to copy it in the Operations tab of phpMyAdmin and then expected it to be found in the WHM "Manage Databases" interface where i could then manage the permissions.

 

[cPRex]

On my personal server, I did the following:

-log in to cPanel as the user
-installed WordPress through WordPress Toolkit just so I could have a database with some data to copy
-open PHPMyAdmin
-selected the WordPress database from the left column
-clicked the "Operations" tab at the top
-used the "Copy database to" section and made sure to name the new database "cpanelusername_dbname"
-clicked the "Go" button at the bottom right of that section
-confirmed the database was copied and the data was the same:



At this point I see the database in both WHM >> Manage Databases and cPanel >> MySQL databases.

 

[danfbach]

See, that is exactly the steps I followed - and have multiple times with the same result.
I was copying "leecom_xcart" => "leecom_xcart_dev"
The cpanel user is leecom.
The database did copy all the data, however as i previously mentioned the permissions did not.
I have been opening phpMyAdmin from WHM, not cpanel. I've just opened it through the cpanel user rather than WHM - i'll report back shortly to see if that makes a difference.

 

[danfbach]

Okay so that is it.
Opening phpMyAdmin through whm fails to copy database permissions and does not show the database in either cpanel or whm after copying completes.
Opening phpMyAdmin through cpanel and copying from there retains the database permissions and the database is now visible in both cpanel and whm.

I'm not sure if this is a bug...and if it is, if it falls on whm or phpMyAdmin - either way, the work around exists.

now I've just got to delete the 10 copies of my database lol
Thanks All!

 

[cPRex]

I'm glad that's working well. It's not a bug, as with WHM there isn't any user assigned to the PHPMyAdmin session - you're just copying data. With PHPMyAdmin, your permissions and ownership is in the user session, so the database is created much like it would be inside the cPanel interface.