Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.9.3

Bug #10211 MySQL v5.x 'bit' fields not recognized
Submitted: 2007-02-27 19:31 UTC
From: mbuchmann at shaw dot ca Assigned: aharvey
Status: Closed Package: DB (version 1.7.9)
PHP Version: 5.1.6 OS: RHEL3
Roadmaps: 1.7.10    

 [2007-02-27 19:31 UTC] mbuchmann at shaw dot ca (Marc)
Description: ------------ MySQL introduced true 'bit' fields in the 5.x series, superceding the tinyint(1) fields they'd been using to fake them in previous versions. DB 1.7.9 (haven't tested previous versions, but they're very likely to be also affected) report these bit fields as type "unknown" and default them to a 'true' value for boolean true/false, no matter what's actually stored in the field. NULL values come through as blank, as expected. Workaround is the modify the query to CAST() the bit fields to an unsigned integer. Test script: --------------- MySQL: CREATE TABLE bugsample ( bitfield BIT default FALSE ); INSERT INTO bugsample (bitfield) VALUES (true), (false), (NULL); // create 3 records with the extended insert syntax PHP: $query = <<<EOF SELECT bitfield AS buggy, CAST(bitfield AS unsigned integer) AS fixed FROM bugsample; EOF; $sth = $dbh->query($query); print_r($dbh->tableInfo($sth)); while(list($buggy, $fixed) = $sth->fetchRow()) { $x = ($buggy) ? 'true' : 'false'; print("buggy: $buggy/$x fixed: $fixed\n"); } Expected result: ---------------- Array ( [0] => Array ( [table] => bugsample [name] => buggy [type] => bit <----!!!!!! [len] => 1 [flags] => ) [1] => Array ( [table] => [name] => fixed [type] => int [len] => 1 [flags] => unsigned binary ) ) buggy: /true fixed: 1 buggy: �/false fixed: 0 <---!!!!!!! buggy: /false fixed: Actual result: -------------- Array ( [0] => Array ( [table] => bugsample [name] => buggy [type] => unknown [len] => 1 [flags] => ) [1] => Array ( [table] => [name] => fixed [type] => int [len] => 1 [flags] => unsigned binary ) ) buggy: /true fixed: 1 buggy: �/true fixed: 0 <---!!!!!! buggy: /false fixed:


 [2007-03-20 09:24 UTC] aharvey (Adam Harvey)
This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on by the end of next Sunday (CET). If this was a problem with the website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better. The short version: The mysqli driver will now return "bit" for bit fields in tableInfo(); otherwise no additional support has been added for bit fields. For the morbidly curious... I can't do anything for the mysql driver, as ext/mysql (specifically mysql_field_type) doesn't support bit fields at present. ext/mysqli does have minimal support for bit fields, and DB_mysqli::tableInfo() has been updated to reflect that. I'm not going to change the behaviour of either the mysql or mysqli drivers to deal more gracefully with bit fields for now -- that would require special-case code that would have to call back to the database extension to check the field type, which would be slow, wouldn't work with ext/mysql, and it's not really the right place to deal with it. The MySQL extensions within PHP should be updated to return more sane values, rather than handling this within PEAR DB and then being forced into maintaining a certain type of behaviour regardless of future changes to PHP. Although not addressed directly by this bug, for the record: I won't be adding support for the b'0101' data format introduced in MySQL 5.0.3 either, due to the fact that it could break quoting for previously valid string values.