0

I gave SELECT privilege to a user on a particular database, Now whenever I try to connect remotely

$ mysql -h ip-of-remote -u username -p

with that user credentials and try to change the database it takes to long to get Database changed while it connects (goes mysql prompt) very fast

mysql> USE db_name;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

On the contrary, when I ssh the mysql server and then try to login mysql prompt locally (without host) with the same user credentials and then try to change database it quickly changes and I don't get that message

mysql> USE db_name;
Database changed
mysql>

I tried to --disable-auto-rehash in my.cnf but no luck. Please help

[mysql]
no-auto-rehash
5
  • 3
    Did you try $ mysql -A -h ip-of-remote -u username -p? Apr 30, 2015 at 21:00
  • On doing that I don't get the delay and that message. But I can't recommend that to every user to append -A every time. I need something at the server configuration level
    – koustuv
    Apr 30, 2015 at 21:02
  • I see, then you should restart the server after adding no-auto-rehash to the cnf file Apr 30, 2015 at 21:03
  • Already restarted after making those changes
    – koustuv
    Apr 30, 2015 at 21:05
  • I'd guess your database host tries to resolve your IP address to a host name or vice versa because of privileges attached to hosts. Wild guess, but you might want to check your DNS setup and/or use ip-addresses/host names when granting privileges. Apr 30, 2015 at 21:16

1 Answer 1

1

since the the following command use my_database; executed too fast when you try this locally (on the same server), and take a log time when you executed remotely, there is some common causes for that like the connection between the server and client is slow, or there is a delay (lag) on attempting to retrieve and verify reverse DNS, you can try to add the following parameter skip_name_resolve for testing to my.ini or my.cnf under [mysqld] section.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.