2

I'm trying to select data from a mysql LONGBLOB field, but the memory usage seems to grow out of control. I've tried with PDO and mysqli. (no problem inserting the data)

<?php
$path = 'test.txt';
$pdo = new PDO('mysql:host=localhost;dbname=db', 'user', 'pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);
$stmt = $pdo->prepare("select contents from files where path = :path limit 1");
$stmt->bindValue(':path', $path, PDO::PARAM_STR);
$result = $stmt->execute();
$stmt->bindColumn(1, $data, PDO::PARAM_LOB);
$result = $stmt->fetch(PDO::FETCH_BOUND);
var_dump(strlen($data));
var_dump(memory_get_peak_usage());

The output when running that is:
int(198608116)
int(1902241376)

so the column is 198.61MB, but the scripts memory usage reaches 10 times that 1,902.24MB.

Any idea what I am doing wrong?

noam
  • 538
  • 5
  • 19
oblong
  • 21
  • 3
  • What happens when you call `var_dump(memory_get_peak_usage());` in different places of the code? Before instantiating `PDO`, before executing the query, etc. – noam Jul 24 '20 at 18:31
  • hi noam, it's low until this runs: $stmt->fetch(PDO::FETCH_BOUND); – oblong Jul 24 '20 at 18:39
  • Are you really sure that storing 198MB objects in the database is a good idea? – Nigel Ren Jul 24 '20 at 18:41
  • I'm also not thinking it's a good idea . It's for an existing system though, and I'm wondering what is happening here. – oblong Jul 24 '20 at 18:47
  • I just modified the select statement to this "select LEFT(contents, 1000)". and gradually increased the length I select.. I see the memory usage starts small and grows out of control (exponentially?). – oblong Jul 24 '20 at 18:51
  • I think some things are mixed up. The length of data must not be the same how many byte it will consume in memory. – Markus Zeller Jul 24 '20 at 18:55

0 Answers0