ID: 44998
User updated by: djneoform at gmail dot com
Reported By: djneoform at gmail dot com
Status: Bogus
Bug Type: MySQLi related
Operating System: Win2003 Standard
PHP Version: 5.2.6
Assigned To: andrey
New Comment:
But this can't be expected behavior, my string is being truncated
improperly.
If I manually run the mysql query in the cli, I get proper results:
mysql> INSERT INTO `test_table` VALUES ('2',
'AAAAAAAAAAAAAAAAAAAAAAAAA\0BBBBBBBBBBBBBBBBBBB');
mysql> select * from test_table;
+----+------------------------------------------------------------------------------------------------------+
| id | name
|
+----+------------------------------------------------------------------------------------------------------+
| 1 | AAAAAAAAAAAAAAAAAAAAAAAAA
|
| 2 | AAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBB
|
+----+------------------------------------------------------------------------------------------------------+
If I do the exact same insert query in PHP, I lose half my string.
Clearly this is not due to mysql.
Previous Comments:
------------------------------------------------------------------------
[2009-08-27 10:55:11] and...@php.net
Hi, this is not a bug. Seems like, but it is a feature of MySQL's
BINARY data type.
http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html reads :
---------------------------
When BINARY values are stored, they are right-padded with the pad
value to the specified length. The pad value and how it is handled is
version specific:
- As of MySQL 5.0.15, the pad value is 0x00 (the zero byte). Values are
right-padded with 0x00 on insert, and no trailing bytes are removed on
select. All bytes are significant in comparisons, including ORDER BY and
DISTINCT operations. 0x00 bytes and spaces are different in comparisons,
with 0x00 < space.
Example: For a BINARY(3) column, 'a ' becomes 'a \0' when inserted.
'a\0' becomes 'a\0\0' when inserted. Both inserted values remain
unchanged when selected.
-Before MySQL 5.0.15, the pad value is space. Values are right-padded
with space on insert, and trailing spaces are removed on select.
Trailing spaces are ignored in comparisons, including ORDER BY and
DISTINCT operations. 0x00 bytes and spaces are different in comparisons,
with 0x00 < space.
Example: For a BINARY(3) column, 'a ' becomes 'a ' when inserted and
'a' when selected. 'a\0' becomes 'a\0 ' when inserted and 'a\0' when
selected.
-------------
The \0 byte in the between is not lost, I see it from the network dump,
but \0 has no visual representation when dumped to screen, thus we see
nothing inbetween.
------------------------------------------------------------------------
[2008-10-21 00:38:51] ian at city17 dot ca
Experienced the same thing when storing files in mysql. Many files end
up corrupt since the file ends before it was supposed to.
------------------------------------------------------------------------
[2008-07-24 14:23:20] djneoform at gmail dot com
The resulting dump for the table once i run the script.
/*
MySQL Data Transfer
Source Host: localhost
Source Database: phpneoform
Target Host: localhost
Target Database: phpneoform
Date: 7/24/2008 10:22:31 AM
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for test_table
-- ----------------------------
CREATE TABLE `test_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` binary(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `test_table` VALUES ('1', 'AAAAAAAAAAAAAAAAAAAAAAAAA');
------------------------------------------------------------------------
[2008-07-24 14:20:36] djneoform at gmail dot com
Results:
TEST STRING LENGTH: 51
RETURNED STRING LENGTH: 100
------------------------------------------------------------------------
[2008-07-24 14:19:36] djneoform at gmail dot com
http://phpneoform.com/error.php
Here's this script running on a win2k3 server with PHP 5.2.6 and mysql
5.0.62
<?php
$mysqli = new mysqli('localhost', 'xxx', 'xxx', 'xxx');
$mysqli->query("DROP TABLE IF EXISTS `test_table`");
$mysqli->query("
CREATE TABLE `test_table` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` binary(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
");
$str = str_repeat('A', 25).chr(0x0).str_repeat('B', 25);
echo "TEST STRING LENGTH: ".strlen($str)."<br />\n";
$mysqli->query("
INSERT INTO
`test_table`
SET
`name` = '".$mysqli->real_escape_string($str)."'
");
$id = $mysqli->insert_id;
$result = $mysqli->query("
SELECT
name
FROM
`test_table`
WHERE
id = '".intval($id)."'
");
$result = $result->fetch_object();
echo "RETURNED STRING LENGTH: ".strlen($result->name)."<br />\n";
?>
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/44998
--
Edit this bug report at http://bugs.php.net/?id=44998&edit=1
