Versions Compared

Old Version 28

changes.mady.by.user Nicolas Rios

Saved on

compared with

New Version 29

changes.mady.by.user Nicolas Rios

Saved on

Key

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

...

Expand
titleI have a very long username for the MySQL database

I have a very long username for the MySQL database at my ISP. SQLyog won't let me use it.

Right! Because a MySQL user name is up to 16 characters long. The MySQL docs clearly state:

MySQL usernames can be up to 16 characters long. Operating system usernames might have a different maximum length. For example, Unix usernames typically are limited to eight characters.

However, it is a bad practice with some ISP's that they generate longer usernames than that. It is typically 'cheaper' hosting providers that offer a single MySQL database as part of a 'personal' or 'small business' subscription plan. They auto-generate the username from the user's domain name and it could be somewhat like mydb_myveryowndomain or similar. It is also true that some of our competitors offer support for that. However it is very bad practice! To make it work you will have to:

  1. ALTER the TABLE mysql.users and change the mysql.users.users column from a char(16) to some longer value
  2. You can no longer GRANT user rights, but must INSERT/UPDATE the mysql system tables directly
  3. 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.

Expand
titleError Nº2002Nº 2002

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

Newtab2
aliasProblems with MySQL sock
urlhttps://dev.mysql.com/doc/refman/8.0/en/problems-with-mysql-sock.html
about this issue.

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.

...

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º1044Nº 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 use in your PHP pages. The Hostname should be "mysql" (case-sensitive).

Expand
titleError Nº1130Nº 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 returns 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 it,

Newtab2
aliasUpgrade and Resolving ERROR 1130 Host ‘localhost’ is Not Allowed to Connect
urlhttp://www.chriscalender.com/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 cannot 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.

...

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

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

Newtab2
alias
urlhttp://www.webyog.com/faq/21_19_en.html
HTTP-tunneling or SSH-tunneling to connect.
  • Also
    • Expand
    titleError Nº 2003

    Error no. 2003:

    Can't

    Cannot connect...

    The error message: 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
    Newtab2
    alias
    urlhttp://www.webyog.com/faq/21_19_en.html
    Newtab2
    alias
    urlhttp://www.webyog.com/faq/22_44_en.html
    Newtab2
    aliasthis FAQ
    urlhttp://webyog.com/faq/23_36_en.html
    may have relevant information. It describes some more special situations when connection to MySQL on Linux.

    ...