Bug #22123 for DBD-mysql: mysql_enable_utf8 switches on utf8 flag for BLOB & GEOMETRY columns

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.

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 );