Skip Menu |
 

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

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

People
Owner: Nobody in particular
Requestors: jdiepen [...] cpan.org
Cc: joost [...] zeekat.nl
AdminCc:

Bug Information
Severity: Important
Broken in:
  • 3.0007_1
  • 3.0007_2
Fixed in: (no value)

Attachments


CC: joost [...] zeekat.nl
Subject: mysql_enable_utf8 switches on utf8 flag for BLOB & GEOMETRY columns
Download (untitled) / with headers
text/plain 753b
Using mysql_enable_utf8 switches on the utf8 flags for BLOB and GEOMETRY columns resulting in much nastyness. The provided patch fixes this partially, by using the internal type MYSQL_TYPE_BLOB for BLOB and GEOMETRY colunms (instead of MYSQL_TYPE_STRING). It doesn't work when "use_server_side_prepare" is false, since I can't figure out how to get at the column type in that case (at lines 3541 - 3544 of dbdimp.c) Note that amongst other things, issuing a "use $dbname" command switches off use_server_side_prepare and so totally negates this patch. (uncomment line 61 and 62 of t/utf8.t to see that). I wish I had a complete fix, but the code is quite complex and my C is not that good :-) Cheers, Joost Diepenmaat, Zeekat Softwareontwikkeling.
Subject: perl-dbd-mysql-utf8-blob.patch
diff -rc DBD-mysql-3.0007_2/dbdimp.c DBD-mysql-3.0007_2-patched/dbdimp.c *** DBD-mysql-3.0007_2/dbdimp.c 2006-10-07 14:33:26.000000000 +0200 --- DBD-mysql-3.0007_2-patched/dbdimp.c 2006-10-16 12:56:05.000000000 +0200 *************** *** 266,283 **** case MYSQL_TYPE_DATETIME: case MYSQL_TYPE_NEWDATE: case MYSQL_TYPE_VAR_STRING: ! #if MYSQL_VERSION_ID > GEO_DATATYPE_VERSION ! case MYSQL_TYPE_GEOMETRY: ! #endif #if MYSQL_VERSION_ID > NEW_DATATYPE_VERSION case MYSQL_TYPE_VARCHAR: #endif case MYSQL_TYPE_STRING: case MYSQL_TYPE_BLOB: case MYSQL_TYPE_TINY_BLOB: - case MYSQL_TYPE_TIMESTAMP: /* case MYSQL_TYPE_UNKNOWN: */ ! return MYSQL_TYPE_STRING; default: return MYSQL_TYPE_STRING; /* MySQL can handle all types as strings */ --- 266,285 ---- case MYSQL_TYPE_DATETIME: case MYSQL_TYPE_NEWDATE: case MYSQL_TYPE_VAR_STRING: ! case MYSQL_TYPE_TIMESTAMP: #if MYSQL_VERSION_ID > NEW_DATATYPE_VERSION case MYSQL_TYPE_VARCHAR: #endif case MYSQL_TYPE_STRING: + return MYSQL_TYPE_STRING; + break; + #if MYSQL_VERSION_ID > GEO_DATATYPE_VERSION + case MYSQL_TYPE_GEOMETRY: + #endif case MYSQL_TYPE_BLOB: case MYSQL_TYPE_TINY_BLOB: /* case MYSQL_TYPE_UNKNOWN: */ ! return MYSQL_TYPE_BLOB; default: return MYSQL_TYPE_STRING; /* MySQL can handle all types as strings */ *************** *** 4239,4245 **** case SQL_BINARY: case SQL_VARBINARY: case SQL_LONGVARBINARY: ! buffer_type= MYSQL_TYPE_STRING; break; default: --- 4241,4247 ---- case SQL_BINARY: case SQL_VARBINARY: case SQL_LONGVARBINARY: ! buffer_type= MYSQL_TYPE_BLOB; break; default: diff -rc DBD-mysql-3.0007_2/mysql.xs DBD-mysql-3.0007_2-patched/mysql.xs *** DBD-mysql-3.0007_2/mysql.xs 2006-10-07 14:33:28.000000000 +0200 --- DBD-mysql-3.0007_2-patched/mysql.xs 2006-10-16 12:43:50.000000000 +0200 *************** *** 412,421 **** break; case MYSQL_TYPE_BLOB: ! buffer_type= MYSQL_TYPE_STRING; param_type= SQL_BINARY; break; default: buffer_type= MYSQL_TYPE_STRING; param_type= SQL_VARCHAR; --- 412,427 ---- break; case MYSQL_TYPE_BLOB: ! buffer_type= MYSQL_TYPE_BLOB; param_type= SQL_BINARY; break; + case MYSQL_TYPE_GEOMETRY: + buffer_type= MYSQL_TYPE_BLOB; + param_type= SQL_BINARY; + break; + + default: buffer_type= MYSQL_TYPE_STRING; param_type= SQL_VARCHAR; Only in DBD-mysql-3.0007_2-patched/t: mysql.mtest diff -rc DBD-mysql-3.0007_2/t/utf8.t DBD-mysql-3.0007_2-patched/t/utf8.t *** DBD-mysql-3.0007_2/t/utf8.t 2006-10-07 14:33:30.000000000 +0200 --- DBD-mysql-3.0007_2-patched/t/utf8.t 2006-10-16 14:04:00.000000000 +0200 *************** *** 57,65 **** Test($state or ($dbh = DBI->connect($test_dsn, $test_user, $test_password, {mysql_enable_utf8 => 1}))) or ServerError(); ! # Test($state or ($dbh->do("SET NAMES UTF8"))) ! # or ErrMsg( "Couldn't set connection to UTF-8 mode\n" ); # # Find a possible new table name --- 57,69 ---- Test($state or ($dbh = DBI->connect($test_dsn, $test_user, $test_password, {mysql_enable_utf8 => 1}))) or ServerError(); + + # (my $testdb = $test_dsn) =~ s/^.*:(\w+)$/$1/; + # $state or $dbh->do("use $testdb"); + ! # Test($state or ($dbh->do("SET NAMES 'UTF8'"))) ! # or ErrMsg( "Couldn't set connection to UTF-8 mode\n" ); # # Find a possible new table name *************** *** 73,79 **** # Create a new table; In an ideal world, it'd be more sensible to # make the whole database UTF8... # ! $query = "CREATE TABLE $table (name VARCHAR(64)) CHARACTER SET utf8"; Test($state or $dbh->do($query)) or ErrMsgF("Cannot create table: Error %s.\n", $dbh->errstr); --- 77,83 ---- # Create a new table; In an ideal world, it'd be more sensible to # make the whole database UTF8... # ! $query = "CREATE TABLE $table (name VARCHAR(64) CHARACTER SET utf8, bincol BLOB, shape GEOMETRY)"; Test($state or $dbh->do($query)) or ErrMsgF("Cannot create table: Error %s.\n", $dbh->errstr); *************** *** 84,100 **** my $utf8_str = "\x{0100}dam"; # "Adam" with a macron. my $quoted_utf8_str = "'\x{0100}dam'"; Test( $state or ( $dbh->quote( $utf8_str ) eq $quoted_utf8_str ) ) or ErrMsg( "Failed to retain UTF-8 flag when quoting.\n" ); Test( $state or ( $dbh->{ mysql_enable_utf8 } ) ) or ErrMsg( "mysql_enable_utf8 didn't survive connect()\n" ); ! $query = qq{INSERT INTO $table (name) VALUES (?)}; ! Test( $state or $dbh->do( $query, {}, $utf8_str ) ) or ErrMsgF( "INSERT failed: query $query, error %s.\n", $dbh->errstr ); ! $query = "SELECT name FROM $table LIMIT 1"; Test( $state or ($sth = $dbh->prepare( $query ) ) ) or ErrMsgF( "prepare failed: query $query, error %s.\n", $dbh->errstr ); --- 88,111 ---- my $utf8_str = "\x{0100}dam"; # "Adam" with a macron. my $quoted_utf8_str = "'\x{0100}dam'"; + + my $blob = "\x{c4}\x{80}dam"; # same as utf8_str but not utf8 encoded + my $quoted_blob = "'\x{c4}\x{80}dam'"; + Test( $state or ( $dbh->quote( $utf8_str ) eq $quoted_utf8_str ) ) or ErrMsg( "Failed to retain UTF-8 flag when quoting.\n" ); + Test( $state or ( $dbh->quote( $blob ) eq $quoted_blob ) ) + or ErrMsg( "UTF-8 flag was set when quoting.\n" ); + Test( $state or ( $dbh->{ mysql_enable_utf8 } ) ) or ErrMsg( "mysql_enable_utf8 didn't survive connect()\n" ); ! $query = qq{INSERT INTO $table (name, bincol, shape) VALUES (?,?, GeomFromText('Point(132865 501937)'))}; ! Test( $state or $dbh->do( $query, {}, $utf8_str,$blob ) ) or ErrMsgF( "INSERT failed: query $query, error %s.\n", $dbh->errstr ); ! $query = "SELECT name,bincol,asbinary(shape) FROM $table LIMIT 1"; Test( $state or ($sth = $dbh->prepare( $query ) ) ) or ErrMsgF( "prepare failed: query $query, error %s.\n", $dbh->errstr ); *************** *** 109,114 **** --- 120,139 ---- Test( $state or ($ref->[0] eq $utf8_str) ) or ErrMsgF( "got back '$ref->[0]' instead of '$utf8_str'.\n" ); + if (eval "use Encode;") { + # Check for utf8 flag + Test( $state or (!Encode::is_utf8($ref->[1])) ) + or ErrMsgF( "blob was made utf8!.\n" ); + + Test( $state or (!Encode::is_utf8($ref->[2])) ) + or ErrMsgF( "shape was made utf8!.\n" ); + } + + # Finally, check that we got back bincol correctly. + Test( $state or ($ref->[1] eq $blob) ) + or ErrMsgF( "got back '$ref->[1]' instead of '$blob'.\n" ); + + Test( $state or $sth->finish ) or ErrMsgF( "Cannot finish: %s.\n", $sth->errstr );
Download (untitled) / with headers
text/plain 250b
A patch to fix this with both server-side prepared statements and emulated prepare statements has been committed to the DBD::mysql source tree, and will be included in the next release (probably 4.001). Thanks for the bug report and initial patch!


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.