Apostrophe and Quotation marks causing database errors after upgrade

[808server]

I upgraded from 2.0.19 to 2.1.4 and now am getting database errors when using quotations and apostrophe errors.

I searched but I didn't see any resolve.

Any ideas?

Thanks

[Illori]

what exact errors are you getting?

[808server]

Any use of apostrophe or quotes from iOS will cause this:

Incorrect string value: '\xE2\xEF\xBF\xBD\xEF\xBF...' for column 'body' at row 1
File: /home/stompbox/public_html/smfforum/Sources/Subs-Post.php
Line: 1897

[808server]

"

[808server]

It's a problem with my site. I can post here.

[808server]

I believe I have found the problem but how to fix it?
The upgrade.php never converted my tables over to utf8_general_ci.
So how can I convert all the tables over?

[808server]

I took a look at upgrade.php. Am I correct in that I have to run:
function ConvertUtf8()

?

[Sir Osis of Liver]

You can change collation with phpmyadmin.

[808server]

There's a twist. There was a sql copy created in the directory and it has all the tables as MyISAM  utf8mb3_general_ci.

Is this correct or should they be utf8_general_ci?

I need to go and check on the differences. Basically the smart quotes of iOS are not accepted.

[shawnb61]

utf8mb3_general_ci = utf8_general_ci, they are synonyms.

You want your tables set to one of these.

[Sir Osis of Liver]

2.1.4 installs with utf8mb3_general_ci collation.

[808server]

Thank you for the reply. Can you think of any other reason why I am getting this error even after the collation has been changed?

[shawnb61]

For your SMF db to be fully utf8, 3 things must be true:
 - All of your tables are utf8-general-ci (or utf8mb3-general-ci, which is a synonym)
 - The value of $db_character_set in your Settings.php file should be: 'utf8'
 - The value of the 'global_character_set' entry in your smf_settings table should be: 'UTF-8'

I'd first check those 3 things.  The last two are usually not set if folks have manually converted to utf8 somehow. 

(Note this error has been reported before, and resolved by setting the values as noted above.  https://www.simplemachines.org/community/index.php?msg=4150709)

[808server]

All tables are definitely utf8mb3-general-ci
$db_character_set = 'utf8';
There is no global_character_set entry in smf_settings. I will add one.

[808server]

Thanks. That was it. Appreciate it.