1

Lately i have migrated some schemas from mysql 4 to mysql 5.6. Everything works fine except one issue: Hebrew characters appears in Gibberish.

Here is the current configuration:

SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | hebrew                     |
| character_set_connection | hebrew                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | hebrew                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

select * from INFORMATION_SCHEMA.CHARACTER_SETS where CHARACTER_SET_NAME='Hebrew';
+--------------------+----------------------+-------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION       | MAXLEN |
+--------------------+----------------------+-------------------+--------+
| hebrew             | hebrew_general_ci    | ISO 8859-8 Hebrew |      1 |
+--------------------+----------------------+-------------------+--------+

select * from INFORMATION_SCHEMA.COLLATIONS where CHARACTER_SET_NAME='Hebrew';
+-------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-------------------+--------------------+----+------------+-------------+---------+
| hebrew_general_ci | hebrew             | 16 | Yes        | Yes         |       1 |
| hebrew_bin        | hebrew             | 71 |            | Yes         |       1 |
+-------------------+--------------------+----+------------+-------------+---------+

My dump file contains the following statement for example:

Create Table: CREATE TABLE `table` (
) ENGINE=InnoDB DEFAULT CHARSET=hebrew

I tried to change it to the following, which didn't help:

Create Table: CREATE TABLE `table` (
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci

Values for example:

select manufacturer, hex(manufacturer) from cars LIMIT 5;
+--------------+-------------------+
| manufacturer | hex(manufacturer) |
+--------------+-------------------+
| ������  | F1E5E1E0F8E5      |
| ������  | F1E5E1E0F8E5      |
| ������  | F1E5E1E0F8E5      |
| ����      | E0E5E3E9          |
| ����      | E0E5E3E9          |
+--------------+-------------------+

Any suggestions? What could be the issue?

  • This belongs in stackoverflow, where it would be a dup of stackoverflow.com/questions/38363566/… – Rick James Aug 28 '17 at 21:52
  • It seems like that when i change the variable character_set_results from utf8 to Hebrew, it fix the issue for the new schemas, that has been imported from the old instance, But it make the current schemas to appear in Gebberish. Is this variable can be defined per schema? If so' i'll set it only on the new schemas. – Omri Aug 29 '17 at 12:29
  • 1
    Please provide SELECT HEX(...) as suggested in my link -- I want to see is in the table. ("Gibberish" is not specific enough for me to determine which of several problem you are having.) – Rick James Aug 29 '17 at 13:20
  • @RickJames, I've just added some values for example above. – Omri Aug 29 '17 at 13:28
  • 1
    Hmmm... If my answer does not suffice, I will need some more details on the dataflow. And maybe your one example used mysql, but there are other clients?? – Rick James Aug 29 '17 at 13:59
1

Analysis

Thanks for the updates; it makes things clear.

F1E5E1E0F8E5, is סובארו when interpreted in CHARACTER SET hebrew.

CREATE TABLE `table` (
) ENGINE=InnoDB DEFAULT CHARSET=hebrew

is not sufficient -- It is possible for each VARCHAR column to override the CHARSET. I'll assume that you have not done that.

Since the HEX in the table is consistent with CHARSET hebrew, I will assume the input side of things is consistent.

Solution 1

The "black diamonds" are coming from the output tool. What tool are you using? Looks like the mysql commandline tool? That needs to know how to display the data. By default (I think), it interprets the bytes it gets (from the SELECT) as being UTF-8. But they are not. Are you running on Windows or *nix? I don't know the details of setting it to Hebrew, but that is probably the solution.

Solution 2

Another solution is to start your 'mysql' session with SET NAMES utf8; (or utf8mb4). That will announce that you want the bytes in the client to be utf8. As the Hebrew chracters are coming from the table, they will be converted to utf8, and will (I think, without trying it) dislayed correctly. Meanwhile, the table will continue to be storing F1E5 E1E0 F8E5, etc.

HTML

If you will be displaying on web pages, keep in mind that browsers make guesses on what the encoding is. You could help them by either doing Solution 2 so that they get UTF-8, which they will readily assume, or you can stick with ISO 8859-8 and help them with <meta charset=...> and <form accept-charset...>` tags.

Please respond with the details of how you choose to solve the issue(s). This is a scenario that is somewhat unusual.

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.