Skip to content

Instantly share code, notes, and snippets.

@omoon omoon/mysql_pdo.md
Last active Feb 12, 2017

Embed
What would you like to do?

PHP 7.0.15 / MySQL 5.6.35

When you fetch 16777212 byte data from MySQL database via pdo, a null byte character seems to be added to the end of the data.

database

CREATE TABLE `test` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `data` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `data`) VALUES (NULL, NULL);

code

<?php
$id = 1;
for ($i = 16777211; $i < 16777214; $i++) {
    $original_data = str_pad('', $i, 'a');
    /** @var PDO $pdo */
    $pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', '');
    $stmt = $pdo->prepare('update test set data=? where id=?');
    $stmt->execute([$original_data, $id]);

    $stmt = $pdo->prepare('select data from test where id=?');
    $stmt->execute([$id]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    $fetched_data = $result['data'];

    echo "compare\n";
    var_dump($original_data === $fetched_data);
    var_dump(strcmp($original_data, $fetched_data));

    echo "\noriginal_data ===\n";
    echo "length : " . strlen($original_data) . "\n";
    echo "ascii code of the last char : " . ord(substr($original_data, -1)) . "\n\n";

    echo "fetched_data ===\n";
    echo "length : " . strlen($fetched_data) . "\n";
    echo "ascii code of the last char : " . ord(substr($fetched_data, -1)) . "\n\n";

    echo "------------------\n";
}

result

compare
bool(true)
int(0)

original_data ===
length : 16777211
ascii code of the last char : 97

fetched_data ===
length : 16777211
ascii code of the last char : 97

------------------
compare
bool(false)
int(97)

original_data ===
length : 16777212
ascii code of the last char : 97

fetched_data ===
length : 16777212
ascii code of the last char : 0 // null?
------------------
compare
bool(false)
int(97)

original_data ===
length : 16777213
ascii code of the last char : 97

fetched_data ===
length : 16777213
ascii code of the last char : 97
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.