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

[Dan Book]

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: Weird issues using DBI + mod_perl

[Dan Book]

Also make sure you set AutoInactiveDestroy, which would be default if not
for back compat, otherwise a fork that doesn't even use the handle may
close your connections.

Making sure connections are specific to one process can be tedious,
especially when you are not controlling the processes, one easier way to
manage it is with DBIx::Connector (https://metacpan.org/pod/DBIx::Connector),
the DBIx::Connector object can be shared between any forks and whenever
->run or ->txn or ->dbh is called it will make sure to get or create a
connection specific to that process.

-Dan

On Fri, Aug 16, 2019 at 8:34 PM Andreas Mock  wrote:

> Hi Bruce,
>
> I'm just guessing, but this sounds much like reusing or double using of
> Oracle db handles, context handles or statement handles.
>
> You have to have a look at the bookkeeping of the handles per request.
> Also forking of the childs may byte you.
> You must ensure that db handles are ONLY opened in the child process doing
> the work and get closed properly.
>
> With DBI in use you can use the variable DBI_TRACE to get extensive log.
> Google for that in combination with mod_perl.
>
> Just some hints.
>
> Best regards
> Andreas
>
>
> -Ursprüngliche Nachricht-
> Von: Bruce Johnson 
> Gesendet: Freitag, 16. August 2019 23:36
> An: dbi users ; mod_perl list  >
> Betreff: Weird issues using DBI + mod_perl
>
> So I’ve built a site using mod_perl in CGI mode, with DBI and Oracle as my
> database back end.
>
> Everything works fine for a while but then I get intermittent weird errors
> that start happening for every page load, which is resulting in
> non-functional pages and error messages “Something happened contact your
> system administrator”
>
> "DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD
> ERROR: OCIStmtExecute) [for Statement "select to_char(add_months(sysdate,
> 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual”]” at
> /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267,  line
> 522.\n
>
> There are no bound variables in the query...
>
> Another one:  [error] DBD::Oracle::st execute failed: ORA-01007: variable
> not in select list (DBD ERROR: OCIStmtExecute) [for Statement "select
> to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate,
> 24), 'J') from dual"] at
> /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267,  line
> 522.\n
>
> Other errors that happen are :
>
> DBD::Oracle::st execute failed: ORA-01007: variable not in select list
> (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, email, afflist,
> bldg_id, start_task from login_info where cookie =?" with ParamValues:
> :p1='NS9DdciuH7XPeSVygRyUjoviZ’]
>
> DBD::Oracle::db prepare failed: ORA-01002: fetch out of sequence (DBD
> ERROR: OCIStmtExecute/Describe) [for Statement "select
> to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate,
> 24), 'J') from dual"] at
> /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 266,  line
> 522.\n
>
> DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication
> protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn,
> email, afflist, bldg_id, start_task from login_info where cookie =?" with
> ParamValues: :p1='v2bpY8jnoPw5yf0x71I4wZPx6'] at
> /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 307
>
> Most of these errors (that cause the DBI stuff to break) are happening in
> this defaults module, which IS referenced more than once per page load as
> the pages have ajax calls to other scripts that reference this module.
>
> restarting httpd fixes the problem, for a while. This isn’t a very heavily
> loaded site, right now it’s the middle of the afternoon and I'm seeing
> 'accesses per minute' kind of traffic.
>
> And clues as to where I should start to look?
>
> --
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
>
> Institutions do not have opinions, merely customs
>

Re: Bugtracker DBD::mysql

[Dan Book]

On Tue, Nov 14, 2017 at 8:34 AM, Michiel Beijen 
wrote:

> On Tue, Nov 14, 2017 at 11:07 AM,   wrote:
>
> > Hi! As there are only few tickets on github, it would be easier to
> > disable creating new tickets on github and those few which are not
> > resolved yet either move on RT or (if github allows it; which I believe
> > yet) continue to exists.
> >
> > And once any migration is ready, then it can be fully switched.
> >
> > Still one bugtracker is better then two, even if it is not on github.
>
> Yeah, I'm sure this is *easier*. But I really hate the RT cpan bug
> tracker, so I'd prefer to look again at migrating from RT >> Github -
> if that really would not work for some reason I'll consider migrating
> the other way around.
>
>
I also prefer github but of course it's mainly up to what is easiest for
the maintainers. Here is a script for migrating tickets if it's not what
you already tried: https://metacpan.org/pod/RTx::ToGitHub

-Dan

Re: DBD::mysql next steps

[Dan Book]

On Fri, Nov 10, 2017 at 8:43 PM, Noel Butler  wrote:
>
> Given that things are only ever going to move forward with my/maria-sql
> would it not be better to enable this by default, and have a "disable"
> setting for those who want to run something of antiques?
>
> Because in years to come there will only ever be the modern versions and
> you;ll have to change default behaviour then anyway, but since that time is
> mostly here now, it makes more sense to enable it by default.
>

No, this whole issue came about because existing code requires modification
to continue working. Requiring a new option to be set to enable old
behavior would cause the same problem. Backcompat is "forever".

-Dan

Re: DBD::mysql path forward

[Dan Book]

It seems to me like the remaining option that can make everyone "happy" is
the previously-suggested option of maintaining a legacy branch and doing
new development (reinstating 4.042) in another branch which will be
released as a new distribution, like DBD::mysql2, by the same maintainers.
(I would not favor DBD::MariaDB as a name, since this new distribution
would also be the favored way to connect to MySQL.) After doing so
DBD::mysql can be deprecated, with migration instructions added to the
docs, and only receive critical security fixes if anything. If done this
way the community should not be fractured; the current module will simply
be abandoned for future development, all support and maintenance will move
to the new one. Patrick and Michiel, what do you think?

-Dan

Re: DBD::mysql path forward

[Dan Book]

How can we proceed from here?
-Dan

On Mon, Sep 18, 2017 at 1:17 PM, Patrick M. Galbraith  wrote:

> Pali,
>
> Great! Now we can start moving forward.
>
> Sorry if my responses have been intermittent - first week at new job.
>
> Regards,
>
> Patrick
> On 9/16/17 4:35 AM, p...@cpan.org wrote:
>
> I prepared branch master-new, which is based on current DBD-mysql master
> branch and revert state to pre-4.043 version, including all changes done
> after 4.043 release to master branch. I have this master-new branch in
> my fork. If you want you can use it...
> https://github.com/pali/DBD-mysql/tree/master-new
>
>

Re: DBD::mysql path forward

[Dan Book]

On Wed, Sep 13, 2017 at 3:53 AM, Peter Rabbitson  wrote:

> On 09/12/2017 07:12 PM, p...@cpan.org wrote:
>
>> On Tuesday 12 September 2017 12:27:25 p...@cpan.org wrote:
>>
>>> To prove fact that other DBI drivers (e.g. Pg or SQLite) had fixed
>>> similar/same UTF-8 issue as MySQL has and behave Perl-correctly, I
>>> would provide test cases so you would see difference between Pg,
>>> SQLite and mysql DBI drivers.
>>>
>>
>> And here is promised script:
>>
>>
> 
>
> The script side-steps showcasing the treatment of BLOB/BYTEA columns,
> which was one of the main ( albeit not the only ) reason the userbase lost
> data.
>
> Please extend the script with a BLOB/BYTEA test.
>

I'm not sure how to usefully make such a script, since correct insertion of
BLOB data (binding with the SQL_BLOB type or similar) would work correctly
both before and after the fix.

-Dan

Re: DBD::mysql path forward

[Dan Book]

On Tue, Sep 12, 2017 at 11:54 AM, Dan Book <gri...@gmail.com> wrote:

> On Tue, Sep 12, 2017 at 11:04 AM, Patrick M. Galbraith <p...@patg.net>
> wrote:
>
>> Pali,
>>
>> Yes, I agree, we'll have to create a fork pre revert and stop accepting
>> PRs
>>
>> How might we allow people time to test the fixes to give them time? Just
>> have them use the fork, I would assume?
>>
>> Regards,
>>
>> Patrick
>>
>
> To be clear, this sounds like a branch not a fork. If your plan is to
> reinstate the mysql_enable_utf8 behavior as in 4.042 rather than adding a
> new option for this behavior, then branching from 4.042 seems reasonable to
> me; but you should be very clear if this is your intended approach, as this
> is what led to many people corrupting data as they send blobs to mysql with
> the same mysql_enable_utf8 option, and expect them to accidentally not get
> encoded.
>
> -Dan
>

And one other note, if you do reinstate the 4.042 behavior, due to the
backcompat breakage at the very least this should be a 5.0 release.

-Dan

Re: DBD::mysql path forward

[Dan Book]

On Tue, Sep 12, 2017 at 11:04 AM, Patrick M. Galbraith 
wrote:

> Pali,
>
> Yes, I agree, we'll have to create a fork pre revert and stop accepting PRs
>
> How might we allow people time to test the fixes to give them time? Just
> have them use the fork, I would assume?
>
> Regards,
>
> Patrick
>

To be clear, this sounds like a branch not a fork. If your plan is to
reinstate the mysql_enable_utf8 behavior as in 4.042 rather than adding a
new option for this behavior, then branching from 4.042 seems reasonable to
me; but you should be very clear if this is your intended approach, as this
is what led to many people corrupting data as they send blobs to mysql with
the same mysql_enable_utf8 option, and expect them to accidentally not get
encoded.

-Dan

Re: [External] Fork DBD::mysql

[Dan Book]

On Tue, Aug 29, 2017 at 3:36 AM,  wrote:

> Then users of MySQL would stay with DBD::mysql and they would not have
> fixed DBD driver. We were thinking about choosing DBD::mariadb name
> for our fork. But without dropping MySQL support, so also MySQL users
> could benefit from it.
>
> On Monday 28 August 2017 18:08:20 Daniël van Eeden wrote:
> > What about splitting it in DBD::mysql and DBD::mariadb ?
> >
> >
> > On 28 August 2017 5:55:09 p.m. p...@cpan.org wrote:
> >
> > >Hello, 2 months passed since two security problems related to DBD::mysql
> > >were reported, namely CVE-2017-10789 and CVE-2017-10788. Looking at the
> > >DBD::mysql github project page and RT bugtracker, it can be seen that
> > >nothing has happened for 2 months. Some people are asking or waiting for
> > >reported problems to be fixed... Without an answer.
> > >
> > >A long discussion was started in April by a group of people who didn't
> > >like the improvements and didn't want to see the bugs fixed in
> > >DBD::mysql driver due to the fact that they probably had old and buggy
> > >legacy code which wouldn't work after DBD::mysql starts fixing bugs.
> > >
> > >From what happened it looks like that this group of people forced
> > >DBD::mysql maintainers to revert all DBD::mysql changes (with all bug
> > >fixes including security ones). And because nothing has happened for 2
> > >months, that looks like fixing those bugs is not planned.
> > >
> > >This means just one thing:
> > >
> > >  DBD::mysql is dead right now :-(
> > >
> > >There are no new commit for 2 months, no evidence of any activity. And
> > >it looks like this is what that group of people wanted. They haven't
> > >written anything, neither any comment about open security issues, nor
> > >any comment that they forced maintainers to revert a security related
> > >bug fix. Probably they don't have to fix their own code and they can use
> > >the last released version of DBD::mysql. Perfect for them, probably a
> > >catastrophe for all others which don't want to use probably-vulnerable
> > >code.
> > >
> > >But it also means that it isn't possible to compile the last DBD::mysql
> > >with the new MySQL 8 or new MariaDB versions. More linux distributions
> > >started to pack/bundle their own DBD::mysql patches just because they
> > >need to build DBD::mysql into their repositories with upgraded MariaDB.
> > >
> > >I need to say that such situation is not acceptable for *any* future or
> > >new development in Perl which uses either MySQL or MariaDB database.
> > >
> > >DBD::mysql has couple of bugs (some are security released) and fully
> > >non-working UTF-8/Unicode support. It means that any internationalized
> > >applications which use MySQL are hard to write in Perl.
> > >
> > >Because of the current situation we in the GoodData company are thinking
> > >about forking DBD::mysql. We use DBI not only with DBD::mysql, but also
> > >with DBD::Pg. The annoying bugs in DBD::mysql still require hacks and
> > >workarounds, while similar bugs were fixed in DBD::Pg years ago. Also,
> > >inability to use the new version of MySQL or MariaDB is a problem. Same
> > >for the open security issues.
> > >
> > >I would like to ask, whether somebody else is interested in DBD::mysql
> > >fork? Either as a user or as a developer? Maintaining a fork isn't a
> > >simple task, but with supporting users and contributing developers it
> > >should be easier.
> > >
> > >And specially, are MariaDB developers interested in fixing/improving
> > >DBD::mysql fork, so Perl could work better with MariaDB servers? At
> > >least it would be able to compile against the new MariaDB C-client?
> > >
>

My reply yesterday seems to not have gone through, so I am replying again
here.

Though the reversion was made with good intentions of preserving back
compat in the short term, it was accompanied by a promise of reapplying the
many important fixes that were reverted (
https://github.com/perl5-dbi/DBD-mysql/compare/4.041...4.042 ) as well as
re-adding a correct utf8 encoding option, as it is still necessary to fix
this broken behavior one way or another. Since nothing has happened,
forking may be the only reasonable option. We are currently version pinned
to 4.042 as "upgrading" to 4.043 would be a massive regression in our
codebase.

-Dan