Unknown MySQL error after apt-get upgrade

[rwfranks]

Transcribed verbatim from CPAN RT#133872, warts and all.

Sat Dec 05 13:35:14 2020 mhadji [...] gmail.com - Ticket created
Subject: Unknown MySQL error after apt-get upgrade

Date: Sat, 5 Dec 2020 13:34:46 -0500
To: [email protected]
From: Marios Hadjieleftheriou [email protected]

Hello!

I am getting this error:
./query_db3.pl
SELECT tape
FROM indices.consolidated_tickers
WHERE date <= 20180910 AND ticker = 'RCUS' and tape IS NOT NULL ORDER BY date DESC LIMIT 1
DBD::mysql::st execute failed: Unknown MySQL error at ./query_db3.pl line 18.
Could not execute statement: Unknown MySQL error

---

From this script:

#!/usr/bin/perl

use DBI;

$date=20180910;
$ticker = 'RCUS';
$indices_dbh = DBI->connect("DBI:mysql:database=indices;host=devdb1", "...", "...");

$query = "
    SELECT tape
    FROM indices.consolidated_tickers
    WHERE date <= $date AND ticker = '$ticker' and tape IS NOT NULL ORDER BY date DESC LIMIT 1
";
print STDERR $query;

$sth = $indices_dbh->prepare($query) or die("Could not prepare statement: " . $indices_dbh->errstr . "\n");
$sth->execute() or die("Could not execute statement: " . $indices_dbh->errstr . "\n");

The error manifests every time I run the script on this host:

> uname -a
Linux lccw10.lioncavecapital.com 4.19.0-10-amd64 #1 SMP Debian 4.19.132-1 (2020-07-24) x86_64 GNU/Linux
> perl -v
This is perl 5, version 28, subversion 1 (v5.28.1) built for x86_64-linux-gnu-thread-multi
(with 65 registered patches, see perl -V for more detail)
> cpan -D DBI  
Loading internal logger. Log::Log4perl recommended for better logging
Reading '/home/marioh/.cpan/Metadata'

  Database was generated on Sat, 05 Dec 2020 16:56:02 GMT
DBI
-------------------------------------------------------------------------
    (no description)
    T/TI/TIMB/DBI-1.643.tar.gz
    /usr/lib/x86_64-linux-gnu/perl5/5.28/DBI.pm
    Installed: 1.642
    CPAN:      1.643  Not up to date
    Tim Bunce (TIMB)
    [email protected]

> apt list --installed | grep perl
WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

libapt-pkg-perl/stable,now 0.1.34+b1 amd64 [installed,automatic]
libarchive-zip-perl/stable,now 1.64-1 all [installed,automatic]
libclass-data-inheritable-perl/stable,now 0.08-3 all [installed,automatic]
libconfig-inifiles-perl/stable,now 3.000001-1 all [installed,automatic]
libdbd-mysql-perl/stable,now 4.050-2 amd64 [installed]
libdbi-perl/stable,now 1.642-1+b1 amd64 [installed,automatic]
libdevel-stacktrace-perl/stable,now 2.0300-1 all [installed,automatic]
libdpkg-perl/stable,now 1.19.7 all [installed,automatic]
liberror-perl/stable,now 0.17027-2 all [installed,automatic]
libexception-class-perl/stable,now 1.44-1 all [installed,automatic]
libfile-lchown-perl/stable,now 0.02-2+b5 amd64 [installed,automatic]
libfile-readbackwards-perl/stable,now 1.05-2 all [installed,automatic]
libfile-stripnondeterminism-perl/stable,now 1.1.2-1 all [installed,automatic]
libio-pty-perl/stable,now 1:1.08-1.1+b5 amd64 [installed,automatic]
libipc-run-perl/stable,now 20180523.0-1 all [installed,automatic]
liblocale-gettext-perl/stable,now 1.07-3+b4 amd64 [installed]
libperl5.28/stable,now 5.28.1-6+deb10u1 amd64 [installed,automatic]
libsvn-perl/stable,stable,now 1.10.4-1+deb10u1 amd64 [installed,automatic]
libterm-readkey-perl/stable,now 2.38-1 amd64 [installed,automatic]
libtext-charwidth-perl/stable,now 0.04-7.1+b1 amd64 [installed]
libtext-iconv-perl/stable,now 1.7-5+b7 amd64 [installed]
libtext-wrapi18n-perl/stable,now 0.06-7.1 all [installed]
libtry-tiny-perl/stable,now 0.30-1 all [installed,automatic]
libx11-protocol-other-perl/stable,now 30-1 all [installed,automatic]
libx11-protocol-perl/stable,now 0.56-7 all [installed,automatic]
libyaml-perl/stable,now 1.27-1 all [installed,automatic]
perl-base/stable,now 5.28.1-6+deb10u1 amd64 [installed]
perl-modules-5.28/stable,now 5.28.1-6+deb10u1 all [installed,automatic]
perl-tk/stable,now 1:804.033-2+b3 amd64 [installed,automatic]
perl/stable,now 5.28.1-6+deb10u1 amd64 [installed,automatic]

---

The script works fine on an identical host (before apt-get upgrade):

> uname -a
Linux lccw9.lioncavecapital.com 4.19.0-6-amd64 #1 SMP Debian 4.19.67-2 (2019-08-28) x86_64 GNU/Linux
> perl -v      

This is perl 5, version 28, subversion 1 (v5.28.1) built for x86_64-linux-gnu-thread-multi
(with 61 registered patches, see perl -V for more detail)
> cpan -D DBI
Loading internal logger. Log::Log4perl recommended for better logging
Reading '/home/marioh/.cpan/Metadata'

  Database was generated on Sat, 05 Dec 2020 16:56:02 GMT
DBI
-------------------------------------------------------------------------
    (no description)
    T/TI/TIMB/DBI-1.643.tar.gz
    /usr/lib/x86_64-linux-gnu/perl5/5.28/DBI.pm
    Installed: 1.642
    CPAN:      1.643  Not up to date
    Tim Bunce (TIMB)
    [email protected]

> apt apt list --installed | grep perl
WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

libapt-pkg-perl/stable,now 0.1.34+b1 amd64 [installed,automatic]
libarchive-zip-perl/stable,now 1.64-1 all [installed,automatic]
libclass-data-inheritable-perl/stable,now 0.08-3 all [installed,automatic]
libconfig-inifiles-perl/stable,now 3.000001-1 all [installed,automatic]
libdbd-mysql-perl/stable,now 4.050-2 amd64 [installed]
libdbi-perl/stable,now 1.642-1+b1 amd64 [installed,automatic]
libdevel-stacktrace-perl/stable,now 2.0300-1 all [installed,automatic]
libdpkg-perl/stable,now 1.19.7 all [installed,automatic]
liberror-perl/stable,now 0.17027-2 all [installed,automatic]
libexception-class-perl/stable,now 1.44-1 all [installed,automatic]
libfile-lchown-perl/stable,now 0.02-2+b5 amd64 [installed,automatic]
libfile-readbackwards-perl/stable,now 1.05-2 all [installed,automatic]
libfile-stripnondeterminism-perl/stable,now 1.1.2-1 all [installed,automatic]
liblocale-gettext-perl/stable,now 1.07-3+b4 amd64 [installed]
libperl5.28/stable,now 5.28.1-6 amd64 [installed,automatic]
libsvn-perl/stable,stable,now 1.10.4-1+deb10u1 amd64 [installed,automatic]
libterm-readkey-perl/stable,now 2.38-1 amd64 [installed,automatic]
libtext-charwidth-perl/stable,now 0.04-7.1+b1 amd64 [installed]
libtext-iconv-perl/stable,now 1.7-5+b7 amd64 [installed]
libtext-wrapi18n-perl/stable,now 0.06-7.1 all [installed]
libtry-tiny-perl/stable,now 0.30-1 all [installed,automatic]
libx11-protocol-other-perl/stable,now 30-1 all [installed,automatic]
libx11-protocol-perl/stable,now 0.56-7 all [installed,automatic]
libyaml-perl/stable,now 1.27-1 all [installed,automatic]
perl-base/stable,now 5.28.1-6 amd64 [installed]
perl-modules-5.28/stable,now 5.28.1-6 all [installed,automatic]
perl-tk/stable,now 1:804.033-2+b3 amd64 [installed,automatic]
perl/stable,now 5.28.1-6 amd64 [installed,automatic]

---

So, the two hosts have the same perl and DBI, but one has a newer version of the libraries (after an apt-get upgrade).

Notice that if I remove all newlines from the query strings, the error goes away :)

Thank you for your help!

Tue Dec 08 18:24:47 2020 mhadji [...] gmail.com - Ticket #133884: - Ticket created
Subject: Re: Unknown MySQL error after apt-get upgrade

Date: Tue, 8 Dec 2020 18:24:36 -0500
To: [email protected]
From: Marios Hadjieleftheriou [email protected]

Hi.

I managed to isolate the issue to -Dusethreads. If I compile perl-5.32 with -Dusethreads (apparently the default in Debian), then I get bizarre errors like the one below. If I get rid of -Dusethreads then everything works fine. It issue is also isolated to InnoDB only. If I query tokuDB tables, everything works fine no matter what Debian or perl installation I use.

To summarize, the problem manifests on Debian 4.19.132-1, when compiling perl with -Dusethreads, for InnoDB tables only.

On 12/5/2020 1:34 PM, Marios Hadjieleftheriou wrote:

Hello!

I am getting this error:
./query_db3.pl
    SELECT tape
    FROM indices.consolidated_tickers
    WHERE date <= 20180910 AND ticker = 'RCUS' and tape IS NOT NULL ORDER BY date DESC LIMIT 1
DBD::mysql::st execute failed: Unknown MySQL error at ./query_db3.pl line 18.
Could not execute statement: Unknown MySQL error

From this script:

    #!/usr/bin/perl

    use DBI;

    $date=20180910;
    $ticker = 'RCUS';
    $indices_dbh = DBI->connect("DBI:mysql:database=indices;host=devdb1", "...", "...");

    $query = "
        SELECT tape
        FROM indices.consolidated_tickers
        WHERE date <= $date AND ticker = '$ticker' and tape IS NOT NULL ORDER BY date DESC LIMIT 1";
    print STDERR $query;

    $sth = $indices_dbh->prepare($query) or die("Could not prepare statement: " . $indices_dbh->errstr . "\n");
    $sth->execute() or die("Could not execute statement: " . $indices_dbh->errstr . "\n");

The error manifests every time I run the script on this host:

    > uname -a
    Linux lccw10.lioncavecapital.com 4.19.0-10-amd64 #1 SMP Debian 4.19.132-1 (2020-07-24) x86_64 GNU/Linux
    > perl -v
    This is perl 5, version 28, subversion 1 (v5.28.1) built for x86_64-linux-gnu-thread-multi
    (with 65 registered patches, see perl -V for more detail)
    > cpan -D DBI  
    Loading internal logger. Log::Log4perl recommended for better logging
    Reading '/home/marioh/.cpan/Metadata'

      Database was generated on Sat, 05 Dec 2020 16:56:02 GMT
    DBI
    -------------------------------------------------------------------------
        (no description)
        T/TI/TIMB/DBI-1.643.tar.gz
        /usr/lib/x86_64-linux-gnu/perl5/5.28/DBI.pm
        Installed: 1.642
        CPAN:      1.643  Not up to date
        Tim Bunce (TIMB)
        [email protected]

    > apt list --installed | grep perl
    WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

    libapt-pkg-perl/stable,now 0.1.34+b1 amd64 [installed,automatic]
    libarchive-zip-perl/stable,now 1.64-1 all [installed,automatic]
    libclass-data-inheritable-perl/stable,now 0.08-3 all [installed,automatic]
    libconfig-inifiles-perl/stable,now 3.000001-1 all [installed,automatic]
    libdbd-mysql-perl/stable,now 4.050-2 amd64 [installed]
    libdbi-perl/stable,now 1.642-1+b1 amd64 [installed,automatic]
    libdevel-stacktrace-perl/stable,now 2.0300-1 all [installed,automatic]
    libdpkg-perl/stable,now 1.19.7 all [installed,automatic]
    liberror-perl/stable,now 0.17027-2 all [installed,automatic]
    libexception-class-perl/stable,now 1.44-1 all [installed,automatic]
    libfile-lchown-perl/stable,now 0.02-2+b5 amd64 [installed,automatic]
    libfile-readbackwards-perl/stable,now 1.05-2 all [installed,automatic]
    libfile-stripnondeterminism-perl/stable,now 1.1.2-1 all [installed,automatic]
    libio-pty-perl/stable,now 1:1.08-1.1+b5 amd64 [installed,automatic]
    libipc-run-perl/stable,now 20180523.0-1 all [installed,automatic]
    liblocale-gettext-perl/stable,now 1.07-3+b4 amd64 [installed]
    libperl5.28/stable,now 5.28.1-6+deb10u1 amd64 [installed,automatic]
    libsvn-perl/stable,stable,now 1.10.4-1+deb10u1 amd64 [installed,automatic]
    libterm-readkey-perl/stable,now 2.38-1 amd64 [installed,automatic]
    libtext-charwidth-perl/stable,now 0.04-7.1+b1 amd64 [installed]
    libtext-iconv-perl/stable,now 1.7-5+b7 amd64 [installed]
    libtext-wrapi18n-perl/stable,now 0.06-7.1 all [installed]
    libtry-tiny-perl/stable,now 0.30-1 all [installed,automatic]
    libx11-protocol-other-perl/stable,now 30-1 all [installed,automatic]
    libx11-protocol-perl/stable,now 0.56-7 all [installed,automatic]
    libyaml-perl/stable,now 1.27-1 all [installed,automatic]
    perl-base/stable,now 5.28.1-6+deb10u1 amd64 [installed]
    perl-modules-5.28/stable,now 5.28.1-6+deb10u1 all [installed,automatic]
    perl-tk/stable,now 1:804.033-2+b3 amd64 [installed,automatic]
    perl/stable,now 5.28.1-6+deb10u1 amd64 [installed,automatic]
    ---

The script works fine on an identical host (before apt-get upgrade):

    > uname -a
    Linux lccw9.lioncavecapital.com 4.19.0-6-amd64 #1 SMP Debian 4.19.67-2 (2019-08-28) x86_64 GNU/Linux
    > perl -v      

    This is perl 5, version 28, subversion 1 (v5.28.1) built for x86_64-linux-gnu-thread-multi
    (with 61 registered patches, see perl -V for more detail)
    > cpan -D DBI
    Loading internal logger. Log::Log4perl recommended for better logging
    Reading '/home/marioh/.cpan/Metadata'

      Database was generated on Sat, 05 Dec 2020 16:56:02 GMT
    DBI
    -------------------------------------------------------------------------
        (no description)
        T/TI/TIMB/DBI-1.643.tar.gz
        /usr/lib/x86_64-linux-gnu/perl5/5.28/DBI.pm
        Installed: 1.642
        CPAN:      1.643  Not up to date
        Tim Bunce (TIMB)
        [email protected]

    > apt apt list --installed | grep perl
    WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

    libapt-pkg-perl/stable,now 0.1.34+b1 amd64 [installed,automatic]
    libarchive-zip-perl/stable,now 1.64-1 all [installed,automatic]
    libclass-data-inheritable-perl/stable,now 0.08-3 all [installed,automatic]
    libconfig-inifiles-perl/stable,now 3.000001-1 all [installed,automatic]
    libdbd-mysql-perl/stable,now 4.050-2 amd64 [installed]
    libdbi-perl/stable,now 1.642-1+b1 amd64 [installed,automatic]
    libdevel-stacktrace-perl/stable,now 2.0300-1 all [installed,automatic]
    libdpkg-perl/stable,now 1.19.7 all [installed,automatic]
    liberror-perl/stable,now 0.17027-2 all [installed,automatic]
    libexception-class-perl/stable,now 1.44-1 all [installed,automatic]
    libfile-lchown-perl/stable,now 0.02-2+b5 amd64 [installed,automatic]
    libfile-readbackwards-perl/stable,now 1.05-2 all [installed,automatic]
    libfile-stripnondeterminism-perl/stable,now 1.1.2-1 all [installed,automatic]
    liblocale-gettext-perl/stable,now 1.07-3+b4 amd64 [installed]
    libperl5.28/stable,now 5.28.1-6 amd64 [installed,automatic]
    libsvn-perl/stable,stable,now 1.10.4-1+deb10u1 amd64 [installed,automatic]
    libterm-readkey-perl/stable,now 2.38-1 amd64 [installed,automatic]
    libtext-charwidth-perl/stable,now 0.04-7.1+b1 amd64 [installed]
    libtext-iconv-perl/stable,now 1.7-5+b7 amd64 [installed]
    libtext-wrapi18n-perl/stable,now 0.06-7.1 all [installed]
    libtry-tiny-perl/stable,now 0.30-1 all [installed,automatic]
    libx11-protocol-other-perl/stable,now 30-1 all [installed,automatic]
    libx11-protocol-perl/stable,now 0.56-7 all [installed,automatic]
    libyaml-perl/stable,now 1.27-1 all [installed,automatic]
    perl-base/stable,now 5.28.1-6 amd64 [installed]
    perl-modules-5.28/stable,now 5.28.1-6 all [installed,automatic]
    perl-tk/stable,now 1:804.033-2+b3 amd64 [installed,automatic]
    perl/stable,now 5.28.1-6 amd64 [installed,automatic]

    ---

So, the two hosts have the same perl and DBI, but one has a newer version of the libraries (after an apt-get upgrade).

Notice that if I remove all newlines from the query strings, the error goes away :)

Thank you for your help!

Wed Dec 09 16:41:20 2020 Karen Etheridge - Ticket #133884: - Merged into #133872: Unknown MySQL error after apt-get upgrade

Wed Dec 09 16:41:20 2020 Karen Etheridge - Merged into #133872: Unknown MySQL error after apt-get upgrade