duan0514324
2015-04-20 16:36
浏览 67
已采纳

在PDO MySQL中读取MEDIUMBLOB时,大约需要1 MB max_allowed_pa​​cket

I'm using PDO to retrieve a 5 MB value in a MEDIUMBLOB column of a table in a MySQL database. A MEDIUMBLOB can store up to 16 MB, but PDO cuts it off at 1 MB because of max_allowed_packet. I tried bindColumn as mentioned in Large Objects, but PDO's MySQL driver produces a string, not a stream (bug 40913, reported as "still present in PHP-5.6.5"). In effect, it treats PDO::PARAM_LOB as a synonym for PDO::PARAM_STR. Answers to BLOB Download Truncated at 1 MB... recommend increasing max_allowed_packet variable in my.cnf on the server, but I lack permission to make changes to my.cnf, which could affect other users of the server. I know it's possible to work around this because phpMyAdmin can download such a large BLOB from the same server.

The table is defined thus:

CREATE TABLE IF NOT EXISTS cache_items (
  `cache_id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `name` VARBINARY(63),
  `value` MEDIUMBLOB NOT NULL,
  `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `expires` DATETIME NOT NULL,
  UNIQUE (`name`),
  INDEX (`expires`)
) ENGINE=INNODB;

The PHP code:

<?php
require_once("dbsettings.php");
$db = new PDO($pdo_dsn, $pdo_username, $pdo_password, $pdo_options);
$name = 'hello';
$read_stmt = $db->prepare("
SELECT `value` FROM `cache_items`
WHERE `name` = :n AND `expires` > CURRENT_TIMESTAMP
ORDER BY `cache_id` DESC LIMIT 1
");
$read_stmt->execute([':n' => $name]);
$read_stmt->bindColumn(1, $value_fp, PDO::PARAM_LOB);
$ok = $read_stmt->fetch(PDO::FETCH_BOUND);
echo gettype($bodyfp);  // string, not resource, because of bug 40913
echo strlen($bodyfp);   // 1048576, not 5xxxxxx, because of max_allowed_packet

So how should a program retrieve a large BLOB? Or would it be more practical to store each value in a file in a directory and then have a periodic task that removes any file from the directory that does not correspond to an unexpired entry in cache_items?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dony39517 2015-04-20 16:36
    已采纳

    I worked around this by making a loop that uses MySQL's SUBSTRING function to read smaller chunks of the value in a loop, where each chunk is smaller than a packet.

    <?php
    // [connection setup omitted]
    $stat_stmt = $db->prepare("
    SELECT `cache_id`, LENGTH(`value`) FROM `cache_items`
    WHERE `name` = :n AND `expires` > CURRENT_TIMESTAMP
    ORDER BY `cache_id` DESC LIMIT 1
    ");
    $read_stmt = $db->prepare("
    SELECT SUBSTRING(`value` FROM :start + 1 FOR 250000)
    FROM `cache_items`
    WHERE `cache_id` = :id
    ");
    
    // Find the ID and length of the cache entry
    $stat_stmt->execute($stat_stmt, [':n'=>$name]);
    $files = $stat_stmt->fetchAll(PDO::FETCH_NUM);
    if (!$files) {
        exit;
    }
    list($cache_id, $length) = $files[0];
    
    // Read in a loop to work around servers with small MySQL max_allowed_packet
    // as well as the fact that PDO::PARAM_LOB on MySQL produces a string instead
    // of a stream
    // https://bugs.php.net/bug.php?id=40913
    $length_so_far = 0;
    $body = [];
    while ($length_so_far < $length) {
        $read_stmt->execute([':id'=>$cache_id, ':start'=>$length_so_far]);
        $piece = $read_stmt->fetchAll(PDO::FETCH_COLUMN, 0);
        if (!$piece) {
            exit;
        }
        $piece = $piece[0];
        if (strlen($piece) < 1) {
            exit;
        }
        $length_so_far += strlen($piece);
        $body[] = $piece;
    }
    echo implode('', $body);
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题