- Timestamp:
- 2018-10-31T18:25:03+13:00 (5 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/greenstone2/perllib/gssql.pm
r32555 r32557 110 110 my $self= shift (@_); 111 111 my ($params_map) = @_; 112 my $db_enc = $self->{'db_encoding'} || "utf8"; 112 #my $db_enc = $self->{'db_encoding'} || "utf8"; 113 my $db_enc = "utf8mb4"; 113 114 114 115 # these are the params for connecting to MySQL … … 130 131 PrintError => 1, # on by default, but being explicit 131 132 RaiseError => 0, # off by default, but being explicit 132 AutoCommit => 1 # on by default, but being explicit 133 AutoCommit => 1, # on by default, but being explicit 134 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 133 135 }); 134 136 135 137 if(!$dbh) { 136 # NOTE, despite handle dbh being undefined, error code will be in DBI->err 138 # NOTE, despite handle dbh being undefined, error code will be in DBI->err (note caps) 137 139 return 0; 138 140 } … … 141 143 # https://dev.mysql.com/doc/refman/5.7/en/charset.html 142 144 # https://dev.mysql.com/doc/refman/5.7/en/charset-conversion.html 143 # Setting the encoding at db server level. 144 # Not sure if this command is mysql specific: 145 my $stmt = "set NAMES '" . $db_enc . "'"; 146 $dbh->do($stmt) || warn("Unable to set charset encoding at db server level to: " . $db_enc . "\n"); 145 # Setting the encoding at db server level: $dbh->do("set NAMES '" . $db_enc . "'"); 146 # HOWEVER: 147 # It turned out insufficient setting the encoding to utf8, as that only supports utf8 chars that 148 # need up to 3 bytes. We may need up to 4 bytes per utf8 character, e.g. chars with macron, 149 # and for that, we need the encoding to be set to utf8mb4. 150 # To set up a MySQL db to use utf8mb4 requires configuration on the server side too. 151 # https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc 152 # https://stackoverflow.com/questions/46727362/perl-mysql-utf8mb4-issue-possible-bug 153 # To set up the db for utf8mb4, therefore, 154 # the MySQL server needs to be configured for that char encoding by running the server as: 155 # mysql-5.7.23-linux-glibc2.12-x86_64/bin>./mysqld_safe --datadir=/Scratch/ak19/mysql/data --character_set_server=utf8mb4 156 # AND when connecting to the server, we can can either set mysql_enable_utf8mb4 => 1 157 # as a connection option 158 # OR we need to do both "set NAMES utf8mb4" AND "$dbh->{mysql_enable_utf8mb4} = 1;" after connecting 159 # 160 # Search results for DBI Set Names imply the "SET NAMES '<enc>'" command is mysql specific too, 161 # so setting the mysql specific option during connection above as "mysql_enable_utf8mb4 => 1" 162 # is no more objectionable. It has the advantage of cutting out the 2 extra lines of doing 163 # set NAMES '<enc>' and $dbh->{mysql_enable_utf8mb4} = 1 here. 164 # These lines may be preferred if more db_driver options are to be supported in future: 165 # then a separate method called set_db_encoding($enc) can work out what db_driver we're using 166 # and if mysql and enc=utfy, then it can do the following whereas it will issue other do stmts 167 # for other db_drivers, see https://www.perlmonks.org/?node_id=259456: 168 169 #my $stmt = "set NAMES '" . $db_enc . "'"; 170 #$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 171 #$dbh->{mysql_enable_utf8mb4} = 1; # tells DBD::mysql to decode the data 147 172 148 173 # if we're here, then connection succeeded, store handle … … 248 273 sub create_db { 249 274 my $self= shift (@_); 250 my $db_name = $self->{'db_name'};275 my ($db_name) = @_; 251 276 my $dbh = $self->{'db_handle'}; 252 277 … … 294 319 # drop table <tablename> 295 320 my $table = $self->get_metadata_table_name(); 296 $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 321 if($self->table_exists($table)) { 322 $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 323 } 297 324 $table = $self->get_fulltext_table_name(); 298 $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 325 if($self->table_exists($table)) { 326 $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table"); 327 } 299 328 } 300 329
Note:
See TracChangeset
for help on using the changeset viewer.