1

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.

1

See "Question Marks" in here . In particular,

  • The bytes to be stored are not encoded as utf8/utf8mb4. Fix this.
  • The column in the database is CHARACTER SET utf8 (or utf8mb4). Fix this.
  • Also, check that the connection during reading is UTF-8.

Since you seem to be aiming for latin1, not utf8, see "Test the data" in that link. äüöß will be hex E4 FC F6 DF in latin1, or C3A4 C3BC C3B6 C39F in utf8/utf8mb4.

SET NAMES establishes the encoding in the client, not the server. The table's encoding is based on SHOW CREATE TABLE; let's see that.

OR...

It is OK if you want latin1 used in the columns/tables, but your client wants utf8. Then the connection setting (to say utf8 / UTF-8) or the use of SET NAMES utf8 (after connecting) will alert MySQL to convert as data is transferred between the client and the server and database.

(With latin1, you are limited to Western European characters.)

  • thank's for the explanation. I added the required information to my answer. As I want to stay with latin1, I'm going to assume that I'd have to 'interchange' latin1 and utf8mb4 in your linked answer/your quoted text. – Steffen Winkler Feb 26 '17 at 16:20
  • After some further research I'm going to mark your answer as 'the' answer as by doing what you wrote down I was able to verify that the storage/database side did everything as 'told' but the viewers (HeidiSQL/.NET strings) are defaulting to utf8 or unicode and thus can not handle latin1 encoded strings by default. – Steffen Winkler Feb 26 '17 at 16:40
  • I updated my Answer to take into consideration your Comment. – Rick James Feb 26 '17 at 18:09
  • My paltry notes notes on .NET say "Connector/.NET possibly defaults to latin1. Consider changing 'connection string' if you need utf8. ", "ADO.NET "charset=utf8"", and "<%Response.charset="utf-8"%>" – Rick James Feb 26 '17 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.