WL#798: MySQL IPv6 support

Affects: Server-5.5   —   Status: Complete

Updates:
  Applied Google Summer of Code Patch. Got feedback on what was wrong with it. 
  The patch has now been rewritten, and I am waiting for 
  * External Feedback
  * Possible Patches from YASSL (don't go bugging Todd based on this message)
  

Previous patch, aka Google, used the second version gethostbyname and similar 
calls. All code has been cleaned up in the server to use the modern IPV6 work:
http://people.redhat.com/drepper/userapi-ipv6.html

Code as is in Bitkeeper right now uses modern calls, but restricts to ipv4. Once 
all cleanups in YASSL have been confirmed I will change the family calls from 
ipv6 to "take anything". It uses calls recommended for all platforms (including 
Windows). 

This patch did not clean up any of the other code in the connection clients. 


Support should be added for MySQL to work over IPv6  
("Internet Protocol Version 6").  
  
This means:  
- users can connect with IPv6. this is partly a  
  connector problem.  
- storage of user address information, e.g.  
  in mysql.user, can be in IPv6 format  
- the proposed new data types CIDR and INET  
  allow IPv6 format as described in  
  WL#2037 "Add CIDR and INET data types"  
- functions like inet_ntoa() need revision  
  
Why this is a 5.2 task  
----------------------  

At one time there was a contract requirement.
We've had some feature requests.

Submissions
-----------

One of the feature requests (BUG#8836) contained a patch  
http://www.ngn.euro6ix.org/IPv6/mysql/ 

The "Google summer of code" included an IPv6 project
http://lists.mysql.com/internals/34830
This appears to add data types that differ from WL#2037's description.
  
Support in other DBMSs  
----------------------  
  
PostgreSQL allows storage of IPv6 in its cidr and inet data types:  
http://www.postgresql.org/docs/current/static/datatype-net-types.html  
but in the authentication section we read that "... entries in IPv6  
format will be rejected if the system's C library does not have  
support for IPv6 addresses".   
  
There is a promise that "Oracle Database and  
Application Server will support IPv6 in an upcoming release."  
http://www.6journal.org/archive/00000058/01/Marshall_Presser.pdf  
But there is no mention of IPv6 in the master  
index for Oracle10g Release 1  
http://www.stanford.edu/dept/itss/docs/oracle/10g/mix.101/b12039/toc.htm  
 
SQL Server 2005 can connect with ipv6, according to 
http://blogs.msdn.com/sql_protocols/archive/2005/10/12/480192.aspx 

IBM has anounced ipv6 support in DB2 9:
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-
0606ahuja2/index.html

 
References  
----------  
  
"IPv6 information page"  
http://www.ipv6.org/  
  
Monty's comment in dev-sales thread "Re: IPv6 question":  
[mysql intranet address]/secure/mailarchive/mail.php?folder=54&mail=1656  
  
Sinisa's comment in dev-public thread "Support for IPv6 ..."  
[mysql intranet address]/secure/mailarchive/mail.php?folder=5&mail=21961  

Mark's comment in dev-sales thread "Which MySQL version is IPv6 compliant?"
[mysql intranet address]/secure/mailarchive/mail.php?folder=54&mail=9399

"MySQL IPv6 support" forum thread
http://forums.mysql.com/read.php?11,141295,141295

"The IPv6 Revolution is Nigh" discuss@mysql.com thread
[mysql intranet address]/secure/mailarchive/mail.php?folder=6&mail=8004

dev-private thread "IPv6"
[mysql intranet address]/secure/mailarchive/mail.php?folder=4&mail=24882

BUG#3318: Add support for IPv6 addresses (Feature Request)  
BUG#6348: MySQL fails to handle IPv6 hosts (Closed)  
BUG#8836: IPv6 support (Feature Request)  
BUG#10093 MySQL is not IPv6 ready (Closed)  
BUG#15127 Possibility to store IPv6 addresses
BUG#34037 ipv6-capable inet_aton and inet_ntoa functions needed
attached is an approximate patch of ipv6 changes pushed, extracted from a series
of changesets in 6.0 tree sans merges

Testplan

Test IPv6 with mtr in the main test suite basically means to test the IPv6
format of IPs. That can be done by using "localhost" or by local IP given for
machines in a local network. Second approch causes some net administration and
is therefore not the favorite. Actually in networks IPv4 and IPv6 exist in
parallel. So, the following test cases shall be part of the main test suite (see
also the grant tests):
Basically, start server with "skip-name-resolve", "bind-address" to listen to a
special IP and connect with mysql client
to the server using "host=IP" with the notations:
::/128 (undefined IP-address)
::1/128 (localhost)
::1
0000:0000:0000:0000:0000:0000:0000:0001
0:0:0:0:0:0:0:1
0:0:0:0:0:FFFF:127.0.0.1
0:0:0:0:0:FFFF:127.0.0.1/96
::FFFF:127.0.0.1

To check if a Linux system supports IPv6 e. g. the following test can be used:

test -f /proc/net/if_inet6 && echo "IPv6 supported"

Other commands around IPv6:

- ping6 (delivers on Windows an error if ipv6 isn't available), otherwise like ping
- ipconfig (Windows)
- net start tpcip6 (switch between IPv6/IPv4), net stop tcpip6 (Windows)
- ipv6.exe (Windows), is only executable if installed.
- ip -f inet6 addr (Linux)

Perl functions:
- Gethostbyaddr (gethostbyname), needs to be tested in IPv6 network. Allows to
check availabitily of IPv6 OS independent (hopefully).

Additionally, the tests shall contain SQL statements showing that the connection
was (un)succesul and that the IP is inserted right in the information schema.
Also, defining a user and giving rights shall be tested:

- show processlist
- show grants
- grant ...
- revoke ...
- create user ...
- rename user ...
- set password for user ...
- show variables like hostname
- replication statements

All these statements shall be able to show or handle IPv6.

SQL functions using hostname/IP:

- current_user()
- user()
- inet_aton()
- inet ntoa()

Test of host cache:

- Option skip_host_cache and its effect on connections.
- Fill the host cache and use then "FLUSH HOST" to allows more connections.

This makes no sense together with IP tests as the host cache is not used.

Interfaces like plugin and service API needs to be tested with IPv6.

I don't really expect that a storage engine can see the IP.