Re: Debian Buster -> Bullseye upgrade issue with UTF8?

--0000000000001eac8005c9c1d04f
Content-Type: text/plain; charset="UTF-8"

On Tue, Aug 17, 2021 at 3:42 AM Simon Cruickshank <
[email protected]> 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

--0000000000001eac8005c9c1d04f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Aug 17, 2021 at 3:42 AM Simon Cru=
ickshank &lt;<a href=3D"mailto:[email protected]">simon.cruicksha=
[email protected]</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>I use perl CGI=
::Session on a Debian buster box, after upgrading to Bullseye the website b=
arfs with message :-<br></div><div><br></div><div>DBD::mysql::db do failed:=
 Incorrect string value: &#39;\xF9\x08a\x00\x00\x00...&#39; for column `web=
app`.`sessions`.`a_session` at row 1 at /usr/share/perl5/CGI/Session</div><=
div><br></div><div>I can recreate the issue without CGI:Session with the fo=
llowing code.</div><div><br></div><div>#! /usr/bin/perl -w<br>use strict;<b=
r>use DBI;<br><br>my ($dbh, $sth, $count);<br><br>$dbh =3D DBI-&gt;connect =
(&quot;DBI:mysql:host=3Dlocalhost;database=3Dwebapp&quot;,&quot;webadmin&qu=
ot;,&quot;?????????&quot;,<br>	{PrintError =3D&gt; 0, RaiseError =3D&gt; 1}=
);<br>$sth =3D $dbh-&gt;prepare (&quot;insert into sessions values (?,?)&qu=
ot;);<br>my ($id,$a_session) =3D ($ARGV[0],$ARGV[1]);<br>$a_session =3D &qu=
ot;\x{F9}&quot;;<br>$sth-&gt;execute ($id,$a_session)<br>or die $DBI::errst=
r;<br>$sth-&gt;finish ();<br>$dbh-&gt;disconnect ();<br>exit (0);</div><div=
><br></div><div>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.</div><div><br></di=
v><div>Is this a change in the mysql backend or a change in the DBI?</div><=
div><br></div><div>Any help would be greatly appreciated.</div></div></bloc=
kquote><div><br></div><div>Hello,</div><div><br></div><div>What versions of=
 DBD::mysql were you using before and after this upgrade?</div><div><br></d=
iv><div>Is the a_session column text/varchar or blob/varbinary?</div><div><=
br></div><div>DBD::mysql has a longstanding encoding bug which cannot be fi=
xed, which means that it will insert different data depending on how Perl d=
ecides to store that data internally.</div><div><br></div><div>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=C2=A0that DBI knows =
not to encode it. (<a href=3D"https://metacpan.org/pod/DBD::MariaDB#Binary-=
parameters">https://metacpan.org/pod/DBD::MariaDB#Binary-parameters</a>) It=
 also means that retrieved text/varchar values will be decoded from bytes f=
or you, similar to the &quot;mysql_enable_utf8mb4&quot; option in DBD::mysq=
l but more consistent.</div><div><br></div><div>If you want consistent beha=
vior using DBD::mysql, a workaround is to enable &quot;mysql_enable_utf8mb4=
&quot; on connection and force the string&#39;s internals to a specific sta=
te just before inserting it:</div><div><br></div><div>utf8::upgrade $text;<=
/div><div># $text can now be inserted to a text column</div><div>utf8::down=
grade $bytes;</div><div># $bytes can now be inserted to a blob column bound=
 as SQL_BLOB</div><div><br></div><div>-Dan</div></div></div>

--0000000000001eac8005c9c1d04f--