On 02/26/2015 11:38 PM, Sam Kington wrote:
On 26 Feb 2015, at 18:14, David Cantrell <da...@cantrell.org.uk> wrote:
On Thu, Feb 26, 2015 at 04:55:10PM +0100, Peter Rabbitson wrote:
[...]
Also (as noted in that RT) - this will badly
break BLOB operations.
Good point. That prompted me to look over our code. We had two BLOB
fields, one of which can be replaced with a TEXT. The other really does
contain binary data, but it seems that it's getting corrupted anyway
even without my monkey-patch. Yuck.
The problem appeared to be encoding issues between DBI and mysql. Delving into
DBIx::Class::Storage::DBI:_dbh_execute I found that we were correctly passing a
byte-encoded string from DBIx::Class, but it ended up corrupted in the database:
DB<11> x $sql
0 'INSERT INTO customer_static_file ( bytes, customer_id, date_created,
date_updated, filename, user_id) VALUES ( ?, ?, ?, ?, ?, ? )'
DB<12> x $bind
0 ARRAY(0x122bf2b8)
0 ARRAY(0x122bef70)
0 HASH(0x122780a8)
'dbic_colname' => 'bytes'
'sqlt_datatype' => 'LONGBLOB'
1 "?PNG\cM\cJ\cZ\cJ??Stuff🝖"
DBI appears to know that this is a binary column, but that's not helping.
The solution I arrived at after some googling was as follows:
__PACKAGE__->load_components('FilterColumn');
__PACKAGE__->filter_column(
'bytes',
{
filter_from_storage => sub { pop },
filter_to_storage => sub {
my ($resultset, $binary_data) = @_;
my $encoded_string = sprintf(
"x'%s'",
join('',
map { sprintf('%02x', ord($_)) } split('', $binary_data))
);
return \$encoded_string;
},
}
);
which uses MySQL's preferred way of encoding binary data on the command line.
Is this the best way of handling this, or is there a simpler or purer way I've
overlooked?
Interesting. This is one way of handling it, yes (btw `unpack 'H*',
$binary data` would be much faster). If this definitely fixes things on
a combination of a DBD and RDBMS version (you never said what you are
using) - I can look into making this implicitly handled by the driver
itself.
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
