Show
Ignore:
Timestamp:
29.11.2018 21:23:49 (7 months 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).

Files:
1 modified

Legend:

Unmodified
Added
Removed
  • main/trunk/greenstone2/perllib/gsmysql.pm

    r32595 r32640  
    208208    $params_map->{'db_encoding'} = $self->{'db_encoding'}; 
    209209    $params_map->{'verbosity'} = $self->{'verbosity'}; 
    210      
     210     
    211211    $self->{'db_handle'} = &_get_connection_instance($params_map); # getting singleton (class method) 
    212212    if($self->{'db_handle'}) { 
     
    240240     
    241241    # For proper utf8 support in MySQL, encoding should be 'utf8mb4' as 'utf8' is insufficient 
    242     my $db_enc = "utf8mb4" if $params_map->{'db_encoding'} eq "utf8"; 
     242    my $db_enc = ($params_map->{'db_encoding'} eq "utf8") ? "utf8mb4" : $params_map->{'db_encoding'}; 
    243243 
    244244    # Params for connecting to MySQL 
     
    287287                   RaiseError => 0, # off by default, but being explicit 
    288288                   AutoCommit => $autocommit, 
    289                    mysql_enable_utf8mb4 => 1 # tells MySQL to use (4 byte) UTF-8 for 
    290                    # communication and tells DBD::mysql to use it to decode the data,  
     289                   #mysql_enable_utf8mb4 => 1 # Unrecognised in Windows' Strawberry Perl 5.22. On Ubuntu perl 5.22 
     290                   # if mysql_enable_utf8mb4 was set to 1 *during connect()*, this tells MySQL to use (4 byte) UTF-8 for 
     291                   # communication AND tells DBD::mysql to use it to decode the data,  
    291292                   # see https://stackoverflow.com/questions/46727362/perl-mysql-utf8mb4-issue-possible-bug  
    292293               }); 
     
    322323    # (see https://www.perlmonks.org/?node_id=259456) 
    323324     
    324     #my $stmt = "set NAMES '" . $db_enc . "'"; 
    325     #$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 
    326     #$dbh->{mysql_enable_utf8mb4} = 1; # tells DBD::mysql to decode the data 
     325    # For Strawberry Perl 5.22, need to the following 2 steps here to take care of encoding issues 
     326    # The 2 steps are: set names utf8mb4; AND setting mysql_enable_utf8mb4 = 1 
     327    # Doing this in 2 steps here also works on Ubuntu perl 5.22, 
     328    # but that also supported setting mysql_enable_utf8mb4 to 1 during connect() 
     329    # to do both these steps in one go 
     330    my $stmt = "set NAMES '" . $db_enc . "'"; 
     331    $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 
     332    $dbh->{mysql_enable_utf8mb4} = 1; # tells DBD::mysql to decode the data 
    327333     
    328334    # if we're here, then connection succeeded, store handle