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?
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.
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 'ä%':
According to Wikipedia's article about ISO8859 that's latin1 alright.
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.