Re: Debian Buster -> Bullseye upgrade issue with UTF8?
[email protected] (Dan Book) Tue, 17 Aug 2021 10:05:30 -0400
| Newsgroups | perl.dbi.users |
|---|---|
| Message-ID | <CABMkAVV13m4GfaKyJCTwYxQ=X5nYKeh-Zrt_ToAV2Ntg1iAsrA@mail.gmail.com> |
--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 <<a href=3D"mailto:[email protected]">simon.cruicksha= [email protected]</a>> 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: '\xF9\x08a\x00\x00\x00...' 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->connect = ("DBI:mysql:host=3Dlocalhost;database=3Dwebapp","webadmin&qu= ot;,"?????????",<br> {PrintError =3D> 0, RaiseError =3D> 1}= );<br>$sth =3D $dbh->prepare ("insert into sessions values (?,?)&qu= ot;);<br>my ($id,$a_session) =3D ($ARGV[0],$ARGV[1]);<br>$a_session =3D &qu= ot;\x{F9}";<br>$sth->execute ($id,$a_session)<br>or die $DBI::errst= r;<br>$sth->finish ();<br>$dbh->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 "mysql_enable_utf8mb4" 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 "mysql_enable_utf8mb4= " on connection and force the string'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--