Re: DBD::mysql path forward

On Tuesday 12 September 2017 12:27:25 [email protected] wrote:
> To prove fact that other DBI drivers (e.g. Pg or SQLite) had fixed
> similar/same UTF-8 issue as MySQL has and behave Perl-correctly, I
> would provide test cases so you would see difference between Pg,
> SQLite and mysql DBI drivers.

And here is promised script:

=======================================

use strict;
use warnings;
use utf8;
binmode \*STDOUT, ':utf8';
binmode \*STDERR, ':utf8';
use DBI;

sub ords { $_[0] . ' (' . (join ', ', map ord, split //, $_[0]) . ')' }

# DBD::Pg enables Unicode <--> UTF-8 automatically
my $pg_dbh = DBI->connect("dbi:Pg:dbname=postgres", "", "");

# DBD::mysql needs mysql_enable_utf8 and mysql_enable_utf8mb4 for Unicode <--> UTF-8
my $mysql_dbh = DBI->connect("dbi:mysql:dbname=mysql", "root", "", { mysql_enable_utf8 => 1, mysql_enable_utf8mb4 => 1 });
# MySQL server by default uses latin1
# Without switch to utf8mb4, Unicode code points above U+FF cannot be stored (á is below U+FF, č not)
# Another option is to specify own charset in CREATE TABLE
$mysql_dbh->do("SET character_set_database='utf8mb4'");
$mysql_dbh->do("SET character_set_server='utf8mb4'");

# DBD::SQLite needs sqlite_unicode for Unicode <--> UTF-8
my $sqlite_dbh = DBI->connect("dbi:SQLite:dbname=:memory:", "", "", { sqlite_unicode => 1 });

for my $dbh ($pg_dbh, $mysql_dbh, $sqlite_dbh) {
  for my $ins ("\xC3\xA1", "\N{U+C3}\N{U+A1}", "á", "č", "\x{263A}", "\N{U+263A}", "☺") {
    {
      $dbh->do("CREATE TEMPORARY TABLE t(s VARCHAR(10))");
      my $sth = $dbh->prepare("INSERT INTO t(s) VALUES('$ins')");
      $sth->execute();
      my $fetch = $dbh->selectrow_array("SELECT s FROM t");
      print $dbh->{Driver}->{Name} . ' without bind - insert: ' . ords($ins) . '; fetch: ' . ords($fetch) . ' - ' . ($ins eq $fetch ? 'OK' : 'FAIL') . "\n";
      $dbh->do("DROP TABLE t");
    }
    {
      $dbh->do("CREATE TEMPORARY TABLE t(s VARCHAR(10))");
      my $sth = $dbh->prepare("INSERT INTO t(s) VALUES(?)");
      $sth->execute($ins);
      my $fetch = $dbh->selectrow_array("SELECT s FROM t");
      print $dbh->{Driver}->{Name} . ' with bind - insert: ' . ords($ins) . '; fetch: ' . ords($fetch) . ' - ' . ($ins eq $fetch ? 'OK' : 'FAIL') . "\n";
      $dbh->do("DROP TABLE t");
    }
  }
}

=======================================

It is simple "identity" script, which stores value into database,
fetches it back and then compare results.

Look on results with DBD::mysql 4.041, 4.042 and 4.043.
Are all users of DBD::mysql aware of such results?

And specially look at results with DBD::Pg prior to version 3.3.0 and up.
Also DBD::SQLite prior to version 1.43_04.

Basically DBD::Pg (pg_enable_utf8 = -1) and DBD::SQLite
(via sqlite_unicode=1) had exactly same bug as DBD::mysql.