bind-address = ::1 # however, netstat shows 0.0.0.0:3306 LISTENING

Question

I'm running MySQL 5.6 on 64-bit Windows 7 with IPv6 and IPv4 enabled.

In my.ini:

port=3306
bind-address = ::1

I was hoping to use IPv6 and restrict to the loop-back adapter. I used ::1 instead of 127.0.0.1 because win7 with IPv6 defaults to ::1 for localhost.

With this configuration netstat reports the following:

C:\>netstat -an |findstr 3306
  TCP    0.0.0.0:3306           0.0.0.0:0              LISTENING
  TCP    [::1]:3306             [::]:0                 LISTENING

It seems like connections may be getting blocked on the IPv4 interface, but seeing netstat report that 0.0.0.0 port 3306 is open makes me uneasy.

The MySQL documentation specifies:

If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address.

TCPView gave me the same info as netstat. I couldn't get TDIMon working on my system.

How can I prevent mysql from listening on 0.0.0.0?

Solution 1

I see two possibilities here:

1. You actually have two copies of MySQL running, one of which bound to IPv4, and the other which bound to IPv6. This is probably not very likely, but it's something you should check for anyway.

2. You've found a bug in the Windows port of MySQL. When I tried this on Linux, bind-address = ::1 caused MySQL to bind only to ::1 and not to any IPv4 addresses. In this case you should report it as a bug to MySQL.

Solution 2

I've finally had time to dig into the mysql 5.6 source. Line 2405 of mysqld.cc seems to hold some answers.

 /*
   For interoperability with older clients, IPv6 socket should
   listen on both IPv6 and IPv4 wildcard addresses.
   Turn off IPV6_V6ONLY option.

   NOTE: this will work starting from Windows Vista only.
   On Windows XP dual stack is not available, so it will not
   listen on the corresponding IPv4-address.
 */
if (a->ai_family == AF_INET6)
{
  arg= 0;

  if (mysql_socket_setsockopt(ip_sock, IPPROTO_IPV6, IPV6_V6ONLY,/* Line: 2405 */
                              (char *) &arg, sizeof (arg)))
  {
    sql_print_warning("Failed to reset IPV6_V6ONLY flag (error: %d). "
                      "The server will listen to IPv6 addresses only.",
                      (int) socket_errno);
  }
}

Setting this socket option causes the extra line 0.0.0.0:3306 to show up in netstat. However, I'm still not able to connect by specifying 127.0.0.1 to telnet.

The comments do make it sound like this option should only be used when wildcard is applied, but it seems the option is applied even when bind-address is specified.

Possibly related discussion here: http://serverdown.ttwait.com/que/486038

google cache: http://webcache.googleusercontent.com/search?q=cache:14aq4-3tRLsJ:serverdown.ttwait.com/que/486038+&cd=4&hl=en&ct=clnk&gl=us

EDIT: In the bug report (which is still in triage), I've proposed MySQL only clear IPV6_V6ONLY when binding to the wildcard address *. This seems to give the most flexibility of configuration in my.ini. If some admins are binding :: and then connecting to 127.0.0.1 they could be surprised. Binding * instead of :: would fix it.

Comments

[ebyrob]

Again, I'd like to bind to ::1 unless I'm planning to disable IPv6 (Microsoft recommends against disabling IPv6). I can connect to 127.0.0.1 by specifying it explicitly, but then any tool or user selecting localhost won't work. (I actually noticed the problem when I had performance issues binding to 127.0.0.1 and connecting to localhost on windows 7, it was about 30 seconds per connection)

[ebyrob]

When I net stop mysql I don't see anything listening on :3306. When I net start mysql and quickly netstat -an I see both listen entries.

[Michael Hampton]

@ebyrob That covers one of the two possibilities I mentioned.

[ebyrob]

MySQL bug submitted: bugs.mysql.com/bug.php?id=72087

[klugerama]

Sorry, I should have been more specific; put both: bind-address = 127.0.0.1 bind-address = ::1

[ebyrob]

It looks like if I put in multiple bind-address entries, only the bottom value gets used. I get listen on 127.0.0.1:3306 or ::1:3306 and 0.0.0.0:3306.

[klugerama]

Hmm, you're right. I had that in one of my installations and never checked the first address. Have you checked your hosts file? See victor-ratajczyk.com/post/2012/02/25/… Also, it may be helpful to post your mysql log file from right after it starts.

[ebyrob]

I could change my hosts file but I didn't want to break some other windows process that might expect localhost to resolve to the new default of ::1. Considering every installation of MySQL 5.6 on win7 I've seen exhibits the same behavior, I don't think posting my particular log file will be useful here.

[RandomSeed]

@ebyrob What happened to your bug report? It says "You do not have access to bug #72087."

[ebyrob]

@RandomSeed You have to have a user account with Oracle and log in to see it. Doesn't appear to have gone through triage yet.

[echo_Me]

check my edit now .

[ebyrob]

Network Time still seems irrelevant. Are you suggesting I write a build script to modify other developer's host files with an obsolete host file configuration?

[Michael Hampton]

Oh, so they intended to do this. That means it's not a bug, it's a design flaw...

[ebyrob]

@MichaelHampton It looks to me like they intended this option to fix this bug: bugs.mysql.com/bug.php?id=67119 But didn't realize what would happen on win7 if they also enabled IPV6_V6ONLY for addresses other than wildcard.

[ebyrob]

I'm accepting because... I think it's a bug whether anyone else agrees or not. I'm not going to penalize your answer because Oracle is slow to triage their MySQL bugs.