0

I tried to migrate my AWS RDS instance from Aurora to MySQL. I created a dump file from the Aurora and imported it into the MySQL instance.

Both instances have the same characters set.

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8mb4                                   |
| character_set_connection | utf8mb4                                   |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8mb4                                   |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.7.26.R1/share/charsets/ |
+--------------------------+-------------------------------------------+

In the original DB the Hebrew letters appears as black diamonds (Converted to Hebrew in app side). After the migration to the new MySQL instance, instead of black diamonds i see questions marks.

What could be the issue?

5
  • You wrote uft8 inti a Latin1 database, and i guess your tabkes a also latin1(default). So you need to get all data connected with character set latin1 and then create a database that is all the way utf8(datase, tabkes Fields) and then you connecting using the word uft8 in the connection string, you have to ggole that for your connectionstring and then import the the database into the new utf8 database. – nbk Jan 26 '20 at 11:21
  • See "question mark" in stackoverflow.com/questions/38363566/… – Rick James Jan 29 '20 at 4:19
  • @RickJames Thanks for the link. I could take the dump properly by using --default-character-set=hebrew. Why is that? Why --default-character-set=utf8 wasn't good enough? – Omri Jan 29 '20 at 12:56
  • hebrew will handle Hebrew and English, but not much else. utf8mb4 handles every character set in the world. "--default..." is only one of about 4 things that need to be set to properly handle anything other than just English (Ascii). – Rick James Jan 29 '20 at 16:34
  • I've have just tried to dump Russian and Japanese with hebrew and it worked as excepted. – Omri Jan 29 '20 at 16:58
1

I think your problem is that you have exported your dump file with ANSI character set, to take a dump file using UTF8, issue your mysqldump command with option --default-character-set=utf8 and check the results. (AFAIK the same options for mysqldump work for Aurora as well)

PS : After your comments, I dug the problem a bit more. The problem comes from different configurations for character-set-system (which is utf8), character_set_server (which is latin1) with character-set-client (which is utf8mb4). Based on this link to documentation, this difference might cause issues in data input and may result in bad format in output. You can check the validity of this root cause by changing your character sets for different components.

7
  • Thanks. I've done that procedure (dump and import) so many times before and never had this proble. What could be the difference now? Is this related to AWS? – Omri Jan 26 '20 at 10:32
  • It should be, check with AWS to get their input, I've been working with UTF8 throughout my entire career in database and your issue is simply a character set convert that happens in the background also, check some data inside your dump file, see if it contains hebrew or question marks – aLuViAn Jan 26 '20 at 10:38
  • Hi. I've just tried to dump the data with the flag --default-character-set=utf8 and got the same result with the question marks. Any other idea? – Omri Jan 26 '20 at 17:15
  • Change all of the character sets within your database to utf8, also, check that the server you are taking dump from is supporting utf8. Did you check the data within the dump file itself? – aLuViAn Jan 27 '20 at 5:03
  • I've changed all the the character sets to ut8 - Didn't help. What do you mean by " check that the server you are taking dump from is supporting utf8"? I'm taking the dump in Linux server which is obviously supporting ut8. The data in the dump file is with question marks. – Omri Jan 28 '20 at 17:09

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.