Incorrect display of query result from MySQL database

[wind-bsk]

Good afternoon! I have the following problem. In my project esp32-s is querying data in a MySQL database. The query result is data in which there are Latin and Cyrillic characters. In this regard, I use utf-8 table encoding. The problem is that the Cyrillic alphabet is not displayed correctly. In place of each Cyrillic symbol I see a “?” Sign. If the result of the query I output to serial port, I’ll see something like the following: {51} ?????? - ??????. Converting the query result to utf-8 does not help. What encoding does the query result in the database come from? How can I correctly process the result of a request to display it correctly?

[ChuckBell]

The server returns strings in the collation and character set specified. Check you server settings for the default user and the table to ensure they are set correctly. You may also try to use the String class to help with printing characters.

[wind-bsk]

The following settings have been added to the server configuration file:

[client] default-character-set = utf8

[mysqld] init_connect = ‘SET collation_connection = utf8_unicode_ci’ character-set-server = utf8 collation-server = utf8_unicode_ci

I checked the server settings with the request: SHOW VARIABLES LIKE'character% '; Result:

Request: SHOW VARIABLES LIKE 'collation%'; Result: MySQL Server Version 8.0.17

I use a String class variable to get the result of a query. These settings do not give the desired result.

[wind-bsk]

The problem is resolved. The reason was that, by default, the connection was encoded charaster set with latin1. The connection occurred with utf8 encoding, I performed the following query: "SET NAMES utf8 COLLATE utf8_unicode_ci". After that, I immediately began to receive data in the right form.

If possible, in future versions of the library, add the encoding charaster set selection to the server connection settings. Thank!

[ChuckBell]

Added to my list. Thanks for the suggestion!

On May 7, 2020, at 3:10 AM, wind-bsk notifications@github.com wrote:

The problem is resolved. The reason was that, by default, the connection was encoded charaster set with latin1. The connection occurred with utf8 encoding, I performed the following query: SET NAMES utf8 COLLATE utf8_unicode_ci. After that, I immediately began to receive data in the right form.

If possible, in future versions of the library, add the encoding charaster set selection to the server connection settings. Thank!

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/137#issuecomment-625073432, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYCGBEM4TSGCVNQAXY3RQJNHTANCNFSM4M2LWUEQ.