Problem with utf8 chars in BLOB field

[adifairbank]

There seems to be a bug in DBD::MariaDB with utf8 chars inserted into.a BLOB. It works fine using the mysql client, but see the following simple test script to reproduce the issue. You just need a "test" database with user "test" and no password.

I am testing this on Rocky Linux 9.7, mariadb-server-10.5.29, DBD::MariaDB 1.24.

#!/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:MariaDB:database=test;host=127.0.0.1;port=3306", 'test', '', {RaiseError => 1});
my $str = "👉";
utf8::upgrade $str;

$dbh->do('CREATE TEMPORARY TABLE test_utf8 ( id  VARCHAR(24) NOT NULL PRIMARY KEY, blob_value BLOB ) ENGINE InnoDB DEFAULT CHARSET=utf8mb4');

$dbh->do("INSERT INTO test_utf8 (id, blob_value) VALUES (?, ?)", {}, "test", $str);
my $a = $dbh->selectall_arrayref("SELECT id,blob_value FROM test_utf8");

foreach (@$a) { print join ";", @$_, "\n"; }

On my system, I get output:

test;ð;

[Grinnz]

When inserting into a blob field, you must bind the parameter as a BLOB type or it will be encoded as text. It's annoying, but the client cannot know whether the parameter will be used as blob or text, and defaults to text for parameters. See https://metacpan.org/pod/DBD::MariaDB#Binary-parameters

[Grinnz]

Note that this accidentally works in DBD::mysql, but only if the string is upgraded. In DBD::MariaDB, the same bytes are inserted regardless of string upgrading, which is the correct Perl behavior (so your utf8::upgrade is unnecessary here).

[adifairbank]

Ah, I see- thanks for educating. Will have to do some code updating.

My code has been using DBD::mysql, and this is the first time it's seen DBD::MariaDB. (as you probably guessed)

[Grinnz]

I also have made such conversions and wrote this regarding it: https://blogs.perl.org/users/grinnz/2023/12/migrating-from-dbdmysql-to-dbdmariadb.html