Re: Debian Buster -> Bullseye upgrade issue with UTF8?
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
Re: Debian Buster -> Bullseye upgrade issue with UTF8?
Sorry I was mistaken I actually use CGI::Application::Plugin::Session but I will look into your suggestion. Many thanks, Simon On Tue, 17 Aug 2021 at 10:14, wrote: > Hi Simon > > Did you see the same issue when replacing the deprecated CGI::Session > with my module Data::Session? > > I must confess I don't expect the latter to fix this particular problem. > > --- > Cheers > Ron > savage.net.au > > > On 2021-08-17 17:41, Simon Cruickshank 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. > > > > Best Regards, > > > > Simon >
Re: Debian Buster -> Bullseye upgrade issue with UTF8?
Hi Simon Did you see the same issue when replacing the deprecated CGI::Session with my module Data::Session? I must confess I don't expect the latter to fix this particular problem. --- Cheers Ron savage.net.au On 2021-08-17 17:41, Simon Cruickshank 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. Best Regards, Simon