MySQL 3.23.49 and problems with accented characters in WHERE clauses

Hello,

I am working with a very old MySQL server (version 3.23.49).

For years I used MySQL-Front and everything worked correctly. Recently I switched to HeidiSQL, but I noticed that some queries no longer return any results when the search criteria contains accented characters such as é.

For example, queries with a WHERE clause containing accented values do not return the expected rows, even though the data exists in the table.

Here is a concrete example:

SELECT * 
FROM type_activite 
WHERE libe_type_acti = 'Récolte';

This query worked correctly in MySQL-Front, but in HeidiSQL it returns no results.

I also tried running:

SET NAMES latin1;

but MySQL returns the following error:

You have an error in your SQL syntax near 'NAMES LATIN1' at line 1

I understand that SET NAMES may not be supported by such an old MySQL version.

The command:

SHOW VARIABLES LIKE '%char%';

returns:

Variable_name | Value
character_set | latin1
character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis ...

So the server appears to be configured for Latin1.

Has anyone successfully used HeidiSQL with MySQL 3.23.x?

Is there a way to force HeidiSQL to use the correct character encoding for such an old server, or should I expect compatibility issues because MySQL 3.23 predates modern character set negotiation?

Any advice would be appreciated.

Thank you.

Well there are some issues with such old MySQL versions in HeidiSQL. But I can query accented values without problem. This probably depends on the collation of the table?

Here's the output of the other query:

SHOW VARIABLES LIKE '%char%';

We can see here that there is an issue whith the value read by heidiSQL, maybe beacause of some wrong charsets ?

The data in my table is correct, it should be "Récolte", but the byte for the é might be interpreted differently.

The 223 ASCII code corresponds to the character é in latin1.

Since this database was created a long time ago, I suspect that the data may have originally been stored using a Windows-1252 code page, while HeidiSQL is interpreting it differently.

Is there a way to force the client encoding to Windows-1252 or Latin1 when connecting to a MySQL 3.23 server?

Which HeidiSQL version is it on your side?

Can you please post the result of SHOW CREATE TABLE libe_type_acti here, as my own table creation has no issues.

I'm using HeidiSQL 12.17.0.7270

Do you mean SHOW CREATE TABLE type_activite; ? Here it is :

CREATE TABLE `type_activite` (
  `code_type_acti` char(8) default NULL,
  `libe_type_acti` char(30) default NULL,
  `flag_gest_lot_reco` char(1) default NULL,
  `flag_sais_quan` char(1) default NULL,
  `flag_prod` char(1) default NULL,
  `flag_reco` char(1) default NULL,
  `flag_pese` char(1) default 'N',
  `flag_paye` char(1) default NULL,
  `coul_type_acti` int(11) default NULL,
  `flag_trai` char(1) default NULL,
  `code_sect_anal` char(8) default NULL,
  `flag_affi` char(1) default 'N',
  `flag_suiv_prod` char(1) default 'O',
  `coef_poly` int(11) unsigned default '1',
  `code_sect_defa` char(8) default NULL,
  `indi_sais_lot_cult` char(1) default 'N',
  `indi_etap_cult` char(1) NOT NULL default 'N',
  `flag_acti_corr` char(1) NOT NULL default 'N',
  UNIQUE KEY `ind_type_acti` (`code_type_acti`)
) TYPE=MyISAM;

Ok... it's a bug in HeidiSQL's internal "IsUnicode" flag on the connection object.

Even though my log says it cannot enable Unicode, and yours is likely the same:

/* Changing character set from latin1 to utf8mb4 */
/* Characterset: latin1 */

... the internal IsUnicode flag is still true, due to the mentioned bug.

When you insert rows within HeidiSQL, data is posted as UTF-8, and the results are also expected to be UTF-8 encoded. So this is not a problem.

What is indeed a problem: a client with a pure latin1 connection did insert rows and you select them with an UTF-8 forced client like HeidiSQL. That is happening in your case. The client which inserted your row did that long time ago, and was latin1 based.

Now on the HeidiSQL side I am hesitating to change that faulty internal flag, as this is likely not what other people expect then.

What I could do is add a "force downgrade charset on old servers" opt-in checkbox for such situations. But I feel that would be quite some effort for servers which should anyway be upgraded.

Ah, that "force downgrade charset on old servers" could also just be a registry hack without a checkbox. I guess in that case it would be hidden enough so users do not get confused by it.

Thanks a lot for your time and help.

This option wouldn't be used by a lot of users lol.

Have a good day.

I have added a new session setting: "ForceUnicode". This is by default on/true. You may disable it:

1. update heidisql to latest build
2. start regedit.exe when HeidiSQL is not running
3. go to HKEY_CURRENT_USER\Software\HeidiSQL\Servers\YourOldServer\
4. add a new dword value ForceUnicode = 0.

Note: this is quite experimental. It may easily break server communication in other places.

I downloaded the latest build (12.17.0.7302) and added the dword in the registry editor but I still have the � that appears instead of some "é".

Here's the result of this query : SELECT libe_type_acti, SUBSTRING(libe_type_acti, 2, 1), ORD( SUBSTRING(libe_type_acti, 2, 1) ), ASCII( SUBSTRING(libe_type_acti, 2, 1) ) FROM type_activite WHERE libe_type_acti LIKE 'R%colte'

And in MySQL-Front :

Here are my logs. I can't remember if it was already like that before adding the dword :

The logs do not mention your overridden ForceUnicode flag. It should say so, after the "Changing character set from ...": "ForceUnicode disabled ...".

Maybe a typo in the registry dword? Did you create it in the session key?

I see the registry screenshot now - looks correct, at least if "Mysql" is the session with the v3.23 server.

Did the update succeed, can you see the build number 7302 in the title bar of HeidiSQL?

Yes "Mysql" is the 3.23 session and I do have the update :

Registry :

That's weird, maybe I should restart my computer.

Ok, please run this query and watch out if it returns 233 as well. It is probably 195, which is the first byte of the two-byte Unicode char é - that would mean the charset is still Unicode, not Latin1:

SELECT ORD('é');

After the HeidiSQL update, you added the reg dword. Did you then restart HeidiSQL?

And, on the status bar, click on the panel which says "MySQL 3.23.x", and see if "Unicode enabled" says "no". (it should with ForceUnicode=0)

Yes I restarted HeidiSQL many times.

Unicode enabled says "yes"...

The reg dword is still there yet.

Here's the value I have when exporting to a .reg file : "ForceUnicode"=dword:00000000

That looks all correct, but still that ForceUnicode=0 is not read by HeidiSQL. Strange. I'm running out of ideas right now.

I just decided to give that ForceUnicode setting its own checkbox in the Advanced tab.

Please update once again, and verify that checkbox is not checked. It should be unchecked with your current registry value, but that was point of the culprit here, so we can see it now how HeidiSQL sees it.

The checkbox was unchecked and now everything works just fine.

Thanks again for such a quick support.

That leaves me a bit with scratching my head why it did not work before.

However, glad this supports legacy and legendary servers from the 90's :)

Thank you for providing quick feedback and help debugging!