PHP & PDO: Connect to MySQL using IPv6 address

问题:

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

回答1:

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", ...);

回答2:

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

回答3:

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();
}
?>