Changeset 32557 for main/trunk

Timestamp:

2018-10-31T18:25:03+13:00 (5 years ago)

Author:

ak19

Message:

1. Bugfix to create_database method: db_name is now a parameter to the method and no longer a member variable of gssql object. 2. UTF8 encodings finally made to work with GreenstoneSQL connections. Doing: 'set NAMES utf8' was insufficient and further causes an error when building a collection with macrons as the utf8 encoding thus specified only supports utf8 chars that take up to 3 bytes to encode. For utf8 chars that can take up to 4 bytes, need to do two things. First, run the mysqld server with minus-minus character_set_server=utf8mb4. Second, EITHER set mysql_enable_utf8mb4 => 1 option when using perl DBI to connect to the db (which in one step tells MySQL to use UTF-8 for communication AND DBD::mysql to decode the data) OR after connection, do BOTH set NAMES 'utf8mb4' (instead of utf8) to tell MySQL to use UTF-8 for communication AND ->{mysql_enable_utf8mb4} = 1 to tell DBD::mysql to decode the data. See ​https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc and ​https://stackoverflow.com/questions/46727362/perl-mysql-utf8mb4-issue-possible-bug

File:

• main/trunk/greenstone2/perllib/gssql.pm (modified) (5 diffs)

main/trunk/greenstone2/perllib/gssql.pm

@@ -110 +110 @@
     my $self= shift (@_);
     my ($params_map) = @_;
-    my $db_enc = $self->{'db_encoding'} || "utf8";
+    #my $db_enc = $self->{'db_encoding'} || "utf8";
+    my $db_enc = "utf8mb4";
 
     # these are the params for connecting to MySQL
@@ -130 +131 @@
                    PrintError => 1, # on by default, but being explicit
                    RaiseError => 0, # off by default, but being explicit
-                   AutoCommit => 1 # on by default, but being explicit
+                   AutoCommit => 1, # on by default, but being explicit
+                   mysql_enable_utf8mb4 => 1 # tells MySQL to use UTF-8 for communication and tells DBD::mysql to decode the data, see https://stackoverflow.com/questions/46727362/perl-mysql-utf8mb4-issue-possible-bug 
                });
 
     if(!$dbh) {
-    # NOTE, despite handle dbh being undefined, error code will be in DBI->err
+    # NOTE, despite handle dbh being undefined, error code will be in DBI->err (note caps)
     return 0;   
     }
@@ -141 +143 @@
     # https://dev.mysql.com/doc/refman/5.7/en/charset.html
     # https://dev.mysql.com/doc/refman/5.7/en/charset-conversion.html
-    # Setting the encoding at db server level.
-    # Not sure if this command is mysql specific:
-    my $stmt = "set NAMES '" . $db_enc . "'";
-    $dbh->do($stmt) || warn("Unable to set charset encoding at db server level to: " . $db_enc . "\n");
+    # Setting the encoding at db server level: $dbh->do("set NAMES '" . $db_enc . "'");
+    # HOWEVER:
+    # It turned out insufficient setting the encoding to utf8, as that only supports utf8 chars that
+    # need up to 3 bytes. We may need up to 4 bytes per utf8 character, e.g. chars with macron,
+    # and for that, we need the encoding to be set to utf8mb4.
+    # To set up a MySQL db to use utf8mb4 requires configuration on the server side too.
+    # https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc
+    # https://stackoverflow.com/questions/46727362/perl-mysql-utf8mb4-issue-possible-bug
+    # To set up the db for utf8mb4, therefore, 
+    # the MySQL server needs to be configured for that char encoding by running the server as:
+    # mysql-5.7.23-linux-glibc2.12-x86_64/bin>./mysqld_safe --datadir=/Scratch/ak19/mysql/data --character_set_server=utf8mb4
+    # AND when connecting to the server, we can can either set mysql_enable_utf8mb4 => 1
+    # as a connection option
+    # OR we need to do both "set NAMES utf8mb4" AND "$dbh->{mysql_enable_utf8mb4} = 1;" after connecting
+    #
+    # Search results for DBI Set Names imply the "SET NAMES '<enc>'" command is mysql specific too,
+    # so setting the mysql specific option during connection above as "mysql_enable_utf8mb4 => 1"
+    # is no more objectionable. It has the advantage of cutting out the 2 extra lines of doing
+    # set NAMES '<enc>' and $dbh->{mysql_enable_utf8mb4} = 1 here.
+    # These lines may be preferred if more db_driver options are to be supported in future:
+    # then a separate method called set_db_encoding($enc) can work out what db_driver we're using
+    # and if mysql and enc=utfy, then it can do the following whereas it will issue other do stmts
+    # for other db_drivers, 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
     
     # if we're here, then connection succeeded, store handle
@@ -248 +273 @@
 sub create_db {
     my $self= shift (@_);
-    my $db_name = $self->{'db_name'};
+    my ($db_name) = @_;
     my $dbh = $self->{'db_handle'};
     
@@ -294 +319 @@
     # drop table <tablename>
     my $table = $self->get_metadata_table_name();
-    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
+    if($self->table_exists($table)) {
+    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
+    }
     $table = $self->get_fulltext_table_name();
-    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
+    if($self->table_exists($table)) {
+    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
+    }
 }