UTF 8

[Plantje]

Trying to convert my db to utf 8 in order to enable users to use special characters. Now I get an error message that I am using forum software version 2.0.11 and db version 2.2 and running the latest upgrade.php is most likely the solution.
I can just download that upgrade.php from the wiki, right? And when doing so, am I then upgrading the entire forum?

[Dav999]

The database upgrade warning should be completely ignored.

What's the full error message?

[shawnb61]

This is not a job for upgrade.php!

There is an admin function under maintenance to do the UTF8  conversion.   

Do a backup first, just in case...   UTF8 conversions can be tricky...

[Plantje]

Did the backup (of course )

This is the exact error message:

Code Select Expand

Database Error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key key varbinary(255) NOT NULL default ''' at line 10
File: /customers/3/2/5/vliegvissers.com/httpd.www/SMForum/Sources/ManageMaintenance.php
Line: 667

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.11, while your database is at version 2.0.2. The above error might possibly go away if you execute the latest version of upgrade.php.

And in posting this I see I made a mistake in how I thought the error message was. It seems I have database version 2.0.2 rather than 2.2

I followed the guidelines here: http://wiki.simplemachines.org/smf/UTF-8_Readme#How_to_convert_to_UTF-8
Guess that is the admin function you're referring to.

I have an acceptance test environment available as well. I will first try the conversion there.

[shawnb61]

Yes, those are the correct instructions.

Dav999 is correct, that "It appears that your database ..." message is bogus.

After installing new language files, some mods may need reinstallation, as many mods update the language files with new text.

[Plantje]

Hmmm... ok. So, no need to run the upgrade.php.

But the special characters are still not working. So it seems the upgrade didn't work.

[shawnb61]

Did the utf8 conversion complete successfully?

Did the db dropdown say iso-8859 ?   If possible, I would try the utf8 conversion again with utf8 as the selection.  That may make the difference.

Very similar thread here, you may find helpful:
https://www.simplemachines.org/community/index.php?topic=555070.0

[Plantje]

No the conversion errors out with the aforementioned error. Will check the other thread. Thanks!

[shawnb61]

Can you show the lines of code with the error?

[Arantor]

The error is a query that is not compatible with MariaDB as given by the error message... because yet again, another word now reserved or in a new context now reserved by MariaDB that wasn't in MySQL.

[shawnb61]

Yep.  Arantor is correct.  The problem is what to do about it, as this particular chunk of code is dynamically built. 

And I'm about to leave town for a couple of days, literally in 10 minutes, and I don't have time to test this...

I'd be tempted to just put backtick quotes around every field column.  I.e., change:

Code Select Expand

$updates_blob .= '
CHANGE COLUMN ' . $column['Field'] . ' ' . $column['Field'] . ' ' . strtr($column['Type'], array('text' => 'blob', 'char' => 'binary')) . ($column['Null'] === 'YES' ? ' NULL' : ' NOT NULL') . (strpos($column['Type'], 'char') === false ? '' : ' default \'' . $column['Default'] . '\'') . ',';
updates_text .= '
CHANGE COLUMN ' . $column['Field'] . ' ' . $column['Field'] . ' ' . $column['Type'] . ' CHARACTER SET ' . $charsets[$_POST['src_charset']] . ($column['Null'] === 'YES' ? '' : ' NOT NULL') . (strpos($column['Type'], 'char') === false ? '' : ' default \'' . $column['Default'] . '\'') . ',';


To...

Code Select Expand

$updates_blob .= '
CHANGE COLUMN \'' . $column['Field'] . '\' \'' . $column['Field'] . '\' ' . strtr($column['Type'], array('text' => 'blob', 'char' => 'binary')) . ($column['Null'] === 'YES' ? ' NULL' : ' NOT NULL') . (strpos($column['Type'], 'char') === false ? '' : ' default \'' . $column['Default'] . '\'') . ',';
updates_text .= '
CHANGE COLUMN \'' . $column['Field'] . '\' \'' . $column['Field'] . '\' ' . $column['Type'] . ' CHARACTER SET ' . $charsets[$_POST['src_charset']] . ($column['Null'] === 'YES' ? '' : ' NOT NULL') . (strpos($column['Type'], 'char') === false ? '' : ' default \'' . $column['Default'] . '\'') . ',';


I think the solution will be along those lines...

[Arantor]

Except those aren't backticks, ` is a backtick, ' is not (and ` doesn't need escaping)

[Plantje]

Did the utf8 conversion complete successfully?

Did the db dropdown say iso-8859 ?   If possible, I would try the utf8 conversion again with utf8 as the selection.  That may make the difference.

Very similar thread here, you may find helpful:
https://www.simplemachines.org/community/index.php?topic=555070.0

That made the script run through on my acceptance test environment. Will have some testers test it as well.

First tests that I ran are ok! It doesn't update the older threads obviously, but the new replies are displaying special characters.

Thanks everyone for thinking along!

[shawnb61]

Plantje -
I'm going to mark this topic as solved.  Any questions or additional problems, let me know.

[Plantje]

Agreed, as it is solved.

Should I have done that myself?