MySQL Bugs: #117264: Arabic font rendering issue in Crystal Reports after updating to MySQL Connector/ODBC 9.2.0"

Bug #117264	Arabic font rendering issue in Crystal Reports after updating to MySQL Connector/ODBC 9.2.0"
Submitted:	22 Jan 9:48	Modified:	29 Jan 11:27
Reporter:	Mohammad Al Sayegh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	9.2	OS:	Windows
Assigned to:	Rafal Somla	CPU Architecture:	x86
Tags:	MySQL Connector/ODBC

Description:
After updating the MySQL Connector/ODBC driver from version 8.4.0 to 9.2.0, I encountered an issue with Arabic font rendering in Crystal Reports for a VB.NET 2022 application. The Arabic text, which previously displayed correctly, now appears garbled or incorrectly formatted shows like that "????".

Additional Information:

The issue did not occur with MySQL Connector/ODBC version 8.4.0.
The problem persists even after updating both NuGet packages and the ODBC driver.
The system's regional settings are configured to support Arabic.
The database and tables are set to use utf8 or utf8mb4 character sets.
Please investigate this issue and provide a solution or workaround.

How to repeat:
Install MySQL Connector/ODBC 9.2.0:

Download and install the MySQL Connector/ODBC 9.2.0 driver from the official MySQL website.
Set Up ODBC Data Source:

Configure an ODBC data source using the new driver, ensuring it connects to a MySQL database that contains Arabic text.
Open VB.NET 2022 Project:

Open a VB.NET 2022 project that uses Crystal Reports to generate reports.
Update NuGet Packages:

Ensure all relevant NuGet packages are updated to their latest versions.
Generate Crystal Report:

Create or open a Crystal Report that includes Arabic text from the MySQL database.
Observe the Report:

Run the application and generate the report. Observe the rendering of the Arabic text in the Crystal Report.
Expected Result: Arabic text should display correctly in the Crystal Report.

Actual Result: Arabic text appears garbled or incorrectly formatted.

Suggested fix:
Investigate Character Set Handling: Review and adjust the character set handling in the MySQL Connector/ODBC 9.2.0 driver to ensure it supports Arabic text correctly. This might involve ensuring that the charset parameter is set to utf8 or utf8mb4.

Revert Changes: If the issue is related to recent changes in the driver, consider reverting those changes or providing an option to use the previous character set handling method.

Provide a Patch: Release a patch or update for the MySQL Connector/ODBC 9.2.0 driver that addresses the Arabic font rendering issue in Crystal Reports.

Documentation Update: Update the documentation to include any necessary configuration steps or settings required to support Arabic text with the new driver version.

Hi Mr. Sayegh,

Thank you for your bug report.

If you use MySQL Server 9.2, do you still get the same problem ?????

Thanks in advance.

If you use MySQL Server 9.2, do you still get the same problem ?????
Not tested on Mysql server 9.2

HI Mr. Sayegh,

Please do test ...... 

You do not have to move your production to server version 9.2, but you should test that option.

Dear Mr. Sayegh,

In addition to the questions asked by Sinisa we need the following details from you:

Q.1: Are you using ANSI or Unicode version of MySQL Connector/ODBC 9.2?

Q.2: Is CHARSET option set for ODBC Data Source or the connection string?
   
NOTE: Q.2 is about the ODBC driver options, you already mentioned the server
      side was using utf8 or utf8mb4 character sets.

Q.1: Are you using ANSI or Unicode version of MySQL Connector/ODBC 9.2?
A.1: ANSI 

Q.2: Is CHARSET option set for ODBC Data Source or the connection
string?
A.2: The CHARSET option is set to Nothing (no selection). 

Note: The CHARSET option for the version 8 ODBC Connector is not selectable. However, the Arabic language is working fine.

Thank you for your answers.

It looks like the ANSI driver might not do the proper conversion of the string data without specifying the character set expected by the client.

Can you try setting CHARSET option to utf8mb4?

This can be done in two ways:

 - In GUI Dialog prompt by clicking "Details >>" button and
   selecting "utf8mb4" in the "Character Set" list.

 - By adding "CHARSET=utf8mb4" to the connection string in your
   application.

i tried this option
In GUI Dialog prompt by clicking "Details >>" button and
   selecting "utf8mb4" in the "Character Set" list.

Arabic shows like this : µط¨ط§ط ط§ظ„ط³ط§ظ„ظ… but before shows like that ????

After further investigation, I believe the issue lies with Crystal Reports and the new connector. The problem occurs specifically when there is both a main report and a sub-report.
    For standard reports (without sub-reports), Arabic text displays correctly.
    However, when a sub-report is present, Arabic text appears incorrectly only in the sub-report.

It’s worth noting that this issue does not occur with the old connector, where Arabic text functions perfectly, even with sub-reports.

The MySQL ODBC driver 9.X had changes related to the string data conversions. In the older versions such as 8.0 the default settings in ODBC driver produced data conversions that coincidentally matched the expectation of your application and therefore it did not require specifying CHARSET.

The situation with the sub-report you are describing is a bit unusual. Therefore, we will try to make an educated guess based on the facts known to us at the present moment.

If the data for the sub-report is different than for the main report then probably they are getting it from different sources. This could still be the same MySQL database and the same ODBC driver, but for one report the driver has CHARSET option set to utf8mb4 and for the other one the option is not specified.

Can you investigate if this is the case?

Thank you for your detailed response and suggestions.

Regarding the points raised:

Data Consistency: The data for the sub-report is indeed the same as for the main report. Both reports are retrieving data from identical sources, and there are no differences in the datasets themselves.

CHARSET Option: I have already ensured that the UTF8MB4 option is disabled in both the connection settings and the code, and there is no discrepancy in how the CHARSET option is applied.

ODBC Connection Issue: I am facing difficulties connecting to the database through Crystal Reports using "Database Expert" with the Connector/ODBC 9.2.0. Every time I attempt to create a new connection, it either fails outright or causes Visual Studio to crash. I have made several attempts but have been unable to resolve the issue.

I will continue investigating the possible causes on my end and would appreciate any further insights you might have to help resolve these challenges.

HI Mr. Sayegh,

Would it be possible for your to run ODBC in debug mode and upload the debug log from your system???

trace log uploaded

Posted by developer:
 
Thank you for the ODBC trace log.
After reviewing it I noticed that it mostly contains Unicode version of function calls such as SQLExecDirectW() instead of ANSI alternative SQLExecDirectA().
Also, the data being retrieved as WIDE char (SQL_C_WCHAR) instead of the regular char (SQL_CHAR):

MonoSys   6d78-4798	ENTER SQLBindCol 
	HSTMT              0x000001ECD0D60600
	UWORD              9 
	SWORD             -8 <SQL_C_WCHAR>
	PTR                0x000001ECDB020330
	SQLLEN             102
	SQLLEN *           0x000001ECDB1DC170

This means that the end application consumes data as UTF16 or UCS2 instead of UTF8MB4.

What happens is this:

The ODBC data source requests the use of ANSI version of the driver (myodbc8a.dll).
However, the application makes calls to Unicode versions of the function (the one with `W` at the end of its name).
The ODBC Driver Manager wraps the UNICODE calls to ANSI calls and does the data conversions UTF16 <--> UTF8MB4 to emulate the work of a UNICODE driver.

Using UNICODE driver would eliminate the need for data conversion between the driver and the client application.
Can you try using MySQL ODBC Unicode Driver instead of ANSI?

Regarding the crashes described in one of previous messages:

We received a number of similar reports of sudden crashes and found out the crashes were caused by a corrupted install of runtime libraries inside Visual C++ Redistributable package 2015-2022 version 14.36. Updating the VC++ package to version 14.40 or newer should resolve the problem with the crashes. Make sure that the redistributable package for x64 architecture is updated.

I switched to the UNICODE driver, tested it successfully, and created a new report that connected to ODBC without any issues. The Arabic font displays correctly in both the main and sub-reports as well.  

Thank you all for your great support! I truly appreciate it.

i want to update my last comment:

I encountered an issue with MySQL ODBC Driver versions 9.1 and 9.2—Crystal Reports closes when I try to connect to ODBC in design mode. Despite this, both versions correctly display Arabic in running reports. Due to this limitation, I am using MySQL Connector 9.0. UNICODE driver  

Note: I am using the latest update of Crystal Reports Service Pack 13.35 (both 64-bit and 32-bit).

Thank you all for your great support! I truly appreciate it.

HI Mr. Sayegh,

You are truly welcome.