Advertisement:

Author Topic: Concerted to SMF Manually and Funny Characters Appeared  (Read 3424 times)

Offline mattizzy

  • Semi-Newbie
  • *
  • Posts: 75
  • Gender: Male
    • mattizzy.okeke on Facebook
    • @mattizzyo on Twitter
Concerted to SMF Manually and Funny Characters Appeared
« on: July 21, 2018, 01:18:27 AM »
Hello, I recently moved to SMF from a self created forum script. I just backed up my database and created another database called BACKUO and inserted all data from the self created forum script.

I head over to SMF database which will created on install, I used a php command to insert data from BACKUP to SMF and I started seeing “ ,”, ‘ e.t.c in between posts. can you explain why this happened and how to fix?
I am only an SMF Addict. I think I took overdose.

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,433
    • sbulen on GitHub
Re: Concerted to SMF Manually and Funny Characters Appeared
« Reply #1 on: July 21, 2018, 02:18:11 AM »
You wrote the conversion script yourself?

There is something wrong with your charset in conversion.

First guess is your script didn't issue a  SET NAMES to get mysqli thinking in UTF8.

Charset of source db?   Charset of smf?  Did you issue a SET NAMES in your converter?   Or issue a mysqli_set_charset()?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline mattizzy

  • Semi-Newbie
  • *
  • Posts: 75
  • Gender: Male
    • mattizzy.okeke on Facebook
    • @mattizzyo on Twitter
Re: Concerted to SMF Manually and Funny Characters Appeared
« Reply #2 on: July 21, 2018, 07:17:33 AM »
Yes I created it my self crested with regular php INSERT SELECT Statement. by inserting from My Custom Site DB to SMF DB
I am only an SMF Addict. I think I took overdose.

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,433
    • sbulen on GitHub
Re: Concerted to SMF Manually and Funny Characters Appeared
« Reply #3 on: July 21, 2018, 09:46:15 AM »
Charset of source db? 
 Charset of smf?
 Did you issue a SET NAMES in your converter?   
Or issue a mysqli_set_charset()?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline mattizzy

  • Semi-Newbie
  • *
  • Posts: 75
  • Gender: Male
    • mattizzy.okeke on Facebook
    • @mattizzyo on Twitter
Re: Concerted to SMF Manually and Funny Characters Appeared
« Reply #4 on: July 21, 2018, 10:39:31 AM »
Character of Custom site DB UTF8mb_Unicode,

Charset of SMF is UTF8_general_ci

I am only an SMF Addict. I think I took overdose.

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,433
    • sbulen on GitHub
Re: Concerted to SMF Manually and Funny Characters Appeared
« Reply #5 on: July 21, 2018, 10:45:37 AM »
Did you issue a SET NAMES in your converter?   

Or issue a mysqli_set_charset()?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline mattizzy

  • Semi-Newbie
  • *
  • Posts: 75
  • Gender: Male
    • mattizzy.okeke on Facebook
    • @mattizzyo on Twitter
Re: Concerted to SMF Manually and Funny Characters Appeared
« Reply #6 on: July 21, 2018, 10:54:52 AM »
Charset of source db? 
 Charset of smf?
 Did you issue a SET NAMES in your converter?   
Or issue a mysqli_set_charset()?


How can I know the charset, is the collocation prefix the charset?

Tell me how to set names also so I can run the script again?

The source DB and SMF DB are using the same collocation. latin1_swedish_ci

but the columns collocation are fluctuating from utf8_general_ci to utf8_bin respectively e.t.c
I am only an SMF Addict. I think I took overdose.

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,433
    • sbulen on GitHub
Re: Concerted to SMF Manually and Funny Characters Appeared
« Reply #7 on: July 21, 2018, 09:23:04 PM »
Yes, the charset is the prefix on the collation.  latin1_swedish_ci is the "latin1" charset (aka ISO 8859-1) with the swedish_ci collation. 

The reason it is easy to get confused is that MySQL has a hierarchy of defaults so you can see different settings depending on where you look.  There is a database-level default, that is used when making records.  There are also record-level defaults used when adding columns. 

Despite this, when making updates to MySQL databases, you need to inform it what charset you're working with via SET NAMES or mysqli_set_charset(). 
   https://stackoverflow.com/questions/1650591/whether-to-use-set-names

IMO, it really oughta know what it's updating & not be told what it's updating...   But it nonetheless does need to be told via SET NAMES or mysqli_set_charset().  Without doing so bad things happen. 

So...

My suggestion is to:
(1) Modify your code to issue a SET NAMES utf8.
(2) Modify your database-level default to be utf8_general_ci (so mods don't introduce a bunch of latin1/swedish tables/columns by accident...)
(3) Reinstall SMF so everything is consistently utf8_general_ci (the SMF default when UTF8 is specified).
(4)  Try your converter again & see if the SET NAMES fixes your conversion...

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