Advertisement:

Author Topic: Strange characters after moving database.  (Read 418 times)

Offline Klajdi

  • Semi-Newbie
  • *
  • Posts: 29
Strange characters after moving database.
« on: December 05, 2017, 11:20:50 AM »
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:
Code: [Select]
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
Code: [Select]
Server type: MySQL
Server version: 5.6.36-cll-lve - MySQL Community Server (GPL)
Protocol version: 10

New database
Code: [Select]
Server: Localhost via UNIX socket
Server type: MariaDB
Server version: 10.1.29-MariaDB - MariaDB Server
Protocol version: 10

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 7,072
  • 'Tis the gift to be simple
Re: Strange characters after moving database.
« Reply #1 on: December 05, 2017, 03:38:40 PM »
Was your database converted to utf-8 before/after you moved it?

Offline Klajdi

  • Semi-Newbie
  • *
  • Posts: 29
Re: Strange characters after moving database.
« Reply #2 on: December 05, 2017, 03:42:52 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:
Code: [Select]
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:
Code: [Select]
" ℳҿšš ωɨƭɧ ƫɧ∊ ℬ∊šʈ, Ðɨ∊ ℒɨƙ∊ ƭɧε Ʀ∊ʂʈ ! 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)
« Last Edit: December 05, 2017, 03:57:31 PM by Klajdi »

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 7,072
  • 'Tis the gift to be simple
Re: Strange characters after moving database.
« Reply #3 on: December 05, 2017, 04:04:54 PM »
Look in Settings.php in your forum root, does it contain  $db_character_set = 'utf8';?

Offline Klajdi

  • Semi-Newbie
  • *
  • Posts: 29
Re: Strange characters after moving database.
« Reply #4 on: December 06, 2017, 08:55:12 AM »
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)

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 7,072
  • 'Tis the gift to be simple
Re: Strange characters after moving database.
« Reply #5 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.

Offline Illori

  • Project Manager
  • SMF Master
  • *
  • Posts: 48,048
Re: Strange characters after moving database.
« Reply #6 on: December 07, 2017, 05:16:56 AM »
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.

Offline Klajdi

  • Semi-Newbie
  • *
  • Posts: 29
Re: Strange characters after moving database.
« Reply #7 on: December 07, 2017, 09:49:11 AM »
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?

Offline Illori

  • Project Manager
  • SMF Master
  • *
  • Posts: 48,048
Re: Strange characters after moving database.
« Reply #8 on: December 07, 2017, 09:50:35 AM »
no it does not, you need to convert the tables to utf-8 as i stated above.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 7,072
  • 'Tis the gift to be simple
Re: Strange characters after moving database.
« Reply #9 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?


Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 507
    • sbulen on GitHub
Re: Strange characters after moving database.
« Reply #10 on: December 07, 2017, 04:03:46 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