I just upgraded to PHP 5.3 on my Windows development box, and ran into an issue making any database connection with the MySQL and MySQLi drivers. Pages not making any database connections worked fine, but any page with a database connection would sit and hang for about a minute, and then throw an error.
PHP Warning: mysqli::mysqli(): [2002] A connection attempt failed because the connected party did not (trying to connect via tcp://localhost:3306) in xxx.php on line 2
PHP Warning: mysqli::mysqli(): (HY000/2002): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
in xxx.php on line 2
PHP bug #45150 suggests that this is due to issues resolving localhost on Windows. This is partially correct, more specifically it's an issue with localhost resolving to ::1 when IPv6 is enabled.
While it feels like Windows has shoehorned in IPv6 support, the blame seems to lie with MySQL, which won't support IPv6 until version 6.0.
I'm not sure why the timeout isn't instant, the port is closed, so it could be partially a problem with PHP or Windows.
> nmap -sT -p 80,3306,3389 -6 ::1
Starting Nmap 5.00 ( http://nmap.org ) at 2009-11-20 15:48 Mountain Standard Time
Interesting ports on cfg64 (::1):
PORT STATE SERVICE
80/tcp closed http
3306/tcp closed mysql
3389/tcp open ms-term-serv
Nmap done: 1 IP address (1 host up) scanned in 3.14 seconds |
The Fix
Open up %windir%\system32\drivers\etc\hosts with a text editor and comment out the line that looks like:
Prefix it with a #, like so:
Save it and your PHP/MySQL connections will immediately begin working. You could also use 127.0.0.1 in your connection string instead of localhost, but I didn't want to change code in innumerable files.
I wanted to cleanly display tabular data from a PHP command line script, and I like how MySQL formats its output. This is quick, somewhat inefficient, hacky code, but it got the job done. You'll find nothing to be proud of in this code; I don't want to spend more time writing this post about it than I did writing it :)
/**
* Out a MySQL-style table of data
*
* @param array $data Associative array of data to output.
* @param array $header_keys Optional; Assoc array of display names to use for headers. Keys must match those of $data. Defaults to keys of $data.
* @param string $glue String to join lines with, defaults to newline.
* @return string
*
*/
function build_table($data, $header_keys=array(), $glue="\n") {
$table = '';
$data_fmt = array();
$divider_row = array();
$header_fmt = array();
$keys = array_keys($data[0]);
$col_lengths = array_flip($keys); // used to determine the max column width
if( empty($header_keys) ) {
$header_keys = array_combine($keys, $keys);
}
// set the base max length to the length of our header keys
foreach( $keys as $key ) {
$col_lengths[$key] = strlen($header_keys[$key]);
}
foreach( $data as $row ) {
foreach( $keys as $key ) {
$col_lengths[$key] = max($col_lengths[$key], strlen($row[$key]));
}
}
foreach( $keys as $key ) {
$data_fmt[] = '%-' . $col_lengths[$key] . 's';
$header_fmt[] = '%-' . $col_lengths[$key] . 's';
$divider_row[] = str_pad('', $col_lengths[$key]+2, '-'); // fill the spacing
}
$data_fmt = '| ' . implode(' | ', $data_fmt) . ' |';
$divider_row = '+' . implode('+', $divider_row) . '+';
$header_fmt = '| ' . implode(' | ', $header_fmt) . ' |';
// assemble the table
$table .= $divider_row . $glue;
$table .= vsprintf($header_fmt . $glue, $header_keys);
$table .= $divider_row . $glue;
foreach( $data as $row ) {
$table .= vsprintf($data_fmt . $glue, $row);
}
$table .= $divider_row . $glue;
return $table;
} |
The output will be similar to:
+--------------------+--------------------+---------------+
| First | Second col | Random Column |
+--------------------+--------------------+---------------+
| 60386980488376 | 597655305189574649 | 51 |
| 959432 | 459191 | 396802 |
| 73874213 | 570702 | 771 |
| 144579584678722975 | 892300317939345 | 59180 |
| 293172 | 314 | 127725766727 |
+--------------------+--------------------+---------------+ |
It only works with associative arrays, but that's easy enough to fix. You can proudly test it out using this glorious snippet:
$data = array();
function get_rand() {
$max=rand(1,6);
$str='';
for( $i = 0; $i < $max; $i++ ) {
$str .= rand(10, 1000);
}
return $str;
}
for( $i = 0; $i < 5; $i++ ) {
$row = array(
'one' => get_rand(),
'two' => get_rand(),
'three' => get_rand(),
);
$data[] = $row;
}
$disp = array(
'one' => 'First',
'two' => 'Second col',
'three' => 'Random Column',
);
echo build_table($data, $disp); |
Recently I ran into an issue where WordPress would take a long time to update rewrite rules after saving a page or post and my PHP error log would contain entries similar to:
WordPress database error MySQL server has gone away for query INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('rewrite_rules', '<serialized array>, 'yes')
In addition to various defaults the serialized array contains 9 regular expressions for every page and post.
(PAGENAME)/trackback/?$
(PAGENAME)/feed/(feed|rdf|rss|rss2|atom)/?$
(PAGENAME)/(feed|rdf|rss|rss2|atom)/?$
(PAGENAME)/page/?([0-9]{1,})/?$
(PAGENAME)(/[0-9]+)?/?$
PAGENAME/attachment/([^/]+)/?$
PAGENAME/attachment/([^/]+)/trackback/?$
PAGENAME/attachment/([^/]+)/feed/(feed|rdf|rss|rss2|atom)/?$
PAGENAME/attachment/([^/]+)/(feed|rdf|rss|rss2|atom)/?$ |
Each post you add increases the size of the rewrite_rules array and puts you one step closer to the breaking point – MySQL's max_allowed_packet variable. By default this is 1MB which means that the size of your statements must be less than that, and the largest row you can retrieve is 1MB. I hit the 1MB limit at around 820 pages.
It's an easy fix; add max_allowed_packet=16M (or however large you'd like to make it) to your MySQL configuration in the [mysqld] section.
