[TYPO3] UTF8 in MySQL Database with a MySQL client in latin1

[Lars Haendler]

Dominique Feyer wrote:
> Hi everybody, here's my summer trouble, if somebody can help me ... i 
> can send some free beer ;-)
> 
> I have a TYPO3 installation setup in UTF-8, like that:
> 
> $TYPO3_CONF_VARS['SYS']['t3lib_cs_convMethod'] = 'iconv';
> $TYPO3_CONF_VARS['SYS']['t3lib_cs_utils'] = 'iconv';
> $TYPO3_CONF_VARS['BE']['forceCharset'] = 'utf-8';
> 
> Is the TS template
> 
> config.renderCharset = utf-8
> 
> But i forgot to set in localconf.php the line:
> 
> $TYPO3_CONF_VARS['SYS']['setDBinit'] = 'SET NAMES utf8;';

So, you are running a live system all the time and now you changed the 
setDBinit?

> If I understand well the problem, TYPO3 send UTF8 data, but the MySQL 
> client translate it lik if the data was in latin1 to UTF8 (the database 
> use utf8_general_ci as collation). finaly my data are not in UTF8 nor in 
> latin1 ...

No, the problem now occurs because you added SET NAMES utf8. Your former 
configuration without SET NAMES worked fine in itself. The configuration 
was wrong when you liked it to be completly in utf8, but in itself it 
worked. - We had the same thing on one server.

> So my 2 cents question, is how can I translate/convert this DB to have a 
> standard UTF-8 configuration.

We had a similar problem with all our TYPO3 Installs on a certain 
server. Doing a convert on the fly isnt that funny - I would suggest 
leaving SET NAMES out and live with this misconfigured system. As I 
said: it works.

If you want to convert you have to alter the DB character set first. 
Then change collations of the tables (needed for searching). Then 
convert all content in all relevant tables. Truncate caching tables. The 
last part is go through your TypoScript and check if those are also 
converted.

> I have a lots of data inside the database so a manual edition is not 
> possible

I could send you a script I made for this purpose, but it only works if 
dont already have mixed up the encoding. Meaning if you have content in 
your DB that was inserted without SET NAMES utf8 in you localconf and 
content that was insertet with SET NAMES utf8 ... you have a problem.

> Thanks a lots for your help
> 
> Dominique Feyer

Greetings
Lars