News:

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

Main Menu

UTF-8 collation: latin1_swedish_ci, character_set: latin1

Started by dekatria, July 16, 2018, 05:34:45 PM

Previous topic - Next topic

dekatria

Although I only use English and I have Converted to UTF-8
All my tables show:
collation: latin1_swedish_ci, character_set: latin1

Why is that? Is there a way (need?) to fix it?

Kindred

Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

shawnb61

Is the option to convert to utf8 still available on the admin panel?

If it is, run it. 

Read this note first for guidance on the correct setting to use:
https://www.simplemachines.org/community/index.php?topic=554994.msg3936447#msg3936447
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Sir Osis of Liver

Try converting to utf-8 in phpmyadmin.  You may have to convert tables individually to get them all.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

dekatria

shawnb61,

There are no settings/options to use, just a button.
The option to convert to utf8 is always available and always shows result:
QuoteThe maintenance task 'Convert HTML-entities to UTF-8 characters' was executed successfully.
I'm confused by your other post (link) about whether you are referring to the phpmyadmin or the forum admin panel - maintenance page. Because there is no dropdown menu in my forum admin interface.
However I might have run in the problem you describe when I created a new db and imported to it the backup of an old one.
But it is not clear how to fix this now.


I will check with the phpmyadmin and probably followup

GigaWatt

Quote from: dekatria on July 16, 2018, 09:41:00 PM
However I might have run in the problem you describe when I created a new db and imported to it the backup of an old one.

If the database was clean (no tables) and you imported the backup, this shouldn't have happened. The backup should also recover the appropriate collations of the tables and rows.

Try the tool shawnb61 posted in the link ;).
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Kindred

unless the OP did the convert and THEN imported an old backup.   That would put things in a weird state.


dekatria, you are going to have to manually change the collations of the tables.
because
Quote
If your columns/tables are latin1-xxxx-xxxx, and you select UTF-8 in Data character set, your data will be corrupted...
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

dekatria

I went to phpmyadmin and this is what I see. A mix of innodb and MyISAM and a mix of utf-8 and  latin1_swedish_ci...


How do I fix this. Should I use this button in phpmyadmin (screen3)?

Please see attachments.

Arantor

Don't trust any of those, because you can have a database collation different to a table collation, different to a column collation.

Go into the tables and see what the actual columns are listed as before going any further.

dekatria

Is this mix of of innodb and MyISAM and a mix of utf-8 and  latin1_swedish_ci ok?
I'm not very experienced with database editing. There is a danger I mess things worse...
Maybe if it works don't fix it? So far my forum functions as usual.

(I started looking into the database thing because I need to do a search replace (the domain name in the posts, links etc.) and I couldn't do it with a tool I tried to use see here)


Arantor

The mix of InnoDB and MyISAM is fine. The mix of collations is a lie.

Your database is notionally UTF-8 so when you make new tables they will default to UTF-8 as well. But none of that matters because it's what the actual columns themselves are that actually matters.

Click on the Structure tab for a table, see what that says. Honestly, nothing else is actually relevant here.

dekatria

I finally managed to convert all tables to utf-8 collation and then perform the search replace action I wanted to do.
I did both, first convert to utf-8 then perform search replace, with the tool Database Search and Replace Script in PHP

My experience may be of help to someone else.

GigaWatt

You could have also used this script. It's made specifically for SMF ;).
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Advertisement: