CiviCRM Community Forums (archive)

Hi everybody

I'm currently having some troubles with  my Joomla 1.5.20 / CiviCRM 3.2 installation.
I had some issues with collation ( being in Swedish and not in utf8 ) , so I put everything in utf8.

The issue is that when I want to create a new custom field with a name containing any accentuated character (like èéà , as I want to use French )

I get  : (the name intended was : Activité test 1)


DB Error: unknown error

Base de donnée de code d'erreur: Invalid utf8 character string: '�_test_1_5', 1300

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] =>         ALTER TABLE civicrm_value_test_accentued_characteres_1
        ADD COLUMN `activit�_test_1_5` varchar(255) DEFAULT 'Activité test 1' [nativecode=1300 ** Invalid utf8 character string: '�_test_1_5']
    [type] => DB_Error
    [user_info] =>         ALTER TABLE civicrm_value_test_accentued_characteres_1
        ADD COLUMN `activit�_test_1_5` varchar(255) DEFAULT 'Activité test 1' [nativecode=1300 ** Invalid utf8 character string: '�_test_1_5']
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="        ALTER TABLE civicrm_value_test_accentued_characteres_1
        ADD COLUMN `activit�_test_1_5` varchar(255) DEFAULT 'Activité test 1' [nativecode=1300 ** Invalid utf8 character string: '�_test_1_5']"]
)

I don't understand this error as utf8 was designed to take care of accentuated characters !

So if anybody as a clue ...
Thank you   

Rémi
[/code]

[SOLVED]

I had a really hard time to fix this bug.
For the french user, the right collation is utf8_unicode_ci
This is the only way to have proper ordering (including the accentuated characters)

On Mysql the default collation is svedish, so be careful and change it even before installing civicrm.
If it's too late, the change will be really painfull.
The only way I found was to install a fresh civicrm with the right collation, then export the both database and use a text editor to change manually the collation in the old database.

Now it works perfectly fine and I can use any character in my custom fields label !

Rémi

It would be safer, but collation changes with localization!

If I trust this :
http://dev.mysql.com/doc/refman/5.1/en/create-database.html

when creating, you should just add

CHARACTER SET [=] charset_name   (I guess UTF-8)
COLLATE [=] collation_name  (for me : utf8_unicode_ci)

Cheers

Rémi