Changeset 32640 for main/trunk/greenstone2/perllib/gsmysql.pm

Timestamp:

2018-11-29T21:23:49+13:00 (5 years ago)

Author:

ak19

Message:

Important changes (and commented out debugging statements) to get charset encodings (utf8 or specifically utf8mb4 in perl mysql case) to work with GS SQL Plugs on Windows. First, Strawberry Perl 5.22 was required and hence committed before this since Strawberry Perl 5.18 came with older mysql DBD/DBI packages that didn't decode utf8 content in the database when content was retrieved. Strawberry Perl 5.22 came with newer versions of DBD and DBI that do this automatically, as has been the case in Linux testing where Ubuntu had Perl 5.22 with sufficiently new versions of the DBI/DBD mysql perl packages. The newer Perl and specifically the newer MySQL DBD/DBI packages required some important changes to the gsmysql.pm code in the way charset encoding stuff is configured. This should work on Linux too, as that already allowed 2 ways to configure DB encoding stuff. I chose the single-line version on Linux, no longer supported with the DBI/DBD upgrade that comes with our new Strawberry Perl 5.22, so opting for the 2 line version to setup the DB encoding stuff which works on Windows and should continue to work on Linux too (where I had tested it before settling on the single-line variant).

File:

• main/trunk/greenstone2/perllib/gsmysql.pm (modified) (4 diffs)

main/trunk/greenstone2/perllib/gsmysql.pm

@@ -208 +208 @@
     $params_map->{'db_encoding'} = $self->{'db_encoding'};
     $params_map->{'verbosity'} = $self->{'verbosity'};
-    
+    
     $self->{'db_handle'} = &_get_connection_instance($params_map); # getting singleton (class method)
     if($self->{'db_handle'}) {
@@ -240 +240 @@
     
     # For proper utf8 support in MySQL, encoding should be 'utf8mb4' as 'utf8' is insufficient
-    my $db_enc = "utf8mb4" if $params_map->{'db_encoding'} eq "utf8";
+    my $db_enc = ($params_map->{'db_encoding'} eq "utf8") ? "utf8mb4" : $params_map->{'db_encoding'};
 
     # Params for connecting to MySQL
@@ -287 +287 @@
                    RaiseError => 0, # off by default, but being explicit
                    AutoCommit => $autocommit,
-                   mysql_enable_utf8mb4 => 1 # tells MySQL to use (4 byte) UTF-8 for
-                   # communication and tells DBD::mysql to use it to decode the data, 
+                   #mysql_enable_utf8mb4 => 1 # Unrecognised in Windows' Strawberry Perl 5.22. On Ubuntu perl 5.22
+                   # if mysql_enable_utf8mb4 was set to 1 *during connect()*, this tells MySQL to use (4 byte) UTF-8 for
+                   # communication AND tells DBD::mysql to use it to decode the data, 
                    # see https://stackoverflow.com/questions/46727362/perl-mysql-utf8mb4-issue-possible-bug 
                });
@@ -322 +323 @@
     # (see https://www.perlmonks.org/?node_id=259456)
     
-    #my $stmt = "set NAMES '" . $db_enc . "'";
-    #$dbh->do($stmt) || warn("Unable to set charset encoding at db server level to: " . $db_enc . "\n"); # tells MySQL to use UTF-8 for communication
-    #$dbh->{mysql_enable_utf8mb4} = 1; # tells DBD::mysql to decode the data
+    # For Strawberry Perl 5.22, need to the following 2 steps here to take care of encoding issues
+    # The 2 steps are: set names utf8mb4; AND setting mysql_enable_utf8mb4 = 1
+    # Doing this in 2 steps here also works on Ubuntu perl 5.22,
+    # but that also supported setting mysql_enable_utf8mb4 to 1 during connect()
+    # to do both these steps in one go
+    my $stmt = "set NAMES '" . $db_enc . "'";
+    $dbh->do($stmt) || warn("Unable to set charset encoding at db server level to: " . $db_enc . "\n"); # tells MySQL to use UTF-8 for communication
+    $dbh->{mysql_enable_utf8mb4} = 1; # tells DBD::mysql to decode the data
     
     # if we're here, then connection succeeded, store handle