0

Good morning, I need to migrate a MySQL 8.0.16 database into a MySQL 5.5 instance.

all worked well, but all the words with accents (e.g. Giacenza-Disponibilità) were ported in the new database like the wrong charset was used (Giacenza-Disponibilit├á).

The original DB charset settings were:

CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;

While the destination database couldn't be set to utf8mb4_0900_ai_ci as that collation, I read, is not available for MySQL < 5.6. I tried setting many different utf8 and utf8mb4 charsets, like utf8mb4_0900_ai_ci, but the resulting database still contains broken records, like in the example before. If i insert any new record, like perché, it will be stored and shown correctly.

could anyone please suggest me a way to migrate the database without encountering those issues?

Fed C
  • 143
  • 1
  • 13
  • Why are you going backwards to a less optimized version ? In MySQL < 5.6, you will need to use `utf8_general_ci` as Collation, and `utf8` as Charset – Madhur Bhaiya Sep 18 '19 at 08:12
  • it's because I need to install the mroonga library. I tried using those sets, but the result is still broken (validitàinstead of validità). – Fed C Sep 18 '19 at 08:57

2 Answers2

1

Solved: I exported the whole database into a .sql script, and transformed the file encoding to ANSI with n++, now the migration works correctly.

Fed C
  • 143
  • 1
  • 13
  • 1
    ciao oramai penso abbia risolto brillantemente in questo modo ma io avrei creato prima l'istanza (il db vuoto) in utf8_unicode_ci e poi avrei reimportato il db precedente – BUcorp Sep 18 '19 at 09:36
1

Somewhere, you are using one of these CHARACTER SETs: cp850, cp852, keybcs2, correct? They transform the UTF-8 encoding (hex c3a0) for à to ├á.

See "Mojibake" in Trouble with UTF-8 characters; what I see is not what I stored for a list of the things that are problably not set correctly:

  • The bytes to be stored need to be UTF-8-encoded. (This does not seem to be the problem.)
  • The connection when INSERTing and SELECTing text needs to specify utf8 or utf8mb4. This is probably the cause.
  • The column needs to be declared CHARACTER SET utf8 (or utf8mb4). (Not the problem)
  • HTML should start with .
Rick James
  • 135,179
  • 13
  • 127
  • 222