MySQL Bugs: #67119: MySQL 5.6.10 GA Service doesn't bind to localhost when IPV6 protocol is enabled

Bug #67119	MySQL 5.6.10 GA Service doesn't bind to localhost when IPV6 protocol is enabled
Submitted:	7 Oct 2012 8:49	Modified:	15 Apr 2013 11:16
Reporter:	MELERIX ...	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S1 (Critical)
Version:	5.6.10	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	BIND, IPV4+IPV6, localhost, service

Description:
after finish install MySQL 5.6.7 in Windows XP SP3, it fail to start.

if you look .err file, this is the log:
121007  5:22:02 [Note] Plugin 'FEDERATED' is disabled.
121007  5:22:02 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
121007  5:22:02 InnoDB: The InnoDB memory heap is disabled
121007  5:22:02 InnoDB: Mutexes and rw_locks use Windows interlocked functions
121007  5:22:02 InnoDB: Compressed tables use zlib 1.2.3
121007  5:22:02 InnoDB: CPU does not support crc32 instructions
121007  5:22:02 InnoDB: Initializing buffer pool, size = 16.0M
121007  5:22:02 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
121007  5:22:02 InnoDB: Setting file .\ibdata1 size to 12 MB
InnoDB: Database physically writes the file full: wait...
121007  5:22:03 InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 10 MB
InnoDB: Database physically writes the file full: wait...
121007  5:22:03 InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
121007  5:22:04 InnoDB: Doublewrite buffer not found: creating new
121007  5:22:04 InnoDB: Doublewrite buffer created
121007  5:22:05 InnoDB: 128 rollback segment(s) are active.
121007  5:22:05 [Warning] InnoDB: Creating foreign key constraint system tables.

121007  5:22:05 [Note] InnoDB: Foreign key constraint system tables created

121007  5:22:05 [Note] InnoDB: Creating tablespace and datafile system tables.

121007  5:22:05 [Note] InnoDB: Tablespace and datafile system tables created

121007  5:22:05 InnoDB: Waiting for the background threads to start
121007  5:22:05 InnoDB: 1.2.7 started; log sequence number 0
121007  5:22:05 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 14632118-1058-11e2-a81a-000c76b31eb4.
121007  5:22:06 [Note] Server hostname (bind-address): '*'; port: 3306
121007  5:22:06 [Note] IPv6 is available.
121007  5:22:06 [Note]   - '::' resolves to '::';
121007  5:22:06 [Note] Server socket created on IP: '::'.
121007  5:22:06 [Warning] Failed to reset IPV6_V6ONLY flag (error: 10042). The server will listen to IPv6 addresses only.
121007  5:22:06 [Note] Event Scheduler: Loaded 0 events
121007  5:22:06 [Note] C:\Archivos de programa\MySQL\MySQL Server 5.6\bin\mysqld: ready for connections.
Version: '5.6.7-rc'  socket: ''  port: 3306  MySQL Community Server (GPL)

How to repeat:
1 - Download MySQL 5.6.7.
2 - Install MySQL 5.6.7.
3 - It fail to start service after finish install.

aditional note, inside .err log file you can see this lines:

121007  5:22:06 [Note]   - '::' resolves to '::';
121007  5:22:06 [Note] Server socket created on IP: '::'.

sholdn't it be '::1' instead of '::?

seems it fails to bind MySQL service to localhost.

Thank you for the bug report. Please check if actually the server failed to start (there the line in the log the server is ready to connections). Thanks.

well, I see mysqld.exe process running, but can't connect to mysql server.

the full server log is in the report description.

Thank you for the feedback. Then in this case the Synopsis: "MySQL 5.6.7 Service Fail to start" is wrong your problem is you can't connect. Please describe how you are trying to connect. Thanks.

@Miguel Solorzano

from command line, or with gui tools like: MySQL Workbench, Navicat, HeidiSQL.

What happens if you try without password:

mysql -uroot

without password it fail too.

btw, I have IPV4 & IPV6 protocols enabled in XP.

Do you have in your my.ini bind-address=* ?. Otherwise please try. Thanks.

I did some more test, and I noticed that if IPV6 protocol is uninistalled from XP and leave only IPV4, MySQL binds to localhost properly and connection works normally.

this issue only happens when you have both protocols enabled IPV4+IPV6.

so unistalling IPV6 protocol solve the issue, but is not a valid fix because I need both protocols enabled IPV4+IPV6.

now I'm going to test what happens using (bind-address=* in my.ini) and both protocols (IPV4+IPV6) enabled.

.err file with IPV4 protocol enabled:
-------------------------------------
121009  2:07:38 [Note] Plugin 'FEDERATED' is disabled.
121009  2:07:38 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
121009  2:07:38 InnoDB: The InnoDB memory heap is disabled
121009  2:07:38 InnoDB: Mutexes and rw_locks use Windows interlocked functions
121009  2:07:38 InnoDB: Compressed tables use zlib 1.2.3
121009  2:07:38 InnoDB: CPU does not support crc32 instructions
121009  2:07:38 InnoDB: Initializing buffer pool, size = 16.0M
121009  2:07:38 InnoDB: Completed initialization of buffer pool
121009  2:07:38 InnoDB: highest supported file format is Barracuda.
121009  2:07:38 InnoDB: 128 rollback segment(s) are active.
121009  2:07:38 InnoDB: Waiting for the background threads to start
121009  2:07:38 InnoDB: 1.2.7 started; log sequence number 1600069
121009  2:07:38 [Note] Server hostname (bind-address): '*'; port: 3306
121009  2:07:40 [Note] IPv6 is not available.
121009  2:07:40 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
121009  2:07:40 [Note] Server socket created on IP: '0.0.0.0'.
121009  2:07:40 [Note] Event Scheduler: Loaded 0 events
121009  2:07:40 [Note] C:\Archivos de programa\MySQL\MySQL Server 5.6\bin\mysqld: ready for connections.
Version: '5.6.7-rc'  socket: ''  port: 3306  MySQL Community Server (GPL)

.err file with IPV4+IPV6 protocols enabled:
-------------------------------------------
121009  2:09:21 [Note] Plugin 'FEDERATED' is disabled.
121009  2:09:21 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
121009  2:09:21 InnoDB: The InnoDB memory heap is disabled
121009  2:09:21 InnoDB: Mutexes and rw_locks use Windows interlocked functions
121009  2:09:21 InnoDB: Compressed tables use zlib 1.2.3
121009  2:09:21 InnoDB: CPU does not support crc32 instructions
121009  2:09:21 InnoDB: Initializing buffer pool, size = 16.0M
121009  2:09:21 InnoDB: Completed initialization of buffer pool
121009  2:09:21 InnoDB: highest supported file format is Barracuda.
121009  2:09:21 InnoDB: 128 rollback segment(s) are active.
121009  2:09:21 InnoDB: Waiting for the background threads to start
121009  2:09:21 InnoDB: 1.2.7 started; log sequence number 1600079
121009  2:09:21 [Note] Server hostname (bind-address): '*'; port: 3306
121009  2:09:21 [Note] IPv6 is available.
121009  2:09:21 [Note]   - '::' resolves to '::';
121009  2:09:21 [Note] Server socket created on IP: '::'.
121009  2:09:21 [Warning] Failed to reset IPV6_V6ONLY flag (error: 10042). The server will listen to IPv6 addresses only.
121009  2:09:21 [Note] Event Scheduler: Loaded 0 events
121009  2:09:21 [Note] C:\Archivos de programa\MySQL\MySQL Server 5.6\bin\mysqld: ready for connections.
Version: '5.6.7-rc'  socket: ''  port: 3306  MySQL Community Server (GPL)

my.ini file doesn't contain bind-address=* line by deault.

I tested adding this line manually to check if something happens, but doesn't change anything, I still can't connect to MySQL Server if IPV4+IPV6 protocols are enabled in XP SP3.

IPV6

Attachment: ipv6.png (image/png, text), 104.61 KiB.

I enabled IPV6/IPV4 on XP Home edition and I am able to connect to the server see attached picture. I assume something wrong happens on your environment. Thanks.

you're using MySQL Server manually (and I've not tryed that).

I'm using MySQL service installed with "MySQL Installer" (mysql-installer-community-5.6.7.1-rc.msi) downloaded from here: http://dev.mysql.com/downloads/installer/5.6.html

Thank you for the feedback. I installed the msi package and what I found was an installer bug and not problems with IPV6 connection. The service only started when  I reboot the machine and changed to system local account and found that the shortcut for the command line was wrong with the my.ini path. Notice below the path for the server and for mysql.exe of my.ini:

"C:\Arquivos de programas\MySQL\MySQL Server 5.6\bin\mysqld" --defaults-file="C:\Documents and Settings\All Users\Dados de aplicativos\MySQL\MySQL Server 5.6\my.ini" MySQL56

"C:\Arquivos de programas\MySQL\MySQL Server 5.6\bin\mysql.exe" "--defaults-file=C:\Arquivos de programas\MySQL\MySQL Server 5.6\my.ini" "-uroot" "-p"

Please check on your side so I can verify and change the synopsis. Thanks.

I have "my.ini" file in: C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\my.ini

and inside "my.ini" file, it have the following paths:

# Path to installation directory. All paths are usually resolved relative to this.
basedir="C:\Archivos de programa\MySQL\MySQL Server 5.6\"

# Path to the database root
datadir="C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\data\"

What I meant you fix the wrong path of the shortcut of the command line client fix the target and start in paths. Thanks.

some more tests...

Windows 7, MySQL binds properly to IPV4+IPV6: http://i45.tinypic.com/iftrmu.gif

and you can connect properly to "localhost": http://i50.tinypic.com/339t7qr.gif

Windows XP SP3, MySQL binds only to IPV6: http://i45.tinypic.com/2qjgl21.gif

but you can't connect to: "localhost": http://i47.tinypic.com/2mra9s1.gif

My opinion and recommendation (to my old colleagues) would be *not* to use IPv6 on XP. This protocol is too exotic for the 11 years old OS, and people who enable it on IPv6 day do not really know what they play with.

You can check Windows version, or you can fallback after setsockopt error.

Just my 2 cents.

@Vladislav Vaintroub

remember that there is still a lot of enterprises that continue using XP or Server 2003, and they use some applications that work with MySQL and these enterprises doesn't have plans to jump to W7 or Server 2K8 due it implies a lot of cost.

and disable IPV6 is not a valid option, because now some ISPs are migrating to IPV6.

so, there should be a workaround by default, like in MySQL 5.5.x where the mysql service binds by default in 0.0.0.0.

0.0.0.0 on XP is what I was talking about, which equals standing away from IPv6 on this OS as much as possible.

I'm not sure why you would disagree that with OS that old using IPv6 will hurt back and knees badly so it is better to avoid this dangerous exersize(you're asking about it in this very bug report :))

yes, but trouble is:

with MySQL 5.6.x if you are using XP and IPV4+IPV6 protocols enabled, MySQL server doesn't bind, and you have to do a "manual and temp workaround" (adding bind-address=0.0.0.0 inside my.ini) 

with MySQL 5.5.x if you are using XP and IPV4+IPV6 protocols enabled, MySQL server binds by default and automatically to 0.0.0.0, so you don't need to modify my.ini.

Imho MySQL service should detect system OS, and if OS is < W7/2K8/W8 it should bind by default to 0.0.0.0, otherwise you can't connect to MySQL server, until you modify my.ini.

bug still present in 5.6.9 RC

Unfortunately, Windows XP is not a supported platform for Server 5.6 (http://www.mysql.com/support/supportedplatforms/database.html). While we understand that XP is still in active use in a number of places, we do not plan to invest further in supporting 5.6 on it.

@Yngve Svendsen

MySQL 5.6.10 works properly in XP, so there is no reason to avoid fix this small issue.

the manual workaround to fix it is add: "bind-address=0.0.0.0" at the end of the config file.

but, MySQL Developers could just add an option in the MySQL Installer (a box for example) with something like: "If you are using Windows XP please select this option..." then if the user select that option, it will add "bind-address=0.0.0.0" automatically at the end of the config file, and everything will be working fine.

is too hard to do this ?

ofc no.

require several hours/weeks to fix it ?

no, just 30 min or less to improve the MySQL Installer gui and commit the new build.

so please consider fix it, remember that MySQL Installer was designed to be user-friendly, even for people that is using MySQL for first time, and we all know that these new users are not familiar with manual workarounds.

thanks.

Hi, while the fix may in itself seem simple, this comes down to weighing where to use our development, test and packaging resources. And with XP nearing its end of life, the decision is that we will use those resources for other platforms instead. This unfortunately means that XP specific workarounds or fixes will not be a priority.

now I'm having this error in MySQL, please everyone help!

130 411 15:25:08 InnoDB: Waiting for the background threads to start
130 411 15:25:09 InnoDB: 1.1.8 started, log sequence number 194 235 763
130 411 15:25:09 [Note] Server hostname (bind-address): '0 .0.0.0 ', port: 3306
130 411 15:25:09 [Note] - '0 .0.0.0 'resolves to '0 .0.0.0';
130 411 15:25:09 [Note] Server socket created on IP: '0 .0.0.0 '.
130 411 15:25:09 [Note] Event Scheduler: Loaded 0 events
130 411 15:25:09 [Note] d: \ host \ xampp \ mysql \ bin \ mysqld.exe: ready for connections.
Version: '5 .5.27 'socket:'' port: 3306 MySQL Community Server (GPL)
130 412 18:49:29 [Note] Plugin 'FEDERATED' is disabled.
130 412 18:49:29 InnoDB: The InnoDB memory heap is disabled
130 412 18:49:29 InnoDB: Mutexes and rw_locks use Windows interlocked functions
130 412 18:49:29 InnoDB: Compressed tables use zlib 1.2.3
130 412 18:49:29 InnoDB: Initializing buffer pool, size = 16.0M
130 412 18:49:29 InnoDB: Completed initialization of buffer pool
130 412 18:49:29 InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence number does not match files in ibdata
InnoDB: the log sequence number in the ib_logfiles!
130 412 18:49:29 InnoDB: Database was not shut down thường!

thank you

130411 15:25:08  InnoDB: Waiting for the background threads to start
130411 15:25:09 InnoDB: 1.1.8 started; log sequence number 194235763
130411 15:25:09 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130411 15:25:09 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
130411 15:25:09 [Note] Server socket created on IP: '0.0.0.0'.
130411 15:25:09 [Note] Event Scheduler: Loaded 0 events
130411 15:25:09 [Note] d:\host\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.5.27'  socket: ''  port: 3306  MySQL Community Server (GPL)
130412 18:49:29 [Note] Plugin 'FEDERATED' is disabled.
130412 18:49:29 InnoDB: The InnoDB memory heap is disabled
130412 18:49:29 InnoDB: Mutexes and rw_locks use Windows interlocked functions
130412 18:49:29 InnoDB: Compressed tables use zlib 1.2.3
130412 18:49:29 InnoDB: Initializing buffer pool, size = 16.0M
130412 18:49:29 InnoDB: Completed initialization of buffer pool
130412 18:49:29 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130412 18:49:29  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite

5.5 doesn't have this issue, because was fixed, trouble is with 5.6.x only.

Please file a separate bug for the 5.5 issue.