Skip Menu |
 

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 121921
Status: resolved
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: markus [...] wernig.net
Cc: pali [...] cpan.org
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



MIME-Version: 1.0
X-Spam-Status: No, score=-1.999 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, SPF_FAIL=0.001] autolearn=no
X-Cpan.org: This message routed through the cpan.org mail forwarding service. Please use PAUSE pause.perl.org to configure your delivery settings.
X-Spam-Flag: NO
Content-Type: multipart/signed; boundary="------------ms080106050602070805070502"; micalg="sha-256"; protocol="application/pkcs7-signature"
Message-ID: <e3710280-d83b-37bc-79ae-7eedb9e0e1aa [...] wernig.net>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Virus-Scanned: clamav-milter 0.99.2 at xfer-srv02-mail
X-Spam-Score: -1.999
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 3C39D24022A for <cpan-bug+DBD-mysql [...] hipster.bestpractical.com>; Sun, 28 May 2017 09:22:03 -0400 (EDT)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id BrHmCbFdkoez for <cpan-bug+DBD-mysql [...] hipster.bestpractical.com>; Sun, 28 May 2017 09:22:00 -0400 (EDT)
Received: from xx1.develooper.com (xx1.develooper.com [207.171.7.115]) by hipster.bestpractical.com (Postfix) with ESMTPS id 8F18C2400FC for <bug-DBD-mysql [...] rt.cpan.org>; Sun, 28 May 2017 09:22:00 -0400 (EDT)
Received: from localhost (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with ESMTP id 8E10811F2E9 for <bug-DBD-mysql [...] rt.cpan.org>; Sun, 28 May 2017 06:21:58 -0700 (PDT)
Received: from xx1.develooper.com (xx1.develooper.com [127.0.0.1]) by localhost (Postfix) with SMTP id DED0E11F31A for <bug-DBD-mysql [...] rt.cpan.org>; Sun, 28 May 2017 06:21:54 -0700 (PDT)
Received: from mail01.xfer.ch (mail01.xfer.ch [217.11.218.165]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by xx1.develooper.com (Postfix) with ESMTPS id 14A3D11F2E9 for <bug-DBD-mysql [...] rt.cpan.org>; Sun, 28 May 2017 06:21:48 -0700 (PDT)
Received: from [192.168.10.146] (84-73-128-119.dclient.hispeed.ch [84.73.128.119]) (authenticated bits=0) by mail01.xfer.ch (8.15.2/8.15.2) with ESMTPSA id v4SDJLgx027511 (version=TLSv1.2 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for <bug-DBD-mysql [...] rt.cpan.org>; Sun, 28 May 2017 15:19:21 +0200 (CEST)
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] wernig.net
Delivered-To: cpan-bug+DBD-mysql [...] hipster.bestpractical.com
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.8.0
Subject: DBD-mysql-4.42.0 encodes binary blobs when storing
Return-Path: <markus [...] wernig.net>
X-RT-Mail-Extension: dbd-mysql
X-Original-To: cpan-bug+DBD-mysql [...] hipster.bestpractical.com
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=wernig.net; s=mail; t=1495977561; bh=y/JRJbVIQcNq4IH/B81D3neHQiGzsg4pUvL8+Kx9E2s=; h=To:From:Subject:Date; b=Sp3PDpi4nXrbfHo5pYjQ3IdtYGFBSJe0DFSte7Pmq5gl2XjPK/ih04ShgX8k1NKbp xKHaFc91dwEqFGN9C7MZLyuA1Xiy1n/blvdPy3N1n5sxJCH+HwCRqx7g1nGjsZ4qeB HRvgusJvWlh5p35V13yrWHDQRN1NMxSqVz2njvso=
X-PMX-Spam: Gauge=IIIIIIII, Probability=8%, Report=' HTML_00_01 0.05, HTML_00_10 0.05, BODYTEXTP_SIZE_3000_LESS 0, DKIM_SIGNATURE 0, NO_CTA_URI_FOUND 0, NO_URI_FOUND 0, NO_URI_HTTPS 0, SPF_PASS 0, __ATTACHMENT_SIZE_0_10K 0, __BAT_BOUNDARY 0, __CT 0, __CTYPE_HAS_BOUNDARY 0, __CTYPE_MULTIPART 0, __FRAUD_MONEY_CURRENCY 0, __FRAUD_MONEY_CURRENCY_DOLLAR 0, __HAS_ATTACHMENT 0, __HAS_ATTACHMENT1 0, __HAS_ATTACHMENT2 0, __HAS_FROM 0, __HAS_MSGID 0, __MIME_TEXT_P 0, __MIME_TEXT_P1 0, __MIME_TEXT_P2 0, __MIME_VERSION 0, __MOZILLA_USER_AGENT 0, __NO_HTML_TAG_RAW 0, __SANE_MSGID 0, __SUBJ_ALPHA_END 0, __TO_MALFORMED_2 0, __TO_NO_NAME 0, __USER_AGENT 0, __blackholes.mail-abuse.org_TIMEOUT , __zen.spamhaus.org_ERROR '
Date: Sun, 28 May 2017 15:19:20 +0200
X-Spam-Level:
X-Virus-Status: Clean
X-Greylist: delayed 142 seconds by postgrey-1.34 at xx1.develooper.com; Sun, 28 May 2017 06:21:49 PDT
X-PMX-Version: 5.6.1.2065439, Antispam-Engine: 2.7.2.376379, Antispam-Data: 2017.5.28.131515
To: bug-DBD-mysql [...] rt.cpan.org
From: Markus Wernig <markus [...] wernig.net>
X-RT-Interface: Email
Content-Length: 0
content-type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-RT-Original-Encoding: utf-8
Content-Length: 2718
Download (untitled) / with headers
text/plain 2.6k
Hi all I have an application that, among others, has to store PDF files in a mysql db. This has been running for almost 10 years now. After upgrading DBD-mysql to 4.42.0, the PDF files get corrupted when storing them to the db. It appears that they are somehow encoded in a character set (presumably utf8), even though the column definition is "mediumblob". 4.41.0 and earlier versions do not show that behaviour, a downgrade of DBD::mysql without any other changes restores the correct behaviour. Here is how the db is connected: my $dbh = DBI->connect(${dsn}, ${username}, ${passwd}, { RaiseError => 1, AutoCommit => 1, AutoInactiveDestroy => 1, mysql_auto_reconnect => 1, mysql_enable_utf8 => 1, } ) or die("DB connect failed: $DBI::errstr"); The code then goes on to insert data into tables like this one: +------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+-------+ | id | bigint(20) unsigned | NO | PRI | NULL | | | name | text | NO | | NULL | | | data | mediumblob | NO | | NULL | | | doctype | text | NO | | NULL | | +------------+---------------------+------+-----+---------+-------+ like this: my $sql = "INSERT INTO $table (id, name, data, doctype) VALUES (?, ?, ?, ?)"; my $sth = $dbh->prepare($sql); # $data_in is the raw PDF file data $logger->log("File $name_in has " . length($data_in) . " bytes and hash " . sha256_hex($data_in)); $sth->execute($id_in, $name_in, $data_in, "application/pdf"); The log entry shows the correct size and hash, identical to what the file looks like on disk. May 28 14:54:17 dev middleware[26817]: File testdoc.pdf has 493392 bytes and hash 403da58f84328365c8bdb646bfa008f31b44f2c391dc5d40eefa6963bc49c991 But after retrieving the blob (either via the app or via CLI), the file size is much larger (700562), the hash is different, and the file is corrupt and cannot be opened by any reader. MariaDB [pdfdb]> select data from $table where id = 48 into dumpfile "/tmp/48.dump"; # ls -l /tmp/48.dump -rw-rw-rw- 1 mysql mysql 700562 May 28 14:56 /tmp/48.dump I've tried re-encoding the file (with vim) to latin1, which results in the original file size 493392, but still leaves the PDF corrupted. I assume that this is a bug in DBD::mysql. System: Gentoo Linux ~amd64, kernel 4.10.5-gentoo Perl 5.24.1 DBD::mysql 4.42.0 Thanks for looking into this. Markus
Content-Description: S/MIME Cryptographic Signature
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Disposition: attachment; filename="smime.p7s"
Content-Transfer-Encoding: base64
Content-Length: 4100
Download smime.p7s
application/pkcs7-signature 4k

Message body not shown because it is not plain text.

MIME-Version: 1.0
In-Reply-To: <e3710280-d83b-37bc-79ae-7eedb9e0e1aa [...] wernig.net>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <e3710280-d83b-37bc-79ae-7eedb9e0e1aa [...] wernig.net>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-21914-1497618190-377.121921-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 3282
Download (untitled) / with headers
text/plain 3.2k
On Ned Máj 28 09:22:04 2017, markus@wernig.net wrote: Show quoted text
> Hi all > > I have an application that, among others, has to store PDF files in a > mysql db. This has been running for almost 10 years now. > > After upgrading DBD-mysql to 4.42.0, the PDF files get corrupted when > storing them to the db. It appears that they are somehow encoded in a > character set (presumably utf8), even though the column definition is > "mediumblob". > > 4.41.0 and earlier versions do not show that behaviour, a downgrade of > DBD::mysql without any other changes restores the correct behaviour. > > > Here is how the db is connected: > > my $dbh = DBI->connect(${dsn}, > ${username}, > ${passwd}, > { RaiseError => 1, > AutoCommit => 1, > AutoInactiveDestroy => 1, > mysql_auto_reconnect => 1, > mysql_enable_utf8 => 1, > } > ) > or die("DB connect failed: $DBI::errstr"); > > The code then goes on to insert data into tables like this one: > > +------------+---------------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +------------+---------------------+------+-----+---------+-------+ > | id | bigint(20) unsigned | NO | PRI | NULL | | > | name | text | NO | | NULL | | > | data | mediumblob | NO | | NULL | | > | doctype | text | NO | | NULL | | > +------------+---------------------+------+-----+---------+-------+ > > like this: > > my $sql = "INSERT INTO $table (id, name, data, doctype) > VALUES (?, ?, ?, ?)"; > my $sth = $dbh->prepare($sql); > > # $data_in is the raw PDF file data > $logger->log("File $name_in has " . length($data_in) . " bytes and hash > " . sha256_hex($data_in)); > > $sth->execute($id_in, $name_in, $data_in, "application/pdf"); > > The log entry shows the correct size and hash, identical to what the > file looks like on disk. > > May 28 14:54:17 dev middleware[26817]: File testdoc.pdf has 493392 bytes > and hash 403da58f84328365c8bdb646bfa008f31b44f2c391dc5d40eefa6963bc49c991 > > But after retrieving the blob (either via the app or via CLI), the file > size is much larger (700562), the hash is different, and the file is > corrupt and cannot be opened by any reader. > > MariaDB [pdfdb]> select data from $table where id = 48 into dumpfile > "/tmp/48.dump"; > > # ls -l /tmp/48.dump > -rw-rw-rw- 1 mysql mysql 700562 May 28 14:56 /tmp/48.dump > > I've tried re-encoding the file (with vim) to latin1, which results in > the original file size 493392, but still leaves the PDF corrupted. > > > I assume that this is a bug in DBD::mysql. > > System: Gentoo Linux ~amd64, kernel 4.10.5-gentoo > Perl 5.24.1 > DBD::mysql 4.42.0 > > > Thanks for looking into this. > > Markus > >
Duplicate of: https://rt.cpan.org/Public/Bug/Display.html?id=120953 https://github.com/perl5-dbi/DBD-mysql/issues/107 See also for more details: https://rt.cpan.org/Ticket/Display.html?id=25590 https://rt.cpan.org/Ticket/Display.html?id=60987 https://rt.cpan.org/Ticket/Display.html?id=53130 https://rt.cpan.org/Ticket/Display.html?id=87428
X-RT-Interface: REST
MIME-Version: 1.0
X-Mailer: MIME-tools 5.504 (Entity 5.504)
RT-Message-ID: <rt-4.0.18-6098-1510732715-1521.121921-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Content-Disposition: inline
Content-Transfer-Encoding: binary
Content-Length: 78


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.