MySQL CLI mode localhost ignoring --PORT option

I have a issue like this in the past, either in Java or PHP, but never dug into the details of how MySQL server treats its command-line options for Localhost connection.

Basically, Localhost would point to local machine and implicitly means local IP like 127.0.0.1.

For standard MySQL instance, port number is default to 3306. To connect MySQL on different port number is possible by changing port number directly. However, such behaviour is a little bit different on Localhost connection.

Even MariaDB shares common bugs/features with its variant MySQL.

When connecting to MySQL instance on Localhost, you can type the followings with success:

> mysql -uuser -p -hlocalhost -P3306
> mysql -uuser -p -hlocalhost -P3307
> mysql -uuser -p -hlocalhost -P80
> mysql -uuser -p -hlocalhost -P443

They will absolutely redirect you to the instance Localhost:3306. I think of this a bug, yet someone has got an explanation on the official site for years:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.

MySQL is based on Unix development source so behaviour will follow even when it's complied to Windows release.

To reach one particular port number on Localhost, we must specify the actual IP address. This ensures MySQL connection is done through TCP protocol rather than socket file.

In other words,

> mysql -uuser -p -hlocalhost -P3306 --protocol=TCP
> mysql -uuser -p -hlocalhost -P3307 --protocol=TCP
> mysql -uuser -p -hlocalhost -P80 --protocol=TCP
> mysql -uuser -p -hlocalhost -P443 --protocol=TCP
> mysql -uuser -p -h127.0.0.1 -P3306
> mysql -uuser -p -h127.0.0.1 -P3307
> mysql -uuser -p -h127.0.0.1 -P80
> mysql -uuser -p -h127.0.0.1 -P443

This will make sure we connect to Localhost on the port number as specified.

Ref: https://dev.mysql.com/doc/refman/5.7/en/connecting.html

Setup MySQL ODBC Driver for XAMPP on OS X Mavericks

This article is mainly for the setup of MySQL ODBC connection used in Apache server in OS X Mavericks. Of course, you can try it on OS X Lion or Mountain Lion as well.

Since Mac OS X 10.9 has been released, I have been searching for the way to revive the nearly forgotten feature of ODBC connection. As OS X is based on UNIX system, unixODBC is the first thing in mind for bridging the gap between various database products and the Mac world. Unfortunately, it is still too early to have a piece of working copy available for OS X 10.9.

As I don't remember wrong, it's a painful experience to implement a working ODBC setup on OS X platform. It's crucial to choose the right product among various combination of 32 bit and 64 bit drivers available yet we don't know which one will be working on the newest platform.

Here's the quick receipt which should work on OS X Mavericks:

Assuming we are using a lazy bundle of XAMPP for OS X package which in theory contains Apache (32 bit) and MySQL (32 bit). Since OS X is 64 bit platform which is able to run 32 bit applications, it is still safe to download a copy of 32 bit ODBC driver for use.

A recent version of MySQL connector ODBC should do the job. As of writing, it's version 5.3.2.
http://dev.mysql.com/get/Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.2-osx10.7-x86-32bit.dmg

OS X 10.9 doesn't provide ODBC Administrator in Utilities anymore. For GUI configuration, I go for the solution of ODBC manager:
http://odbcmanager.net/downloads/ODBC_Manager.dmg

Once we finish both of the software installations, we can start digging with Terminal commands.

In Terminal, we can can have a quick access to where ODBC configuration files are hiding under the tree of Filesystem.

The ODBC files should be located as follows:

System wide configurations:
/Libaray/ODBC/odbc.ini
/Libaray/ODBC/odbcinst.ini

These files are supposed to be empty or kept in basic information. They need further modification to get our ODBC connection working.
 Unfortunately, MySQL Connector has default installation of configuration files in user specific folder. It also creates two sample ODBC connections at the beginning of installation.

User specific configurations:
~/Libaray/ODBC/odbc.ini
~/Libaray/ODBC/odbcinst.ini

Now, we need to migrate the settings from user specific location to system wide location.

Two simple steps to get the job done:

Cut the content from ~/Libaray/ODBC/odbc.ini and paste it into /Libaray/ODBC/odbc.ini.

Cut the content from ~/Libaray/ODBC/odbcinst.ini and paste it into /Libaray/ODBC/odbcinst.ini.

Of course, you need to merge the content for the files so they contain unique section for each.

Remember to save the empty content back in ~/Library/ODBC/odbc.ini and ~/Library/ODBC/odbcinst.ini in order to eliminate any duplicate entries to be shown on ODBC Manager.

Open ODBC Manager and have a look if MySQL ODBC 5.3 ANSI Driver exist. We'll make use of this driver to create System DSN for

 Click Add... button in System DSN Tab and add the basic connection parameters:

For Apache's ODBC configuration, please have a look at here for more information:
http://httpd.apache.org/docs/2.4/mod/mod_dbd.html

It takes a little bit longer period of time to get MySQL ODBC drivers ready for mod_dbd since OS X 10.8. Now it's working.