On Tue, Aug 17, 2021 at 3:42 AM Simon Cruickshank <
simon.cruicksh...@gmail.com> wrote:
> I use perl CGI::Session on a Debian buster box, after upgrading to
> Bullseye the website barfs with message :-
>
> DBD::mysql::db do failed: Incorrect string value:
> '\xF9\x08a\x00\x00\x00...' for column `webapp`.`sessions`.`a_session` at
> row 1 at /usr/share/perl5/CGI/Session
>
> I can recreate the issue without CGI:Session with the following code.
>
> #! /usr/bin/perl -w
> use strict;
> use DBI;
>
> my ($dbh, $sth, $count);
>
> $dbh = DBI->connect
> ("DBI:mysql:host=localhost;database=webapp","webadmin","?????????",
> {PrintError => 0, RaiseError => 1});
> $sth = $dbh->prepare ("insert into sessions values (?,?)");
> my ($id,$a_session) = ($ARGV[0],$ARGV[1]);
> $a_session = "\x{F9}";
> $sth->execute ($id,$a_session)
> or die $DBI::errstr;
> $sth->finish ();
> $dbh->disconnect ();
> exit (0);
>
> So the issue just seems to be with UTF8? This works fine on Buster but not
> Bullseye. If I encode_utf8 the string the insert works.
>
> Is this a change in the mysql backend or a change in the DBI?
>
> Any help would be greatly appreciated.
>
Hello,
What versions of DBD::mysql were you using before and after this upgrade?
Is the a_session column text/varchar or blob/varbinary?
DBD::mysql has a longstanding encoding bug which cannot be fixed, which
means that it will insert different data depending on how Perl decides to
store that data internally.
DBD::MariaDB is a fork which solves this issue but requires you to
understand that text data must be decoded from bytes before inserting, and
binary data must be inserted as SQL_BLOB or SQL_BINARY bound parameters
so that DBI knows not to encode it. (
https://metacpan.org/pod/DBD::MariaDB#Binary-parameters) It also means that
retrieved text/varchar values will be decoded from bytes for you, similar
to the "mysql_enable_utf8mb4" option in DBD::mysql but more consistent.
If you want consistent behavior using DBD::mysql, a workaround is to enable
"mysql_enable_utf8mb4" on connection and force the string's internals to a
specific state just before inserting it:
utf8::upgrade $text;
# $text can now be inserted to a text column
utf8::downgrade $bytes;
# $bytes can now be inserted to a blob column bound as SQL_BLOB
-Dan
