[TYPO3-english] How can I convert my database to utf8?

[Jörg Klein]

Hi Jigal,

"Jigal van Hemert" <jigal at xs4all.nl> schrieb im Newsbeitrag 
news:mailman.1.1275683794.13451.typo3-english at lists.typo3.org...
>> My problem arose from another setting in 
>> $TYPO3_CONF_VARS['SYS']['setDBinit'].
>> My provider wrote that the following should be set there:
>> $TYPO3_CONF_VARS['SYS']['setDBinit'] = 'SET NAMES utf8;\'.chr(10).\
>> 'SET CHARACTER SET utf8;\'.chr(10).\
>> 'SET SESSION character_set_server=utf8;';
>
> Bottom line is that SET NAMES utf8; sets the correct variables for 
> charsets and collation (it uses the default collation for utf8: 
> utf8_general_ci) to make sure that both the MySQL client (=the functions 
> in PHP), the connection and the result set of a query is in utf-8.

Searching Google showed me that obviously for some configurations SET 
SESSION character_set_server=utf8; is needed.
For me character_set_server already is utf8, so it does not change anything.
But SET CHARACTER SET utf8; makes the special chars being displayed 
incorrectly.

>> For the conversion your code temporarily changes the type of some 
>> columns: char to binary and text to blob.
>> I never did that. Why is that needed?
>
> The temporary changes to binary/blob types (also varchar to varbinary, 
> mediumtext to mediumblob, etc.) is used if there is utf-8 encoded data in 
> for example latin-1 fields.
> (...)
> The total effect is that the already utf-8 encoded data is now seen by 
> MySQL as utf-8 data.

Thank you for the detailed explanation! So in the end the code just changes 
all columns from the old to the new collation. Exactly what I needed.

> You can do this 'conversion' by hand, but doing this for dozens of tables 
> each with several columns to handle is not something I would look forward 
> to.

Right!

Thank you again!

Have a nice Sunday!

Jörg