#41722 closed defect (bug) (fixed)
wpdb::db_connect() does not connect to IPv6 address of mysql server
Reported by: | fierevere | Owned by: | pento |
---|---|---|---|
Milestone: | 4.9 | Priority: | normal |
Severity: | normal | Version: | 4.8.1 |
Component: | Database | Keywords: | has-patch needs-testing has-unit-tests |
Focuses: | Cc: |
Description
There is currently no way to make WordPress to connect to MySQL server using IPv6 address
<?php define('DB_HOST', '::1');
Error establishing a database connection
in brackets:
<?php define('DB_HOST', '[::1]');
Error establishing a database connection
the only way it works by defining a hostname in /etc/hosts and then connecting to it, but its not available option on shared hosting when you dont have control over files in /etc/
quoting @sergeybiryukov :
wpdb::db_connect() appears to treat everything after : as a port or socket: https://core.trac.wordpress.org/browser/tags/4.8.1/src/wp-includes/wp-db.php?marks=1517-1535#L1504
Attachments (5)
Change History (24)
This ticket was mentioned in Slack in #forums by yui. View the logs.
7 years ago
#3
@
7 years ago
From #41721
Hello,
I am trying to connect to a remote database using IPv6. I have configured the MySQL database bind address to that of the IPv6 address and created a database and issued all the required permissions.
When I go to install WordPress, I enter the IPv6 address and WordPress returns the following message "Error establishing a database connection". I have made sure my MySQL is working by using MySQL Client and it connects to the database without any problems.
I then did a little more digging and found that if I change the database bind address to a IPv4 address and configure the correct permissions, WordPress will install using an IPv4 address.
WordPress will not work with IPv6 address.
Thanks.
#4
@
7 years ago
- Keywords has-patch needs-testing added
I've provided a first patch that changes the way the host is parsed. Now, DB_HOST
values like ::1
or [::1]:3306
are correctly parsed and forwarded.
I couldn't find any prior tests that check the DB_HOST
values, so not sure how this could be properly tested. I could write some unit tests for the wpdb::parse_db_host()
method, but without more specific data on what is acceptable to mysqli
(which I couldn't find yet), I'm not sure that will tell us much.
Note: mysqli_real_connect()
seems to require the IPv6 hosts to always be enclosed in square brackets, at least on my system (PHP 7.0.20 + MariaDB 10.2.6).
#5
@
7 years ago
There seems to be some inconsistency how IPv6 addresses are handled by mysqlnd, that's a part of the PHP distribution since 5.3.0. (http://php.net/manual/en/mysqlinfo.library.choosing.php ) and libmysqlclient, according the the comments here:
https://bugs.php.net/bug.php?id=67563
[2015-04-18 13:48 UTC] bobe at webnaute dot net There is a real problem here because mysqlnd wants the IPv6 address to be enclosed in brackets (illogical since mysqli_connect() or PDO wants a host, not a url). But when using libmysqlclient, these extensions wants the IPv6 address NOT to be enclosed in brackets.
[2015-04-18 14:30 UTC] bobe at webnaute dot net Here is some tests: PDO mysql + mysqlnd: [::1] => ok ::1 => getaddrinfo failed mysqli_connect() + mysqlnd: [::1] => ok ::1 => getaddrinfo failed mysql_connect() + mysqlnd: [::1] => Failed to parse IPv6 address "[:3306" ::1 => No such file or directory PDO mysql + libmysqlclient: [::1] => SQLSTATE[HY000] [2005] Unknown MySQL server host '[::1]' ::1 => ok mysqli_connect() + libmysqlclient: [::1] => (HY000/2005): Unknown MySQL server host '[::1]' ::1 => ok mysql_connect() + libmysqlclient: [::1] => Unknown MySQL server host '[' (2) ::1 => Can't connect to local MySQL server through socket '1'
This would need to be verified for mysqli_real_connect().
Wonder if this should be handled by WordPress (lib-detecting) or if it should be up to the user to provide the correct ipv6 form based on the underlying lib? I think the latter would be confusing to users.
@
7 years ago
Added detection for mysql drriver, as mysqlnd needs square brackets and the other doesn't
#6
@
7 years ago
Thanks, @birgire, I added detection for the mysqlnd
driver and only conditionally add the square brackets as needed.
#8
@
7 years ago
Here's a first draft for db host string examples:
// db hosts and the expected host, port, socket $data = array( // db_host // host //port //socket '' => array( null, null, null ), ':3306' => array( null, '3306', null ), ':/tmp/mysql.sock' => array( null, null, '/tmp/mysql.sock' ), '127.0.0.1' => array( '127.0.0.1', null, null ), '127.0.0.1:3306' => array( '127.0.0.1', '3306', null ), 'example.com' => array( 'example.com', null, null ), 'example.com:3306' => array( 'example.com', '3306', null ), 'localhost' => array( 'localhost', null, null ), 'localhost:/tmp/mysql.sock' => array( 'localhost', null, '/tmp/mysql.sock' ), '0000:0000:0000:0000:0000:0000:0000:0001' => array( '[0000:0000:0000:0000:0000:0000:0000:0001]', null, null ), '::1' => array( '[::1]', null, null ), '[::1]' => array( '[::1]', null, null ), '[::1]:3306' => array( '[::1]', '3306', null ), '2001:0db8:0000:0000:0000:ff00:0042:8329' => array( '[2001:0db8:0000:0000:0000:ff00:0042:8329]', null, null ), '2001:db8:0:0:0:ff00:42:8329' => array( '[2001:db8:0:0:0:ff00:42:8329]', null, null ), '2001:db8::ff00:42:8329' => array( '[2001:db8::ff00:42:8329]', null, null ), );
Some host string examples come from:
Here we assume the mysql native driver.
If we run it through:
foreach( (array) $data as $db_host => $expected ) { $actual = $wpdb->parse_db_host( $db_host ); $this->assertEquals( $expected, $actual ); }
we get failed tests for the db host strings: ':3306'
, ':/tmp/mysql.sock'
and 'localhost:/tmp/mysql.sock'
I wonder if the brackets should be handled outside of the parse_db_host()
function? That way the output would be more predictable, as it wouldn't depend on the library. It would also make the above testing easier.
#9
@
7 years ago
Regarding the IPv4 case, it looks like we could parse ':3306'
, ':/tmp/mysql.sock'
and 'localhost:/tmp/mysql.sock'
with:
$pattern = '#^(?<host>[^:/]*)(?::(?<port>[\d]+))?(?::(?<socket>/.+))?#';
instead of
$pattern = '/^(?:\[)?(?<host>[^:^\/]+)(?::(?<port>[\d]+))?(?:\/(?<socket>.+))?/';
but we might look into that further. The above tests run successfully through this pattern.
@
7 years ago
Moved bracket logic out of parsing, added unit test for parsing and modified regexes to fit all tested host strings.
#10
@
7 years ago
- Keywords has-unit-tests added
Thanks for the feedback, @birgire .
I've added unit tests, and moved the bracket logic outside of the parsing method.
I've slightly tweaked your IPv4 pattern to not assume the socket starts with a /
, as it might also be a relative path or a named pipe.
#11
@
7 years ago
Attachment 41722.3.diff added
works nicely for me on 4.8.1-multisite, with or without brackets for DB_HOST
PHP 7.1.9 mysqlnd-mysqli
#12
@
7 years ago
Thanks for the update @schlessera
Glad to hear it worked for you @fierevere
Just few notes:
- If we want to take this in smaller steps, we could keep the same non-ipv6 code, to be absolutely sure that we're not exluding anything with the new patch.
- Would it make sense and be helpful for the developers reading the code, to match the most used db host strings, like localhost, 127.0.0.1 etc, before parsing with regex?
Here's a list of db host strings from the Codex, maybe helpful in tests?:
Hosting Company DB_HOST Value Guess 1and1 db12345678 A2 Hosting localhost AN Hosting localhost Aruba.it localhost or real IP provided with activation mail. A Small Orange localhost AT&T xxxxxxxx.carrierzone.com full server name found in PHP MyAdmin. BlueHost localhost DreamHost mysql.example.com GoDaddy - Shared and 4GH Hosting In the Databases menu go to MySQL. To the right of the database name click on Actions and Details. The hostname is at the bottom of the window. GoDaddy - cPanel Hosting localhost GoDaddy - Plesk Hosting Use the IP address shown in the Databases Section in Plesk. Do not include :3306 HostGator localhost ICDSoft localhost:/tmp/mysql5.sock Infomaniak (old admin) mysql.yourdomain Infomaniak In the Dashboard of your Web Hosting, go to Databases. The host server is at the top of the window (example: xxxx.myd.sharedbox.com) InMotion Hosting localhost iPage username.ipagemysql.com IPower username.ipowermysql.com Laughing Squid localhost MediaTemple Grid internal-db.s00000.gridserver.com - (Replace "00000" with the actual site number) MediaTemple DV localhost MegaHost localhost NearlyFreeSpeech.Net username.db NetworkSolutions mysqlv5 one.com example.com.mysql pair Networks dbnnnx.pair.com QTH.com localhost Rackspace Cloud localhost for unmanaged servers, variable for Cloud Sites like mysqlXY-AB.wcN.dfQ.stabletransit.com where X,Y,A,B,N,Q are variables SysFix.eu Power Hosting datapower.sysfix.eu Site5 localhost SiteGround localhost Vevida.com mysql.example.com, where example.com is your own domain name Yahoo mysql Hosts with cPanel localhost Hosts with Plesk localhost Hosts with DirectAdmin localhost Tophost.it sql.your-domain-name.it
#15
@
7 years ago
- Resolution fixed deleted
- Status changed from closed to reopened
This broke socket connections.
My DB_HOST
looks like this:
define( 'DB_HOST', ':/Applications/MAMP/tmp/mysql/mysql.sock' );
What happens is that $port
ends up being an empty string, which is not a valid value.
Warning: mysqli_real_connect() expects parameter 6 to be integer, string given in /Users/mark/svn/wp-dev/trunk/src/wp-includes/wp-db.php on line 1498
And thus the connection fails.
Warning: mysqli_get_server_info(): invalid object or resource mysqli in /Users/mark/svn/wp-dev/trunk/src/wp-includes/wp-db.php on line 3329
$port
should remain null, if no actual value is extracted.
This ticket was mentioned in Slack in #core by mark. View the logs.
7 years ago
#17
@
7 years ago
Thanks for the heads up @markjaquith
- Replace
assertEquals()
withassertSame()
- Added the same empty check for
$host
and$socket
, as done in 41722-sockets-fix.diff for the$port
.
In the first test, within parse_db_host_data_provider()
, the empty string DB_HOST
is expected to be parsed into a null
$host
. So this patch assumes that output for empty $host
. It would be good to have others verifying that this is valid.
Update: we then could consider e.g.:
foreach ( array( 'host', 'port', 'socket' ) as $component ) { if ( array_key_exists( $component, $matches ) ) { $$component = ! empty( $matches[$component] ) ? $matches[$component] : null; } }
#41721 was marked as a duplicate.