Retrieving contents of a blob from MariaDB database returns only NUL bytes

[vadz]

I'd like to leave at least a record of this bug because it's a pretty dangerous because it results in silent data loss: when retrieving blob contents from MariaDB database its contents consists of only 0 bytes if it's long enough.

In my case I have a table

CREATE TABLE `attach_data` (
  `id` mediumint(9) NOT NULL,
  `thedata` longblob NOT NULL,
  PRIMARY KEY (`id`)
)

and I am doing something like

use DBIish;

my $st = $dbh.prepare(q:to/END/);
    SELECT thedata FROM attach_data WHERE id = ?;
    END

$st.execute($attach-id);
my @attachments = $st.allrows(:array-of-hash);
for @attachments -> $attachment {
    my $name = $attachment<filename>;
    my $data = $attachment<thedata>;
    say qq[Saving {$data.elems} bytes to "$name".];
    $name.IO.spurt: $data;
}

And while I get the correct length (e.g. 60196) in the output message and the file has the same correct length too, it only contains zeroes, instead of the PDF data stored in the blob.

Surprisingly, using substr(convert(thedata using latin1), 1, 4000) as thedata in the query allows to retrieve the (part of) actual data, but I have no idea if this always works, as the cutoff length seems to depend on the blob contents. And I couldn't find any other workaround, after trying cast(thedata as char(1000000)), convert(thedata using latin1) and a few other things: as long as the string/buffer is long enough, only zeroes are left in it.