DBD::mysql UTF-8 fallacy

Dieser Post wurde aus meiner alten WordPress-Installation importiert. Sollte es Darstellungsprobleme, falsche Links oder fehlende Bilder geben, bitte einfach hier einen Kommentar hinterlassen. Danke.

Todays world is UTF-8, I'm very sure that most software worldwide is UTF-8 today but mySQL on Perl?

I had a big annoying fight much fun with encodings lately and discovered that all our database connections return bytes instead of UTF-8 chars. Some of them run their tables in ISO-8859-1 encoding for historical reasons but the newer ones use UTF-8 and they should return UTF-8 chars. Our DSN strings look like this:

DBI:mysql:database=asia_japan;host=japan.asia.db.company.com;user=japan;password=asia;mysql_enable_utf8=1

The last argument is a driver setting which should force the internal communication between anything calling DBI and the database connection to use UTF-8. The recent encoding trouble is over now but I still couldn't believe that we don't use UTF-8 where we should (and want) and I tried to confirm the myth:

perl -MDBI -le 'my $dbh = DBI->connect("DBI:mysql:database=asia_japan;host=japan.asia.db.company.com;user=japan;password=asia;mysql_enable_utf8=1"); my $t = $dbh->selectrow_array("SELECT firstname FROM CUSTOMERS WHERE id=137"); print DBI::data_string_desc($t);'
UTF8 off, non-ASCII, 15 characters 15 bytes

Surprised? I fetched a Japanese name (and confirmed it to contain only Japanese chars before) and got "15 bytes" without UTF-8 flag.

Another try but passing the mysql_enable_utf8 flag as additional argument this time. This shouldn't make any difference because both places should be the same:

perl -MDBI -le 'my $dbh = DBI->connect("DBI:mysql:database=asia_japan;host=japan.asia.db.company.com;user=japan;password=asia",undef,undef,{mysql_enable_utf8=>1}); my $t = $dbh->selectrow_array("SELECT firstname FROM CUSTOMERS WHERE id=137"); print DBI::data_string_desc($t);'
UTF8 on, non-ASCII, 5 characters 15 bytes

Here we are: 5 characters with correct UTF8-flag, exactly what I want.

What shall I do? Fixing this is one like within our db_connect wrapper used for all modules, but as everything was running wrong for the last years... what will break when fixing this bug?

Maybe I'll recommend to remove the non-working mysql_enable_utf8 argument from all connection strings, fix the connection wrapper to extract them from further DSNs and pass them to DBI->connect as expected. The next database could be set up with a real, working UTF-8 connection and we'ld see any problems before they go to the live systems.

UPDATE: I missed something (thanks to Andreas for pointing that out) by not testing a connection without any mysql_enable_utf8=1. Here is the result:

perl -MDBI -le 'my $dbh = DBI->connect("DBI:mysql:database=asia_japan;host=japan.asia.db.company.com;user=japan;password=asia"); my $t = $dbh->selectrow_array("SELECT firstname FROM CUSTOMERS WHERE id=137"); print DBI::data_string_desc($t);'
UTF8 off, ASCII, 5 characters 5 bytes

5 Japanese chars in 5 bytes? No, not at all. The mysql_enable_utf8 in the dsn actually switches the connection to UTF-8-mode on the mySQL server side. All non-ISO-8859-1 chars are simply converted to "?" by the server.I strongly suggest to replace the dsn-suffix ";mysql_enable_utf8=1" by a explicit "SET NAMES utf8" call after connecting:

perl -MDBI -le 'my $dbh = DBI->connect("DBI:mysql:database=asia_japan;host=japan.asia.db.company.com;user=japan;password=asia"); $dbh->do("SET NAMES utf8"); my $t = $dbh->selectrow_array("SELECT firstname FROM CUSTOMERS WHERE id=137"); print DBI::data_string_desc($t);'
UTF8 off, non-ASCII, 15 characters 15 bytes

Doesn't really work but does the same as ;mysql_enable_utf8 in the DSN string and is much more clear. A simple database setup update or software change may easily switch your believed-to-work DSN suffix into a real driver parameter - and probably start a huge encoding bug wave throughout your application.