PHP & PDO: Connect to MySQL using IPv6 address

7
votes

I want to connect to a remote MySQL instance (a Google Cloud SQL one) by using its IPv6 address.

I'm using PHP PDO like that:

$db = new \PDO('mysql:host=<ipv6-address>;port=3306;dbname=<database-name>',
  '<username>',
  '<password>'
);

But it always fails with the following exception message:

PDOException: SQLSTATE[HY000] [2002] No route to host

From the terminal I can connect to the MySQL instance, without any issue, like this:

mysql --host=<ipv6-address> --user=<username> --<password>

Any help will be really appreciated.

Thanks

3
phpmysqlpdoipv6
Either you don't have IPv6, or the connection is firewalled. - Michael Hampton
If it was the case, I wouldn't be able to connect from the command line. - Johann Fradj
@JohannFradj Did you solve the issue? - Jonas Gröger
@JonasGröger I didn't made any more tests or progress since I've posted this question. From my last understandings, it was a PHP connector issue. Don't forget to share with us if you find something ;-) - Johann Fradj

3 Answers

7
votes

In case anyone else stumbles over the same problem, and to save them 2 hours delving through PHP source, PDO MySQL IPv6 connections work if you put square brackets around the address.

See: https://github.com/php/php-src/blob/master/main/streams/xp_socket.c#L568

e.g.

$pdo = new PDO("mysql:host=[1234:5678::42];port=3306;dbname=foo", ...);
4
votes

Reading this https://www.saotn.org/php-mysql-and-ipv6-still-slow/ gives the following idea:

Knowing that, normally, IPv6 takes precedence over IPv4 (which is configurable), users are left with a slow responding website and database operations, only because connecting to an IPv6 address in PHP is refused, and the connection refused isn’t handled correctly, making the fallback to IPv4 slow. It takes mysql.connect_timeout seconds

Note: the source does seem credible

Also, this is a good read: http://dev.mysql.com/worklog/task/?id=798

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

-1
votes

Try using this for your PDO conenction and see if it works.

$dbh = new PDO('mysql:host=<ipv6-address>;port=<port>;dbname=<dbname>', <dbusername>, <dbpassword>);

In case if fails, you can better use try...catch to get exactly at the error

<?php
try {
    $dbh = new PDO('mysql:host=<ipv6-address>;port=<port>;dbname=<dbname>', <dbusername>, <dbpassword>);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>