I'm almost certain this has come up before but I couldn't find anything regarding it.
My problem is that I've a database and a database server (and every table and column inside) set to latin1/latin1_general_ci.
Curiously when I connect with a client (for example HeidiSQL) client specific variables regarding character sets are set to utf8mb4
, even more curious: äüöß are shown correctly.
When I execute SET NAMES latin1;
and check again, the variables are set to latin1
, as they should be, but the Umlaute are now displayed as question marks. This is also true if they where inserted AFTER SET NAMES latin1;
was executed.
Another database server is completely set to utf8, except the database my program works with, which is again set to latin1. If I execute SET NAMES latin1;
there it works as expected.
Could someone explain/tell me what is going on here? What am I doing wrong?
The variables
I'm talking about are:
- character_set_client [Global/Default: latin1]
- character_set_connection [Global/Default: latin1]
- character_set_database [Global/Default: latin1]
- character_set_results [Global/Default: latin1]
- character_set_server [Global/Default: latin1]
- collation_connection [Global/Default: latin1_general_ci]
After some toying around it seems like character_set_results
is the problematic one here. Setting it to latin1, while leaving the other ones at utf8mb4, results in question marks being displayed. Still no idea why this happens.
edit
Result of SHOW CREATE TABLE adressen
:
CREATE TABLE `adressen` (
`ID` int(9) unsigned NOT NULL AUTO_INCREMENT,
`Strasse` longtext,
`Strasse2` longtext,
`Hausnummer` longtext,
`Postleitzahl` longtext,
`Ort` longtext NOT NULL,
`Postfach` longtext,
`IDLand` int(9) unsigned NOT NULL,
`Laengengrad` float NOT NULL,
`Breitengrad` float NOT NULL,
`Adresszusatz` longtext,
PRIMARY KEY (`ID`),
KEY `FK_Adressen_Land` (`IDLand`),
CONSTRAINT `FK_Adressen_Land` FOREIGN KEY (`IDLand`) REFERENCES `laender` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=137205 DEFAULT CHARSET=latin1
SELECT STRASSE, HEX(STRASSE) FROM adressen WHERE STRASSE LIKE 'ä%'
:
Ä[...];C4[...]
According to Wikipedia's article about ISO8859 that's latin1 alright.
edit2
After some further reading I found that both, HeidiSQL and .NET handle text in utf8 / unicode [at least by default]. The solution to this question is thus to convert everything to utf8mb4_unicode_ci as per this answer.