Versions Compared

Old Version 5

changes.mady.by.user Wara Hermosa

Saved on

compared with

New Version 6

changes.mady.by.user Wara Hermosa

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

1. Client hosts' file
There is an excellent article on the hosts file here

Newtab2
aliasWhat is hosts?
urlhttp://accs-net.com/hosts/what_is_hosts.html
. Try to find your own hosts file. You will find this line in it:

...

to your hosts file!
2. A local nameserver.
A local nameserver can be installed explicitly or implicitly. If you have more windows computers connected on a windows' network (using the TCP protocol - not old NETBEUI protocol etc) you already have a local nameserver that was implicitly installed. If you have two machines on your network named 'Bonnie' and 'Clyde', then you can connect to the MySQL server running on 'Bonnie' from the computer 'Clyde' with SQLyog entering 'Bonnie' as the host name. Because the nameserver that was implicitly installed as a part of the Windows network resolves the name 'Bonnie' to 'Bonnie's ip on the network
3. A Domain Name Server
Domain name Servers are part of the Internet structure. Any name/string in the format domain.topleveldomain (ie: school.edu, business.com, whisky.org etc. ) can be looked up on the Domain Name Servers of the Internet. The ip returned is then used for creating the connection.

...

With HTTP-tunneling 'host' can simply be 'mysqlserver' (ie: 'mydb1' or 'mysql' or whatever). ' mysqlserver.thisisanisp.com' will work too, but there is no need for an extra domain lookup! If it is the same computer that runs the Web-server (with PHP-interpreter) and the MySQL server it can also be 'localhost' or the ip '127.0.0.1'. PHP will connect to MySQL through a Unix-Socket if 'localhost' is specified and TCP if '127.0.0.1'. Both will normally work. TCP may be a little bit slower on Linux machines than Socket, so you can try 'localhost' first. However there are situations where 'localhost' will return an error like error: 'Error No. 2013: Lost connection to MySQL server during query'. This is likely because PHP looks for the socket file where it does not exist. The underlying reason could be that MySQL and PHP have been installed from different repositories using different file positions for the socket file and the mysql datadir.

...

Even if the TCP-port (3306) normally used by the MySQL server is blocked (as often is the case at ISP's) SQLyog still let you connect using HTTP(s)-tunneling or SSH-tunneling. And if you are behind a proxy SQLyog can handle that too. The client for SSH connection is installed with SQLyog, and a PHP-script for HTTP-tunneling is too. That script must be uploaded to your webhost. The PHP-script uses php_mysql extension that is available practically everywhere where MySQL is available.

...

If SQLyog is taking long time (more than 5-10 seconds) to connect to your MySQL server using SSH tunneling then there could be problem with the name resolution.
The first thing that the SSH server does before forwarding the connection to the specified MySQL server is to perform a reverse DNS lookup on the client’s IP. The SSH server may cause an unnecessary delay during authentication due to incorrect or absent of reverse DNS settings. So in case of any slowness you should check those settings.
You can also disable most of the server-side lookups by setting UseDNS = "no" in SSHD configuration file (/etc/ssh/sshd_config on most systems). But in that case the MySQL host must be specified with an ip and not a hostname.

SSH error messages

Starting from SQLyog version 5.15 we have improved the error message handling in relation to SSH-tunnel. The most common error messages now are:

SSH - related:

...

The above error may also result from a hardware error on the remote network.

Also those:

FATAL ERROR: network error: Software caused connection abort.

FATAL ERROR: Network ERROR: no route to host

... may be hardware related (they both can both be triggered by disconnecting the network cable).  But likely that there are more reasons.

"SSH ERROR: Unable to open connection; Host does not exist"

...occurs in case SSH Host does not exist (for instance in case of unsuccessful DHCP or DNS lookup - here there is no active refusal from the server, as there was not even an attempt to connect it!)

Also see the note to the MySQL client error 2013 "Lost connection ..." below!

MySQL - related:

...

  • If MySQL user/pw is wrong result is MySQL server error 1045: "Access denied ..." as with any type of connection.
  • If MySQL port is wrong result is MySQL client error 2013 "Lost connection ...".
    Note that this error also occurs if port forwarding is disabled in SSH configuration (the configuration parameter 'AllowTcpForwarding' is set to 'no' in the 'sshd_config' file). It (here) simply tells that there is no connection from SSH to MySQL for some reason. But the mySQL client API 'thinks' there was one connection and that is why is says 'Lost connection ...' and not 'Can’t connect...'. There was one successful connection - but not to the MySQL server - to the SSH daemon only! But the MySQL client API is not designed to 'see' the difference!
  • If MySQL port is empty or ZERO however result is MySQL client error 2003 "Can’t connect to mysql server ..."
  • If MySQL host is wrong (or cannot be reached from SSH for some reason) result is MySQL client error 2005 "Unknown MySQL server..."

Unspecified error:

And this one is displayed if SSH connection cannot be established for some other reason and it has not been possible to resolve the error to one of the above.
"Could not establish SSH connection. Make sure that the SSH server is running and you are entering correct values for SSH port forwarding."
A concluding note on the popular 'Putty' program and SQLyog SSH-tunneling.

Sometimes when people are having problems with SSH-connections, we often hear "I can connect with Putty without problems". Maybe so, but it does not tell very much (almost nothing actually!) because a connection with Putty or a similar program is not tunneling and does not make use of port forwarding on the remote host. Putty simply creates a remote (and secure) shell on the client machine, and connects to the 'mysql' client program on the server. With Putty the MySQL client is the 'mysql' client on the remote server. With SQLyog SSH-tunnel the MySQL client is the client API that is compiled into SQLyog (and SJA). That is why port forwarding is needed and must be functional with SQLyog SSH-tunneling!MySQL host must be specified with an ip and not a hostname.

What Is SSH and SSH-tunneling?

...

There is a built-in SSH-client in SQLyog that lets you connect to a MySQL server using SSH.

Basically there are two benefits of SSH Tunneling:
* SSH can be used to encrypt communications between SQLyog and your remote MySQL server.
* It lets you access the MySQL server even if the MySQL port (3306) is blocked.

Unlike HTTP-tunneling, you cannot take it for granted that SSH-tunneling is available at your webhost. In general the "more professional" and "more expensive" hosting providers offer SSH and the cheaper ones don't.

Refer to the SQLyog help file for instructions how to set up the SQLyog Connections Manager, if you want to use SSH-tunneling with SQLyog. Each SSH-connection occupies a TCP-port at your local machine. With recent SQLyog versions this port is picked automatically from the pool of high-numbered ports not already in use.

You can read about SSH-related error messages here.

A concluding note on the popular 'Putty' program and SQLyog SSH-tunneling.

...

Expand
titleI am able to connect using phpMyAdmin, but SQLyog will not let me connect.

I am able to connect using phpMyAdmin, but SQLyog will not let me connect.

PhpMyAdmin is running on the server itself so when connecting to MySQL with phpMyAdmin you are NOT connecting from a remote host! With SQLyog you are connecting from a remote host. This is a very important difference as far as user configuration with MySQL is concerned.

The user that you are connecting with maybe has no privilege to connect from remote.
By default, MySQL only gives access for users to connect from localhost. If you want to give some user access to the server from another host you must specify the hostname (an ip or a URL). You can use the SQL wildcards "%" and "_" (but not windows wildcards like "*"). Simply giving permission for a user to access the server from "%" means from everywhere. Read more about the MySQL privileges system in "Error Nº1044".
If the database is at an ISP there usually is some kind of "Control Panel" application available from where to configure users. Often user configuration is only allowed using this tool. It also is very likely that your ISP has blocked direct access to MySQL on port 3306. If that is the case SQLyog offers you the option of using HTTP-tunneling as well as SSH-tunneling.

Expand
titleError Nº1044

Error Nº 1044: "Access denied..."

This one is very similar to "Error no. 1045: Connection denied .."

However this case connection is established OK but access to data is denied. The user exists but most like he does not have any privilege at all. Read more about user privileges in "I am able to connect but cannot see the list of databases/tables" issue listed.

...

Expand
titleI have an account with Yahoo. Can I use SQLYog

I have an account with Yahoo. Can I use SQLyog…

Yes. But several users have had problems getting connection parameters right. Here is what Yahoo say themselves:

Why can't I access my database?

First, make sure that you are using the hostname "mysql" and not "localhost" in any of your PHP or Perl configuration files that require a MySQL hostname.

You will need to use HTTP Tunneling. First upload SQLyogTunnel.php (available with the SQLyog installer). Put the correct URL in the HTTP Tunneling field and use the same credentials as you will use in your PHP pages. The Hostname should be "mysql" (case-sensitive).

Expand
titleError Nº1130

I get error 1130 "Host is not allowed to connect ..." or "Access denied ..." or "Could not connect ..."

Error 1130 is a networking error. The server cannot resolve the hostname of the client. Or the host is not allowed to connect to the MySQL server.

There are basically 2 categories of possible reasons:

  • The simple one:

In MySQL a user a user is specified using BOTH the user name and the host from where the user may connect. If no user has been created where the host-part (using wildcards or not) mathces the host of the client trying to connect MySQL will return this error.

  • Specific for MySQL 5.7:

When upgrading to MySQL 5.7.3 from a previous version this may occur due to changes to the user table introduced in 5.7.3. There is a good blog about this here it,

Newtab2
aliasUpgrade and Resolving ERROR 1130 Host ‘localhost’ is Not Allowed to Connect
urlhttp://www.chriscalender.com
/?p=1392
/5-7-upgrade-and-resolving-error-1130-host-localhost-is-not-allowed-to-connect/
.

  • The tricky ones:
  1. Your hosts file is damaged or invalid. Various vira and spyware attack and alter the host file in various ways. For instance if the hosts file does not contain the line

    127.0.0.1 localhost

    'Localhost' can not be resolved as pointing to ip 127.0.0.1. On some larger corporate network it is widely used to "roll out" host files to all clients with symbolic names (like 'mysqlserver', 'mailserver' etc.) for important machines on the network and the corresponding ip's. Check with your admin that you got the right file!

  2. If you use the Windows network name as hostname, it may be a network configuration problem. Try using the ip instead.

  3. On Unix/Linux systems the hosts files sometimes reads

  4. Is a variation of number 3. With a complex server setup (involving more ip's, domains, subdomains and/or virtual hosts) a similar issue can occur.

    In this situation HTTP-tunneling will normally work for all users, but often/sometimes direct connection and SSH will not work with the 'root' user - everything depending on the server configuration. Try another and 'ordinary' user account. You may mirror the privileges of 'root' to a 'superadmin' user.

    127.0.0.1 localhost.localdomain

    This causes a problem with MySQL. MySQL docs at

    Newtab2
    aliasAccess Control, Stage 1: Connection Verification
    urlhttps://dev.mysql.com/doc/refman/8.0/en/connection-access.html
    say:

    A Host value may be a hostname or an IP number, or 'localhost' to indicate the local host.

    No mention of 'localhost.localdomain'. This means that MySQL can't resolve that 'automatically'! This can affect SQLyog when tunneling and SJA for Linux. Workarounds for this include:

    I: A workaround that has worked is to give the user access from 'localhost.%'

    II: You can add localhost to ip 127.0.0.1 in host file like
    a. Make sure your '/etc/hosts' file reads as follows:
    127.0.0.1 localhost //localhost *MUST* be first (notice separate entries)
    127.0.0.1 localhost.localdomain
    127.0.0.1 . . .
    b. Make sure you reference the local server in the SJA.XML file as:
    127.0.0.1 or localhost.localdomain
    This is known to work in some situations where the MySQL configuration file contains:
    bind-address = 127.0.0.1
    III: It has sometimes worked to add a 'dummy' ip to my.cnf like:
    bind-address = 10.10.10.10
    .. supposed that 10.10.10.x is also the ip of the local machine. Then you use this 'dummy ip' as the host specification when connecting with SQLyog and SJA.
    It seems to be something special for some DEBIAN distributions to use this 'bind-address' construction with MySQL. Solution II) and III) both are solutions that users have contributed at our Forums. Both situations involved DEBIAN.
    IV: You can try any host name that the host file maps to ip 127.0.0.1. There might be several! Even a SAMBA NetBIOS alias might work!
    V: You can use the local ip (ie. 10.0.0.1 or whatever) of the actual machine or a name server alias for this. But in this case normal TCP-connections must be enabled in MySQL configuration - that is 'skip-networking' must be disabled/commented out and no 'bind-address' may be there. Of course then an additional DNS lookup will have to take place for the connection to be established. This is of no practical importance.

    Finally you could test if this connection issue is the same with 'MySQL Administrator'. It uses the same client code (the C-API) and connects exactly as SQLyog and SJA do.

...

Expand
titleError Nº 2005

Error No. 2005: Unknown MySQL server host...

The error message: Error No. 2005: Unknown MySQL server host 'some_URL_or_ip'

Simply means that connection is not possible for the following (or similar) reasons:

A protocol is specified in the "MySQL host address" field of the SQLyog Connection Manager that does not support MySQL connection. It is a common mistake among beginners to use "http://...", instead of just "www.myveryowndomain.com" or "sales.myowncompany.biz" or "localhost" (if the webserver and the MySQL server is running on the same computer). When connecting to a remote network you may need to ask the Sys Admin there for the correct URL to use for addressing the MySQL server.

However if you use HTTP-tunneling the URL-field on the Tunnel -tab of the SQLyog Connection Manager should be a complete URL with the "http://" protocol specified. This is because the tunneling script must be addressed through a webserver (that is the idea of HTTP-tunneling!).

...