UTF-8 Database connection - inconsistencies to review

[taniwallach]

While reviewing #1125 in noticed that one of the patches is fixing a SET NAMES call to mySQL to set the connection character set being used in lib/WeBWorK/Utils/ListingDB.pm.

It is not clear to me why this file needs to manually force the UTF-8 charset for the connection using SET NAMES. (bin/OPL-update also uses SET NAMES after establishing a UTF-8 connection.)

• When considering these matters we get into the ugly issue that older versions of mySQL supported only the 3-byte utf8 character set and not the full 4-byte set which is called utf8mb4 for mySQL. The need for the control switch under discussion was to provide a mechanism to fall back to the older servers without support for the newer utf8mb4.
• Apparently the use of SET NAMES was added by @heiderich to fix issues with non-ASCII characters in the library databases making trouble:
  • UTF8 Support #712 (comment)
  • Develop uft8 ver3 #800 (comment)
  • Develop uft8 ver3 #800 (comment)
• History
  • @heiderich added a SET NAMES in d24b860#diff-ff30e7d70fdc1c5d4f17cba56a9221e2
  • I changed the line to use utf8mb4 in b840e28#diff-ff30e7d70fdc1c5d4f17cba56a9221e2
  • @mgage added the condition on the control switch in 1ae3087#diff-ff30e7d70fdc1c5d4f17cba56a9221e2
  • It is likely that @mgage and I should have made the choice of charset depend on the control setting, as suggested below.
• I suspect that a better approach would be to add the relevant control settings to the connection parameters in the getDB method of this file, namely mysql_enable_utf8mb4 => 1 and mysql_enable_utf8 => 1, # for older versions of DBD-mysql Perl modules as appears in lib/WeBWorK/DB/Driver/SQL.pm which would also set the connection character set (based on the version of the Perl DBD module).
  • I suspect that the lack of that setting may have been the root issue, and that the SET NAMES could only partially solve the problems, as it does not have the full effect of mysql_enable_utf8 and ``mysql_enable_utf8mb4`.
  • Any change of this sort would need to be tested.
• For now, I doubt there is any damage in leaving this line where it is but maybe it should be modified to select one of the two UTF-8 mySQL character set options depending on the value of ENABLE_UTF8MB4.

if $ce->{ENABLE_UTF8MB4} {
  $dbh->do(qq{SET NAMES 'utf8mb4';});
} else {
  $dbh->do(qq{SET NAMES 'utf8';});
}

---

There are several other files which open their own connections to the database, and which were not modified to provide the UTF-8 support switches as part of their connection parameters. I suspect all should have the relevant connection options added. These are:
- bin/load-OPL-global-statistics.pl
- bin/OPL-update
- bin/test_library_build.pl
- bin/update-OPL-statistics.pl
- lib/WeBWorK/Utils/CourseIntegrityCheck.pm
- lib/WeBWorK/Utils/DBUpgrade.pm
- lib/WeBWorK/Utils/LibraryStats.pm

There seem to be 2 options:

1. Using the approach as in lib/WeBWorK/DB/Driver/SQL.pm and setting both mysql_enable_utf8 and ``mysql_enable_utf8mb4`.
2. Setting only one of them based on the value of ENABLE_UTF8MB4 as done in bin/OPL-update.

[taniwallach]

Note: lib/WeBWorK/DB/Driver/SQL.pm in the new() method actually sets both UTF-8 support switches on:

webwork2/lib/WeBWorK/DB/Driver/SQL.pm

Line 73 in 9f31811

mysql_enable_utf8mb4 => 1,

-

webwork2/lib/WeBWorK/DB/Driver/SQL.pm

Line 74 in 9f31811

mysql_enable_utf8 => 1, # for older versions of DBD-mysql Perl modules

From what I understand from the documentation, both switches do almost the same thing, except for the the fact that the mysql_enable_utf8mb4 setting will try to force the connection character set to utf8mb4 (which is not supported by older versions of mySQL). It seems that setting both on is essentially the same as setting only mysql_enable_utf8mb4 on. See: https://github.com/perl5-dbi/DBD-mysql/blob/2a5e6071b81ab727befc631ec747a149adbf942f/dbdimp.c#L1895 and the following lines.

The current approach seems to be working fine on modern servers. However, something could potentially fail on older servers if the Perl module supported the newer mysql_enable_utf8mb4 while the actual mySQL database supports only the older utf8 character set and not utf8mb4.

As noted above bin/OPL-update makes a choice between the options based on the value of ENABLE_UTF8MB4, and that should avoid issues on older servers. Probably that approach should be used everywhere, including in lib/WeBWorK/DB/Driver/SQL.pm.