News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Strange characters after moving database.

Started by Klajdi, December 05, 2017, 11:20:50 AM

Previous topic - Next topic

Klajdi

Hello,
Recently I had to move forums and thus the database aswell. However, after everything was completed, some users names (personal text, title and probably posts) changed to some strange characters.
One example which I saw was this:
The old name of a member was Name™ and after moving database it became Nameâ,,¢ (This only happens to some sypes of characters, as far as I know it doesn't happen to letters/numbers).
After trying to change his name back to the old one I got this message:
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICT)
And below something that said that my database needed an upgrade. Later I just changed the name to Name™ (which worked).
Now I am noticing that some members have their titles and personal texts this way aswell, and I wouldnt like to take any action without any advice.
Forum is running under SMF 2.0.13

I thought these might be useful aswell:
Old database
Server type: MySQL
Server version: 5.6.36-cll-lve - MySQL Community Server (GPL)
Protocol version: 10


New database
Server: Localhost via UNIX socket
Server type: MariaDB
Server version: 10.1.29-MariaDB - MariaDB Server
Protocol version: 10


Sir Osis of Liver

Was your database converted to utf-8 before/after you moved it?
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Klajdi

#2
Quote from: Sir Osis of Liver on December 05, 2017, 03:38:40 PM
Was your database converted to utf-8 before/after you moved it?
I'm not sure, I had a member from the host support team move the database since it was large. But now they are the same I guess. Both:
Server charset: UTF-8 Unicode (utf8)
Collation of each table in both databases is "latin1_swedish_ci"


EDIT: Actually the database seems to have the right characters, they only seem to be displayed wrong.
This text (" ℳҿšš ωɨƭɧ ƫɧ∊ ℬ∊šʈ, Ðɨ∊ ℒɨƙ∊ ƭɧε Ʀ∊ʂʈ ! Hmph ") is saved in the database as:
" ℳҿšš ωɨƭɧ ƫɧ∊ ℬ∊šʈ, Ðɨ∊ ℒɨƙ∊ ƭɧε Ʀ∊ʂʈ ! Hmph "
but is displayed as  " ℳҿÅ¡Å¡ ωɨƭɧ ƫɧ∊ ℬ∊Å¡ʈ, Ðɨ∊ ℒɨƙ∊ ƭɧε Ʀ∊ʂʈ ! Hmph "

Also the Name™ was actually saved as that in the database, but would be displayed wrong. And im still not sure what caused the error page when changing name. (Now name is Name™ bacause i changed it and is displayed right)

Sir Osis of Liver

Look in Settings.php in your forum root, does it contain  $db_character_set = 'utf8';?
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Klajdi

Quote from: Sir Osis of Liver on December 05, 2017, 04:04:54 PM
Look in Settings.php in your forum root, does it contain  $db_character_set = 'utf8';?
It doesn't seem to be set. I can not find a variable named db_character_set in Settings.php file (If i checked correctly it's where db credentials are set)

Sir Osis of Liver

Doesn't matter where it is in the file, you need that line if you're using utf-8 database.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Illori

Quote from: Klajdi on December 05, 2017, 03:42:52 PM
Collation of each table in both databases is "latin1_swedish_ci"
if each table is not utf-8 then you need to convert your tables to utf-8 as well as doing the above edit. if the tables are not utf-8 the characters will not show up correctly.

Klajdi

Quote from: Sir Osis of Liver on December 06, 2017, 09:41:47 PM
Doesn't matter where it is in the file, you need that line if you're using utf-8 database.
Does that mean that after adding that line im good to go?

Illori

no it does not, you need to convert the tables to utf-8 as i stated above.

Sir Osis of Liver

It's in Admin -> Forum Maintenance -> Database.

Seems like it was already converted to utf-8 on old server, why would collation change when moved to new server?

Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

shawnb61

Quote from: Sir Osis of Liver on December 07, 2017, 03:36:06 PM
It's in Admin -> Forum Maintenance -> Database.
Seems like it was already converted to utf-8 on old server, why would collation change when moved to new server?

That all depends on exactly how the DB was migrated.  Net-net, though is that if the collation says it's latin1, those tables are not UTF8 at the moment. 

Note also that latin1 (ISO-8859-1) tables can hold UTF8 characters...  It's confusing, but basically latin1 behaves like single-byte binary, i.e., it'll take anything.   

So things are a little confused.   Here are my recommended steps:

(1) MAKE A NICE NEW SAFE BACKUP of the DB. 
(2) Do the UTF8 conversion as directed above.  Note there is a dropdown called "Data character set".   Since your source tables are latin1, you should select ISO-8859-1 in the dropdown.
(3) Test & make sure everything is OK.
(4) To get rid of the html characters, you should next run the "Convert HTML-entities to UTF-8 characters" option on the same menu. 
(5) Test & make sure everything is OK.

The UTF8 conversion will properly update settings, including the Settings.php file discussed above.

If anything goes wrong, restore your DB from backup, no harm done. 

For more information, I hope this helps, read all 3 posts:
   https://www.simplemachines.org/community/index.php?topic=554994.0

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Advertisement: