Problems when converting latin1 to utf8

[TSP]

Hi, the other day we test upgraded a db copy of our site. We also decided we would like to test and make the switch to utf-8.

Please note: This convert is only done on a test version of our forum with a test db.

We did this by commands like:
mysql> ALTER TABLE ippbe_posts CONVERT TO CHARACTER SET utf8 COLLATE utf8_swedish_ci;
Query OK, 19203896 rows affected (2 hours 48 min 33.23 sec) Records: 19203896 Duplicates: 0 Warnings: 0

On all the tables. As you can see, it appearantly executed successfully.

Testing it later, it worked but... then I made the switch of the document character set to utf-8 and then it didn't work anymore. Causing errors like:


As you can see it affected the language as well as the post. But even in that post.. the letter

[TSP]

I had to put sql_charset = utf8 in the config-file and that seems to have resolved things..

But I'm still wondering, is it likely the convert was succesful? Do you have any tips on how to properly test that things is working properly?

I've heard about problems that when you convert from latin1 to utf8 you risk getting double encoded chars etc. is there a way to test for that? It's a 11 year old forum who have been through many upgrades and conversions in it's early days so just want to be as comfortable with this as we can possibly get before we convert our live forum.

[TSP]

So we got a problem.. which I submitted as a ticket to IPS. Unfortunately they can't help with sorting out issues like this. So I'm wondering if you could have any ideas?

So this is slightly weird...

We did a test convert to utf8 the other day on our test board and things looked okay after I had put in $INFO['sql_charset'] = 'utf8'; to the config-file and recached language packs etc. I actually posted a post in the forum here:

http://community.inv...latin1-to-utf8/

But.. when I came back to the test board today, after I've not been there for a few days, I was met with an IPB Fatal Error. The error told me that IPB was not able to load the settings and IPB was unable to run because of it. And that it was likely caused by collation/charset-issues.

So I decided to comment out $INFO['sql_charset'] = 'utf8'; and reloaded the page. Then it worked. Then I added it back in.. and it still worked... So I'm confused here...

It worked a few days ago, just after what looked to be a successful utf8-convert of our test board. Then, today, settings suddenly decide it doesn't like the charset anymore and fails to load them, and then when I remove utf8 in the config-fille it works again. And when I add it back in, it still works.

So.. what's the reason? Obviously something we would want to avoid from happening on our live board if we decide to convert from latin1 swedish to utf8 swedish there aswell..

[TSP]

Once again it fails, after I've not visited it since my last post.. You can see the error here: http://diskusjon.test.hw.no/

or read it:

Your settings could not be read by IP.Board. This is a fatal error and IP.Board cannot function while this issue persists.

This issue is generally caused by changing your character set in the ACP to one that does not support data stored in the rest of your settings, or by restoring a database backup/completing a server transfer and importing your database tables using the wrong character set or collation. You should contact IPS Technical Support for further assistance.

Now.. If I were to change the config-file again, to not include $INFO['sql_charset'] = 'utf8' and reload the page, it would work again, if I then would include it again, it would still work. As described in the earlier post.

I'm not sure whether this error is triggered by the absense of any visitors or just the passing of time...

Noone encountered this one or could have a clue about the cause and how to fix it? We really hit a wall with this one :/

I mean.. since all tables was converted, I can't see a reason why it should have a problem with the settings-table.

[joelle]

Once again it fails, after I've not visited it since my last post.. You can see the error here: http://diskusjon.test.hw.no/

or read it:


Now.. If I were to change the config-file again, to not include $INFO['sql_charset'] = 'utf8' and reload the page, it would work again, if I then would include it again, it would still work. As described in the earlier post.

I'm not sure whether this error is triggered by the absense of any visitors or just the passing of time...

Noone encountered this one or could have a clue about the cause and how to fix it? We really hit a wall with this one :/

I mean.. since all tables was converted, I can't see a reason why it should have a problem with the settings-table.

It seems like I have a similar problem because I get different results from issuing the "show variables like 'char%';" command depending on if I used this command from ACP or within phpmyadmin.

My tables are all converted from latin1 to utf8 but when I try to enter utf-8 within the ACP in "document character set", special characters within the forum are being displayed by a black diamond and a white question mark within it. But when I use the "iso-8859-1" syntax within the "document character set", the special characters are displayed as they should even though all my tables are in utf8.

So, did you manage to solve your problem (which seems to be the same as mine)?

[TSP]

First some silly questions:

You have defined $INFO['sql_charset'] = 'utf8' in conf.global.php ?

Check your setting: Document character set

But you might want to try this immediately, since I believe it would fix your issue:

1. Go to System -> Tools & Settings -> Cache Management

2. Click Rebuild Global Caches Cache

3. Click Recache All

4. You might also need to recache your templates, but I don't think you should need to.

I believe this is what turned out to be the fix for me. (Although we have not yet converted to utf-8)

[joelle]

When adding $INFO['sql_charset'] = 'utf8'; to conf.global.php I am getting a fatal database error. After removing it the fatal error is gone.

The document character set says UTF-8 and I have made sure to recache everything at the cache management. I also recached the templates.

But still, special characters e.g. ä,ö,ü are being displayed like this �.

[TSP]

Then I don't really know what it could be.

But you should check your cache-folder or the SQL error logs from your ACP and tell what error you're getting.