0

I migrated a Microsoft SQL database to Mysql and I hat some collation problems in the rows in Mysql, I tried to change the collation but the erros still there. The data is goning to be in a Wordpress, so I tried the Database Collation Fix pluguin but doesn't work.

The table afected is wp_posts in post_title and post_content. All the characters that contain an accent or 'ñ' in Spanish are replaceed by a random character.

I already tried with utf8_spanish_ci and utf8mb4_spanish_ci.

Any suggestions?

Microsoft SQL database collation: Modern_Spanish_CI_AI

Mysql database collation: UTF8 Defaul Collation

Thanks

lip
  • 19
  • 3
  • "some collation problems" is not a sufficiently detailed description of your issue. Not enough info here to help you accurately. However, an obvious question occurs: Can you change the collation of MySQL to the same as the MS SQL one (preferably _before_ you migrate the data to it)? Or does that break Wordpress? – ADyson Aug 09 '17 at 10:17
  • Please consider making an [edit] to your question to give more details. What table in WordPress? Please show its definition. *Some collation problems in some rows* is not specific enough to guess what's wrong. – O. Jones Aug 09 '17 at 10:27

2 Answers2

0

I don't know if this helps you, but the collating orders in MySQL's Modern Spanish utf8_spanish_ci and/or utf8mb4_spanish_ci collations are different from those in utf8_unicode_ci and/or utf8mb4_unicode_ci.

Modern Spanish collation handles N and Ñ as separate characters, with Ñ coming directly after N. Generic latin-language collation treats them as variants of the same character. So, if you want Spanish collation -- that is, if you're dealing with lots of proper names and so forth -- you'll need to use the Spanish collation for this data.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

If ñ turned into ?, you have one type of problem.
If ñ turned into ñ, you have "Mojibake".
If ñ turned into �, it's yet another problem.

Please be more specific, since the solutions are quite different.

Trouble with utf8 characters; what I see is not what I stored provides information on the common issues.

The "Collation" is not relevant to ñ being replaced by a 'random character'. Only the CHARACTER SET is relevant.

When you get into comparing or sorting strings, then the COLLATION becomes relevant. I think the only difference between ..._spanish_ci and ...spanish2_ci is the handling of ch and ll.

Rick James
  • 135,179
  • 13
  • 127
  • 222