Linux 2.6, with "localhost" being defined as both 127.0.0.1 and ::1

Wlad,

Since you already looked into the issue, could you please handle it?

This patch fixes things for me with mariaDB 10.0.1.

130402 19:53:11 [Note] Server hostname (bind-address): 'localhost'; port: 7306
130402 19:53:11 [Note] - 'localhost' resolves to '::1';
130402 19:53:11 [Note] - 'localhost' resolves to '127.0.0.1';
130402 19:53:11 [Note] Server socket created on IP: '::1'.
130402 19:53:11 [Note] Event Scheduler: Loaded 0 events
130402 19:53:11 [Note] /opt/zimbra/mysql/bin/mysqld: ready for connections.
Version: '10.0.1-MariaDB-log' socket: '/opt/zimbra/db/mysql.sock' port: 7306 Source distribution
root@zre-ldap003:/opt/zimbra/log# netstat -a -n | grep 7306
tcp6 0 0 ::1:7306 :::* LISTEN

It would be even better if MariaDB could join most modern applications and listen to multiple addresses of course. But as long as it is limited to a single address, at this allows things to function with ipv4/ipv6. This patch correctly prioritizes ipv6 over ipv4.

Sigh, the patch doesn't fully work, but it's a start.

How much hassle would it be for you to explicitly state --bind-address=::1 (I assume you did not like --bind-address=127.0.0.1 because it was IPv4)
If you can resolve the ambiguity manually rather trivially, in localhost case, is this still that big problem? If yes, why?

It is a significant problem when dealing with multiple applications that talk to mysql/mariadb, particularly on dual-stack hosts. I'm still baffled that while every other piece of software I use can actually listen to both IPv4/IPv6 at the same time for a given hostname, this software can't even do that. The least it could do is be able to pick one of those addresses as has been done with mysql. I've been reading all the hype about how mariadb is a drop in replacement for mysql, and how mariadb is synced with mysql. Clearly those statements are not true, and quite misleading to end admins who are looking to drop their reliance on mysql.

I think you need to clarify something here:

10.0.x is a) nowhere near GA at this point and b) is not a drop-in replacement for MySQL 5.6. It will eventually be GA and be a drop-in replacement of 5.6 , but at the current stage it won't . I do not believe anyone has made different statements about 10.0.x

MariaDB 5.5 , which is GA, is a drop-in replacement of MySQL 5.5, right now. If you are looking into production quality release, it is 5.5 now.

So, I hope this is clarified.

That aside, I would still like to have an answer to my question.

Clearly MariaDB 5.5.30 is not a drop in for mysql 5.5.30 either, given that this bug was fixed in mysql 5.5.24. So all of the statements that it is remain misleading.

I'm looking at deploying MariaDB in the next version of Zimbra, which is not due out for another year or so. We've already moved to mysql 5.6 in that branch. At this point, I have no choice but to keep mysql 5.6 since MariaDB clearly is not ready for production deployments with either its 5.5 or 5.6 releases. I would also suggest to stop promoting it as a drop in replacement when clearly it is not.

As for your question, we use a variety of interfaces for talking to mysql (java, perl, C, etc). We also support in Zimbra the ability to run in IPv4 only mode, IPv4/IPv6 dual stack, and IPv6 only. We also allow people to move between those modes (ipv4 -> both -> ipv6 or vice versa). MySQL's inability to listen to both ipv4/ipv6 in dual stack mode becomes clearly problematic. Right now, with mysql at least, since this bug is fixed there for both the 5.5 and 5.6 releases, we can just use "localhost", and things work overall, without ever having to fiddle with the bind address. We clearly cannot do this with MariaDB since it is lacking the fix that went into the MySQL 5.5 release. Overall, I can, with a significant amount of work, hack things to do 127.0.0.1, or ::1, and keep switching around the bind addresses as people change modes. However, this should not be necessary. There is no technical reason for mysql to be unable to listen to multiple addresses if that is what the admin wants.

This issue first bit us when moving to mysql5.5, as most modern OSes define both an IPv4 and an IPv6 localhost, as previously it had only been IPv4 aware, which then caused immediate breakage with mysql on upgrades to the 5.5 series.

I extract that the only thing you care for is that mysqld does startup if localhost resolves to 2 different addresses. What it actually binds to does not matter much for you (despite that if you say IPv6 is better somewhere in the bug description, you're comfortable running MySQL that binds to IPv4. Maybe you even more inclined to use IPv4, if possible, because you're not sure that all MySQL connectors you use can work with IPv6)

Is that, in essence, correct?

No, that is not correct. What I would like is for it to bind to all addresses associated with a hostname, whether IPv4 or IPv6. Failing that, I would prefer it to bind to IPv6 over IPv4 if both are present (dual-stack). If it can only bind to a single address, then it should only do IPv4 as a last resort. I've managed to deal with it doing ipv4 by default in mysql, but it is not my preferred scenario. This issue came up in particular with some of the Perl modules, because they prefer IPv6 for localhost over IPv4, as is standard. But all the connectors we use support both ipv4 and ipv6.

By the way, I'm happy to test any patch for this.

If you can test this one, http://lists.askmonty.org/pipermail/commits/2013-April/004559.html it would be great. It is against current 5.5, I did not yet verify if it applies cleanly to 10.0 (but if it does not and you need one for 10.0, tell, I'll create one for 10.0 too)

Testing it now with 10.0, thank you. Patch didn't apply cleanly, but that may be spaces vs tabs in cut & paste. Is it possible to download the patch directly via a git web interface or subversion web interface to skip those types of issues?

Well, it would not apply cleanly to 10.0 anyway, some code around patch has changed.

I created 10.0 patch out of it and pushed the tree. We use neither Git nor Subversion, but bzr.
Patch can be downloaded directory from
http://bazaar.launchpad.net/~maria-captains/maria/10.0-wlad/revision/3508

(there is a download diff link)

Perfect, thanks. That applied no issue. I'll build it out and follow up. I appreciate the quick turnaround on this.

Just one other quick question – Is there a technical reason that listening to "localhost" on both ipv4/ipv6 is something not desired?

All the other major software I use allows this. It also generally has specific flags if you wish to limit this behavior. For example OpenLDAP's slapd defaults to listening to ipv4 + ipv6 if those are configured, with -4 or -6 flags to limit it to ipv4 or ipv6 respectively.

Postfix (MTA) is similar – It will listen to either 4, both, or 6, as directed.
Same with java programs such as Jetty, apache, and on and on.

Thanks,
Quanah

Suggested patch for single binding works:

root@zre-ldap003:/opt/zimbra/log# cat mysql_error.log
130403 18:04:27 mysqld_safe Starting mysqld daemon with databases from /opt/zimbra/db/data
130403 18:04:27 InnoDB: The InnoDB memory heap is disabled
130403 18:04:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130403 18:04:27 InnoDB: Compressed tables use zlib 1.2.3.3
130403 18:04:27 InnoDB: CPU supports crc32 instructions
130403 18:04:27 InnoDB: Initializing buffer pool, size = 905.0M
130403 18:04:27 InnoDB: Completed initialization of buffer pool
130403 18:04:27 InnoDB: highest supported file format is Barracuda.
130403 18:04:27 InnoDB: 128 rollback segment(s) are active.
130403 18:04:27 InnoDB: 1.2.1 started; log sequence number 1605418
130403 18:04:27 [Note] Server socket created on IP: '::1'.
130403 18:04:27 [Note] Event Scheduler: Loaded 0 events
130403 18:04:27 [Note] /opt/zimbra/mysql/bin/mysqld: ready for connections.
Version: '10.0.1-MariaDB-log' socket: '/opt/zimbra/db/mysql.sock' port: 7306 Source distribution

root@zre-ldap003:/opt/zimbra/log# cat /etc/hosts
127.0.0.1 localhost
10.137.242.53 zre-ldap003.eng.vmware.com zre-ldap003

1. The following lines are desirable for IPv6 capable hosts
   ::1 localhost ip6-localhost ip6-loopback

to answer : "Just one other quick question – Is there a technical reason that listening to "localhost" on both ipv4/ipv6 is something not desired?"

So far following works due to "dual stack" mechanism : you need to create IPv6 socket, listening on "any" interface - "::" , ensure that IPV6_V6ONLY flag is not set, and that can serve both IPv4 and IPv6 traffic. You can achieve this with --bind-address=::

MySQL 5.6 has this behavior by default, with fallback mechanism to IPv4-any "0.0.0.0" if IPv6 socket can't be created. This is good default we should copy.

However, back to your question. localhost is not "any" interface. it is either 127.0.0.1 or ::1 . Dual stack does not work here. If you, by analogy with above, create socket listening on IPv6 loopback address, and ensure that IPV6_V6ONLY is not set, it still would not receive IPv4 traffic on the same port. You'd need 2 separate sockets, and this is something that we do not support as of today, but probably could in 10.0. This is more work that the micro-patch you have tested.

Ok. Would you like me to file a separate issue tracker for it? It would at least for me be really helpful to have, and I imagine may benefit others.

If you can benefit , sure , please create separate one. I have now pushed that change into 5.5, it will appear in 10.0 after upmerge.

> So far following works due to "dual stack" mechanism : you need to create IPv6 socket, > listening on "any" interface - "::" , ensure that IPV6_V6ONLY flag is not set, and that can > serve both IPv4 and IPv6 traffic. You can achieve this with --bind-address=::

This is not portable and a poor way of doing things.

> MySQL 5.6 has this behavior by default, with fallback mechanism to IPv4-any "0.0.0.0" > if IPv6 socket can't be created. This is good default we should copy.

No, it is poor default.

> However, back to your question. localhost is not "any" interface. it is either 127.0.0.1 > or ::1 . Dual stack does not work here. If you, by analogy with above, create socket
> listening on IPv6 loopback address, and ensure that IPV6_V6ONLY is not set, it still
> would not receive IPv4 traffic on the same port. You'd need 2 separate sockets, and
> this is something that we do not support as of today, but probably could in 10.0. This
> is more work that the micro-patch you have tested.

Utilizing dual sockets is the only portable way of doing things that does not break IPv4 support.

What I described was "utilizing dual sockets". So, utilizing dual sockets is "poor way of doing things and poor default". Can you describe what you want, then

The first two paragraphs were talking about dual-stack; which is utilizing one socket. The last paragraph was talking about modifying the currently broken MySQL 5.5/5.6 / MariaDB code to also work on a split-stack system which will utilize dual sockets.

Brad, see https://mariadb.atlassian.net/browse/MDEV-4379

So, the dual-stack is bad and multiple sockets, one per IPvX are good? I never heard "dual socket" , but multiple listening sockets is what you mean, right?

Correct and yes I basically mean multiple listening sockets. I say dual sockets because for a lot of apps they just want to use both v4 and v6 so that would result in only two sockets (.e.g. * and :, but other apps sometimes want to listen to a bunch of specific IP addresses whether v4 or v6 and being a mix of the two and that could result in many sockets.

I thought MariaDB, like MySQL, was bind "all or one"? Why do internal adapters get preferential treatment? (binding wildcard * allows access to both IPv4 and IPv6)

Is this actually in the code-base? I downloaded 10.0.9 source and don't see any evidence of binding to multiple IP addresses in /sql/mysqld.cc, specifically near line 2440 which seems to bind to only one addrinfo::ai_addr.

Note: If just clearing IPV6_V6ONLY is supporting this on UNIX, it certainly doesn't work on Windows 7 et al.

@Roberty Eby: You're looking for https://mariadb.atlassian.net/browse/MDEV-4379