cancel
Showing results for 
Search instead for 
Did you mean: 

MySQL Administrator on migrated server

diyaddict
Newbie
Posts: 7
Registered: ‎22-03-2014

MySQL Administrator on migrated server

Has anyone had any luck using MySQL Administrator to access their database on the new server?
I used MySQL Administrator on the old server (rumpus) to make daily backups of my database. However, when used on the migrated server (with the new host, user, password settings), it can't find my database.
Thanks in advance for any insight
19 REPLIES 19
bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: MySQL Administrator on migrated server

I can connect using my Hostopia details however I encounter access violation errors when trying to create a new backup project. This doesn't seem to be a problem when connecting to the Plusnet database. Is this the same problem you're encountering?
Can't say I'm overly familiar with the client to be honest.

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵

diyaddict
Newbie
Posts: 7
Registered: ‎22-03-2014

Re: MySQL Administrator on migrated server

Yep, exactly the same problem. Access violation in a local module libmysqlx.dll, when the Backup menu is accessed.
Anyone know how to get around this?
diyaddict
Newbie
Posts: 7
Registered: ‎22-03-2014

Re: MySQL Administrator on migrated server

Having done some research, it looks like the new MYSQL has very tight security, and access rights for remote users need to be granted.
Can this be done via phpMyAdmin?
bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: MySQL Administrator on migrated server

You say that, however I can access my databases using Adminer uploaded to a non-Hostopia server. I'd expect similar problems if the issue was related to remote access? Huh
As an interim measure, you could use phpMyAdmin or Adminer to periodically dump your database content?

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵

diyaddict
Newbie
Posts: 7
Registered: ‎22-03-2014

Re: MySQL Administrator on migrated server

Hi Bob
You have a good point, but I still think it's a privileges issue.
Using phpMyAdmin:
If I use the SQL query SHOW DATABASES on the old server, RUMPUS, it works and lists my database name
But If I use the same query on the new server, I get an error message:
#1227 - Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation
My guess is that MySQL Administrator can't find the database because I don't have any SHOW DATABASES privilages, hence it crashes.
Is it possible to set the privilege on the new server?
Paul
diyaddict
Newbie
Posts: 7
Registered: ‎22-03-2014

Re: MySQL Administrator on migrated server

Having checked the server variables on phpMyAdmin, the 'skip show database' variable is ON
I think this is why mySQLAdministrator can't find my databases.
I guess I need to raise a ticket to see if I can get it set to OFF?
Paul
bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: MySQL Administrator on migrated server

I'll query the situation with Hostopia, however there may be a reason why they've elected to remove such privileges.

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵

jelv
Seasoned Hero
Posts: 26,785
Thanks: 971
Fixes: 10
Registered: ‎10-04-2007

Re: MySQL Administrator on migrated server

The same issue affects mySQL Query browser.
jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£14.40/month)
Mobile: iD mobile (£4/month)
bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: MySQL Administrator on migrated server

Noted. Thanks jelv.

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵

diyaddict
Newbie
Posts: 7
Registered: ‎22-03-2014

Re: MySQL Administrator on migrated server

Thanks Bob - let me know what you find, Paul
bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: MySQL Administrator on migrated server

As I suspected, it's intentional.
Quote
Due to the way our environment is configured, the show database privilege would allow a user to receive list of all databases on the server cluster. This would probably take several minutes to execute and it's not preferred from a security perspective. Table SCHEMATA from database information_schema can be used to retrieve a database list instead of SHOW DATABASES.

That obviously doesn't help as far as the aforementioned SQL clients are concerned. I've asked whether or not Hostopia can suggest a work around for this. I'm not convinced there is one though without resorting to another client.

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵

spraxyt
Resting Legend
Posts: 10,063
Thanks: 674
Fixes: 75
Registered: ‎06-04-2007

Re: MySQL Administrator on migrated server

Running

select * from information_schema.SCHEMATA

in phpMyAdmin only shows the database currently selected in the left-hand panel for me. If I choose a different one it shows that instead.
David
David
jelv
Seasoned Hero
Posts: 26,785
Thanks: 971
Fixes: 10
Registered: ‎10-04-2007

Re: MySQL Administrator on migrated server

Bob,
Ask them to turn the --skip-show-database option off!
[quote=https://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_show-databases]The SHOW DATABASES privilege enables the account to see database names by issuing the SHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the --skip-show-database option. Note that any global privilege is a privilege for the database.
This is not any sort of security risk as users will only see their own databases - in fact I suspect they might only see the one database which has the user and password they used to connect.
Edit: This isn't something I knew about but it took me all of about two minutes to find that information using Google! Not impressed that apparently neither they nor you found this.
jelv (a.k.a Spoon Whittler)
   Why I have left Plusnet (warning: long post!)   
Broadband: Andrews & Arnold Home::1 (FTTC 80/20)
Line rental: Pulse 8 Home Line Rental (£14.40/month)
Mobile: iD mobile (£4/month)
bobpullen
Community Gaffer
Community Gaffer
Posts: 16,869
Thanks: 4,950
Fixes: 315
Registered: ‎04-04-2007

Re: MySQL Administrator on migrated server

Quote from: jelv
Ask them to turn the --skip-show-database option off!

That's exactly what I've done and I'm fairly sure they're aware of the consequences. Hostopia's have quite a unique set up though, they develop from source and requests for changes are typically evaluated on customer demand (bear in mind any changes would be applied to everybody sharing their European server farm). There may be some aspect of their set-up that wouldn't play nicely with this variable disabled.
This is essentially what I said when asking for clarification:
[quote author="Bob Pullen"]Looking at the MySQL manual here - http://dev.mysql.com/doc/refman/5.0/en/show-databases.html
Suggests that you could disable the --skip-show-database variable and allow customers the ability to run SHOW DATABASES *without* it listing all databases on your server i.e. customers would only see the databases that they have some sort of privilege for.
Indeed, the Plusnet platform our customers have been migrated from does not exhibit this problem and neither do users have the global SHOW DATABASES privilege e.g. SHOW GRANTS for a user on the Plusnet platform returns:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `pnhosting2\_pn`.* TO 'pnhosting2'@'%' WITH GRANT OPTION

Yet SHOW DATABASES is possible and the query output only lists the customer’s database (rather than every database on our server)

Bob Pullen
Plusnet Product Team
If I've been helpful then please give thanks ⤵