MySQL Bugs: #107698: MySQL ODBC 8.0 Unicode Driver Fails to Write 4-Byte Emoji to Database

Bug #107698	MySQL ODBC 8.0 Unicode Driver Fails to Write 4-Byte Emoji to Database
Submitted:	29 Jun 2022 9:22	Modified:	12 Jul 2022 19:51
Reporter:	Robin Hickmott	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.29	OS:	Windows
Assigned to:		CPU Architecture:	x86
Tags:	4-byte, 8.0, connector, driver, emoji, MySQL, ODBC, Unicode

Description:
This is an extension to this bug here

https://bugs.mysql.com/bug.php?id=104346

Using the ODBC Unicode Driver I can successfully read 4 Byte Strings from the database (including emoji) using 8.0.28 and above however writing back to the database results in the following error.

System.Data.Odbc.OdbcException (0x80131937): ERROR [HY000] [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.29]Conversion from collation utf8_general_ci into utf8mb4_unicode_ci impossible for parameter

Issue is there in 8.0.28 and 8.0.29

There seems to be no way that I can find of getting the connector to use UTF8MB4 instead of UTF8 (which is depreciated now anyway)

How to repeat:
Create a Table

CREATE TABLE `games` (
  `id` int UNSIGNED NOT NULL COMMENT 'THE GAME ID',
  `name` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HOLDS DETAILS OF ACTIVE EVENT GAMES';

INSERT INTO `games` (`id`, `name`) VALUES (NULL, 'Testing 123 🙃');

Using .NET (Going to use VB this example sorry) and the DSN

"DRIVER={MySQL ODBC 8.0 Unicode Driver}; SERVER=127.0.0.1; DATABASE=test; UID=test; PASSWORD=test; PORT=3306; CHARSET=utf8mb4; AUTO_RECONNECT=1; MULTI_STATEMENTS=1;"

'-- Read Value
Using mRetrieve As New OdbcCommand(" SELECT `name` FROM `games` WHERE `id` = 1 ", m_DbConnection)

	'-- Read Value
	Dim tName As String = ""
	Using mReader As OdbcDataReader = mRetrieve.ExecuteReader
	    If mReader IsNot Nothing AndAlso Not mReader.IsClosed AndAlso mReader.HasRows Then
		tName = mReader.Item("name")
	    End If
	End Using

	'-- Update
	Using mWriter As New OdbcCommand(" UPDATE `games` SET `name` = ? WHERE `id` = 1 ", m_DbConnection)
	    mWriter.Parameters.AddWithValue(":NAME", tName)
	    mWriter.ExecuteNonQuery()
	End Using

End Using

Suggested fix:
MySQL ODBC connector should default to at least utf8_general_ci or allow this parameter to be set.

Sorry fix should be to default to at least utf8mb4_general_ci 😉 not that it effects the above.

Hello Robin,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

Bug #106251 marked as duplicate of this one.

Posted by developer:
 
There's a related change in the upcoming 8.0.30 release and here's the release note for it:

  The driver's default character set changed to utf8mb4.
  Previously it defaulted to utf8, which is an alias to utf8mb3.

The developers report that the above change fixes this bug.

Thanks Robin for the bug report, a proposed update to the above is:

  The driver's default character set changed to utf8mb4.
  Previously it defaulted to utf8, which is an alias to the
  deprecated utf8mb3. Using utf8mb3 could cause problems, like
  incorrectly inserting and selecting emojis.

Setting status to closed and referencing this bug number in the 
above utf8mb4's release note entry.

Posted by developer:
 
This bug should be fixed in the version 8.0.30.
ODBC Driver of this version is using utf8mb4 as the default character set.