"Incorrect string value" when inserting non-ASCII into MySQL database using ADODB in MS Access (VBA), but retry works

Question

After finding a bug in MySQL ODBC 5.3.6, I have another problem which also happens using MySQL ODBC 5.3.4.

I have an MS Access application (Office 2016 ProPlus 32-bit) that uses ADODB with MySQL ODBC (5.3.4 32-bit) to interface to a local MySQL database server (5.7.16 64-bit) on a Windows 10 Pro 64-bit computer. Inserting non-ASCII characters in the MySQL database fails with the error "Incorrect string value" but if I try the same statement again, the SQL statement is correctly executed and the correct value is inserted into the database table!

To isolate the problem, I created the following table:

  CREATE TABLE test2 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) CHARACTER SET utf8mb4,
    PRIMARY KEY (id));

And a test MS Access database containing the following VBA code (and a reference to the Microsoft ActiveX Data Objects 6.1 library):

  Public Function dbTestIt2() as Long

  Dim dbConn As New ADODB.Connection
  Dim dbCmd As New ADODB.Command
  Dim dbParams As New ADODB.Parameter
  Dim l As Long

      dbConn.ConnectionString = "Driver={MySQL ODBC 5.3 Unicode Driver};option=3;database=xxx;user=yyy;password=zzz;"
      dbConn.Open
      With dbCmd
          .ActiveConnection = dbConn
          .CommandType = adCmdText
          .CommandText = " INSERT INTO test2 (name) VALUES (?);"
          dbParams.Type = adVarChar
          dbParams.Size = 100
          dbParams.Value = "abcdèfgh"
          dbParams.Direction = adParamInput
          .Parameters.Append dbParams
          .Execute l, , adExecuteNoRecords
      End With
      dbConn.Close

      dbTestIt2 = l

  End Function

Relevant lines in my.ini:

  [client]
  default-character-set=utf8mb4
  [mysql]
  default-character-set=utf8mb4
  [mysqld]
  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci

Here are the test results:

1. Execution stops at the .Execute statement with the error 80004005: [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.16-log]Incorrect string value: '\xE8gh' for column 'name' at row 1;
2. If I continue debugging (F8), the SQL statement is correctly executed and the correct value is inserted into the database table;
3. If I use the "ANSI" ODBC driver instead of the "Unicode" driver, this test succeeds the first time, but then more complex (e.g. Chinese) non-ASCII characters get replaced by question marks;
4. I believe that VBA internally uses Unicode but MySQL does not support UTF16 for clients and specifying "charset=utf8mb4" in the ODBC connection string or execute "SET CHARACTER SET utf8mb4" first does not help and "SET NAMES utf8mb4" is not supported;
5. If I use INSERT IGNORE instead of INSERT, the first execution seems to succeed, but the value is actually truncated at the "è" (so only "abcd" is inserted);
6. It also happens with MySQL ODBC 5.3.6

Am I doing anything wrong or is this another bug in the MySQL ODBC driver?

Answer 1

The statement ...

dbParams.Type = adVarChar

... is telling the ADODB.Parameter object that its value will be a string of single-byte characters. Those single-byte characters will be passed to the ODBC driver, which will in turn pass them to the MySQL database engine. If the database engine is expecting UTF-8 characters then an error will occur because 0xE8 (è in many "latin-1"-type code pages like Windows-1252) is not a valid UTF-8 byte sequence.

Changing the above statement to ...

dbParams.Type = adVarWChar

... tells the ADODB.Parameter object that its value will be a string of multi-byte ("Wide") characters. When assigning the value using a VBA string literal ...

dbParams.Value = "dèf"

... ADODB will convert the string from its single-byte representation (based on the current Windows locale) to Unicode, and pass that to the ODBC driver. The ODBC driver is able to "repackage" the string from raw Unicode (U+0064 U+00E8 U+0066) into UTF-8 encoding (0x64 0xC3 0xA8 0x66) and pass that to the database engine.