1. ALTER the TABLE mysql.users and change the mysql.users.users column from a char(16) to some longer value

1. You can no longer GRANT user rights, but must INSERT/UPDATE the mysql system tables directly

1. You must 'patch' (or rather 'hack') the MySQL API/client code

We have had intense discussions with this the MySQL AB on this issue. From the official correspondence we quote:

"This is simply a lucky fluke of sorts (if it works). MySQL simply does not support longer usernames .... Altering the system tables, aside from using our own mysql_fix_privilege_tables script to keep up with our changes, is simply unsupportable. There are server and client changes needed to properly handle any sort of modifications here, even though in some cases a quirk (as above) may seem to function .... This is, basically, dangerous behavior. We will attempt to curb it as well as we can.... Luckily, our manual states clearly that in both cases, MySQL will not provide support if any problems arise ... That is , it may work and it may not work, but MySQL will not ponder as to why it works or why it does not work ... We simply do not provide support for such cases."

We won't play that game as others do! You should convince your ISP that changing the format of the user table is bad and dangerous practice!

And further: MySQL has 'stopped the game'. Again we quote from the above correspondence:

"To make things even more precise, I will now send a server patch to our development management. This patch adds a code that will truncate user column at 16 chars and other columns to their nominal sizes. This will ensure that future 4.1 and 5.0 versions will not work with longer names, whatever changes some application could envisage."

So with the most recent builds in the MySQL 4.1.x series and with MySQL 5.0 it would not work anyway. There is now code in the server binary itself that truncates any user name to 16 characters.

I get Error Nº 2002. Can't connect to local MySQL server through socket ...

This can occur when connecting using HTTP-tunneling to a MySQL server running on Unix/Linux platforms.

MySQL writes

How to cope with this would depend on which webserver and which php version is used. But here is a workaround that has worked with Apache:
If the directory /var/lib/mysql doesn't exist than create it and chown to user mysql:
"mkdir /var/lib/mysql; chown mysql /var/lib/mysql"
Then edit the /etc/my.cnf file and specify:
[mysqld]
socket=/var/lib/mysql/mysql.socket
[client]
socket=/var/lib/mysql/mysql.socket
And, restart the mysql server ("etc/init.d/mysql restart")
That is enough for MysSQL. However the chances are that PHP was compiled with a different default mysql socket location (e.g. /tmp/mysql.sock). In which case you have to edit the php.ini file and find the variable "mysql.default_socket". Set this to the above value
mysql.default_socket = /var/lib/mysql/mysql.socket
And restart Apache to re-read the php.ini file
If you do not have access to the configuration files and system command-line then you must ask your Sys Admin/support to help with this.

The Client as far as SQLyog goes is either the sqlyog.exe and sja.exe executable files (with its compiled-in MySQL C-API) or - in case you use HTTP-tunneling - your PHP-binary.

SQLyog itself (the sqlyog.exe -executable) handles all MySQL versions from 3.23.x and upwards automatically, and the error message should not occur with direct connection. In case you experience the error when HTTP-tunneling, you can EITHER replace the PHP-binary OR

downgrade

the hash-type for the user used for tunneling (and other PHP based connections) with the following command:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

The command must be written

as

it is.

At ISP's you should expect the Sys Admin there to have MySQL-installations and PHP-binaries that "fit". If you operate your own MySQL server the hashing method may or may not change when upgrading the server from 4.0 to a newer version. That depends on the upgrade method.

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

Connection denied .."

, which you can find it in this list. 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. About user privileges start

, please refer to the issue "I am able to connect but can't see the list of databases/tables" listed in this page.

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.

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.

More information on the MySQL privilege system can be found

If the database is hosted at an ISP/hosting provider it is likely that you must use some

web-based "Control Panel" application to set up user privileges.

ideramysqlsupport@idera.com.

We can

not guarantee that it will be the latest version of SQLyog.

The

opposite

error message appears if you try to connect from an application with a connector (for instance an ODBC-driver) that is too old too be used with the actual MySQL version.

The Hostname should be "mysql" (case-sensitive).

• 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 http://www.chriscalender.com/?p=1392

• 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

   '

1. Localhost'

1. 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.

1. 127.0.0.1 localhost.localdomain

   This causes a problem with MySQL. MySQL docs at

1. Newtab2
   alias Access Control, Stage 1: Connection Verification url https://dev.mysql.com/doc/refman/

1. 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:

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

1. You can add localhost to ip 127.0.0.1 in host file like
   a

1. . 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

1. . 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:

1. 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:

1. 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:

1. 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

1. 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.

: Error No. 1045: Connection denied for 'someuser@somehost' (using password: YES/NO)

It is a user authentication error. The user details specified do not "match" the user tables of the specified MySQL server. Common situations are:

• No such user.

NOTE: MySQL does not use the OS's or domain's user's management. It operates its own user accounts. With a fresh MySQL installation the user ROOT is created with NO PASSWORD. When working with MySQL databases at ISP's an admin user most often must be activated from some web based Control Panel Application before connection to the MySQL server is possible. There could be more "rules" applying here (database and user naming conventions etc). Refer to the docs/support at the ISP for details on that. We can't give them!

• User is not allowed to connect from the actual host. Note that MySQL by default only allows connection from 'localhost'. To specify from where a user may connect SQL wildcards (% and _) can be used. Simply 'someuser@%' means that user 'someuser' may connect from everywhere.

• Wrong password, missing password or password specified where it should not

• If you are upgrading MySQL from an old version (4.0.x or lower) to a more recent (4.1.x or highere) and if you are still using a rather old PHP version you may need to execute this command from command-line client

SET PASSWORD FOR some_user@localhost = OLD_PASSWORD('newpwd');

(where some_user@localhost is the user used for this connection) since the format for storing passwords has changed between 4.0.x and 4.1.x versions.

: 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!).

: Error No. 2003: Can't connect to MySQL server on 'localhost' (or some other host)

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

• There is no MySQL server running at the specified host

• Connection to the MySQL server is not allowed using TCP-IP. Check the 'skip-networking' setting in the MySQL configuration file (my.ini on Windows, my.cnf on Unix/Linux). It shall be commented out like '#skip-networking'. If it is not commented out, then do it and restart the MySQL server for the change to take effect. SQLyog needs to connect using TCP-IP.

• Some networking issue prevents connection. It could be a network malconfiguration or a firewall issue. We have experienced sometimes that some firewalls (ZoneAlarm in particular) is blocking TCP-IP connections even if it claims to be disabled. Most often it will help to uninstall and reinstall the firewall.

• When trying to connect to a MySQL server at an ISP this error message often indicates that direct connection to MySQL has been blocked. You must then use HTTP-tunneling or SSH-tunneling to connect.

• Also this FAQ may have relevant information. It describes some more special situations when connection to MySQL on Linux.