[TYPO3] database charset problems

[Sander van Gelderen]

Hello Patrick,

by database I mean the MySql tables of course.

I don't really use the the 1-2-3 installer myself, but I don't think it 
does, it probally relies on the default character set.

The way it works is like this:

- The database-server has a default character set.
- When you create a schema in the database (mysql calls a schema a 
database) you can specify a character set for it or just (implicitely) 
use the default supplied by the db-server.
- when you create a table in the schema, the same thing applies, you 
either specify a character set for the table, or the schema-default is 
used, and if that has not been specified the db-server default is used.


So when you start a fresh TYPO3 installation, when you create the schema 
(database) you should supply the character set (utf-8) to use. After 
that, don't worry anymore, if the 1-2-3- installer does not mention 
characet sets, everythinh will be created utf-8.

I don't know how you create the schema/database, you can do it with the 
command line mysql client or use the gui tools from 
http://www.mysql.com/products/tools/
Maybe your isp has a panel where you do it.

Check out http://dev.mysql.com/doc/refman/5.0/en/create-database.html 
for the correct syntax, or if you use the gui tools you must look at one 
of the tabs, 'table properties' or 'advanced' or something like that.

It is interesting to note that utf-8 is a 'wider' character set than 
iso8859 (latin), I mean everything that is in the iso8859 set will fit 
in utf-8, but not the other way around.
As long as your database is utf-8, any character your application stores 
with an insert/update query will be correctly stored and retrieved, even 
if your application uses iso8859. But there is a subtle consequence: if 
you use an 'order by' clause, the iso8859 strings in the utf-8 table may 
not be ordered as you expect.

cheers,

Sander



Patrick Schläpfer schreef:
> Hi there
> And what about the MySQL Tables/DB?
> The scripts in the 1-2-3 installer, do they take care of utf-8.
> My tables in the DB have all as "Kollation" latin_swedish_ci,
> although the MySQL has UTF-8 Unicode
> 
> 
> 
> Am 15.3.2008 22:25 Uhr schrieb "Sander van Gelderen" unter
> <mail at sandervangelderen.nl>:
> 
>> Hello Panagiotis,
>>
>> It is best to have utf8 in your database and in the TYPO3 installation
>>
>> For the DB:
>>
>> - You can find out what character set the db currently uses with
>> phpMyAdmin module; when you enter the module from TYPO3 menu, it will
>> first show you  the list of tables. Notice the 5th column 'collation'.
>> Now click the little house icon top left, it will show you the 'welcome'
>> page for the db. Here you can see what character set the db uses by default.
>>
>>
>> For TYPO3, I use 4 settings:
>>
>> - in your root typoscript template:
>> page.config.renderCharset = utf-8
>>
>> - in the install tool:
>>
>> - enter the following three lines in [SYS][setDBinit]:
>> SET CHARACTER SET utf8;
>> SET NAMES utf8;
>> SET SESSION character_set_server=utf8;
>>
>> - [BE][forceCharset] = utf-8
>>
>> - [SYS][UTF8filesystem] = 1
>> is usefull as well
>>
>>
>>
>> This setup works for me and I have never had a 'gremlin' since I use it.
>>
>> HTH,
>>
>> Sander
>>
>>
>>
>> Panagiotis Kondaxis schreef:
>>> Hello all,
>>>
>>> I 've some charset problems with my database.
>>>
>>> My problem is that when i select data with an application outside from typo3
>>> i get "chinese"-characters.
>>> (for example 'select name from fe_users')
>>>
>>> I've added $TYPO3_CONF_VARS['BE']['forceCharset'] = 'utf-8' in localconf.php.
>>>
>>>
>>> I 've tried ISO 8859-7 (Greek) (I 'm from Greece) with no result.
>>>
>>> I 've also tried to create manually the typo3 database in a new-clean
>>> installation with utf8_general_ci and utf8_greek_ci collation but my problem
>>> remains.
>>>
>>> Any help will be appreciated.
>>> Thanks in advance.
>>>
>>> Regards.
>>> Panagiotis.
>> _______________________________________________
>> TYPO3-english mailing list
>> TYPO3-english at lists.netfielders.de
>> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-english
> 
>