Wrong Characters displayed. Problem with character set?

In all modules of SuiteCRM wrong characters are displayed: e.g. äöüà in spite of äöüß.
Correct characters are shown while field edit, but wrong after save.
Same problem when importing from csv files

I inspected a lot of topics in this forum but I found no solution.
Most likely there is a problem with database characterset.
I tried various adjustments in suiteCRM (admin/locale) and database (phpmyAdmin) but there was no solution up to now.

My actual adjustments:

  • SuiteCRM-version: 7.10.7
  • Installation: on server at german host Strato
  • SuiteCRM (admin/locale): Default Character Set = UTF8
  • Database (using command SHOW VARIABLES LIKE ‘character_set%’ ):
  • character_set_client utf8mb4
  • character_set_connection utf8mb4
  • character_set_database utf8 (I additionally tried latin1)
  • character_set_filesystem binary
  • character_set_results utf8mb4
  • character_set_server latin1
  • character_set_system utf8

Additional Informations

  • I can insert, save and show correct characters manually in database (with phpmyadmin), but afterwards they are not shown correctly in SuiteCRM
  • I have the same SuiteCRM installation with identical adjustment locally on my computer and it works fine
  • I tried english and german language (german language package from diligent)
  • Charactes in german menu items (not fields) are shown correctly
  • On the same server I have a SugarCRM installation (Version 6.5.15) with same adjustments and it works fine

Is there any idea?

What is your database collation? Did you create the database manually? Some tutorials tell you to do that, but it’s much better to let the SuiteCRM installer do that.

In phpMyAdmin, click the database, then “Operations”, at the bottom you will see Collation. I always see the value “utf8_general_ci” here.

Hi pgr,

Installation:
I first asked my provider for a new (empty) database. Then I installed SuiteCRM via “suiteCRM/install.php” using the new database.

Collation:
a) in suiteCRM, admin/local: collution is utf8_general_ci
b) in phpmyadmin: collution was latin1_swedish_ci !!
Oops! I changed to utf8_general_ci. The problem unfortunately persists.
I tried other collusions but without success

If this is a new installation, and you still don’t have any data, I’d try reinstalling, starting with the right collation.

You don’t have to create a blank database, just give SuiteCRM MySQL credentials and it will create everything for you. (Collation is defined at the time the database is created, so this where your problem came from, I suspect).

New Database:
There is no possibility to get a new Database by SuiteCRM.
My provider (Strato) requires first a request for a new database.In the second step an installation program (e.g. suiteCRM/install.php) can use this new database.
As an exception Strato provides an AppWizzard for installation of some popular programs like Joomla or SugarCRM (the old Open Source version)
Unfortunately SuiteCRM is not (yet;) part of it.

Trial with emptySugarCRM Database:
As remarked in a former post of this thread, a SugarCRM installation, generated by Strato’s AppWizzard, does not have the problem with characterset. Therfore I tried to use a SugarCRM database as a basis for a SuiteCRM installation, hoping to get a database with correct parameters:

  • Generation of a SugarCRM-installation with Strato’s AppWizzard (checked: correct characters)
  • Erasure of all tables of the new SugarCRM database
  • Installation of SuiteCRM using this empty database
    Result, however: same problem with wrong characters in all fields

Another idea?
Phpmyadmin indicates all character sets/-collation as “UTF8” except of the parameter “character_set_server” = “latin1”.
Unfortunately I cannot change this parameter, because I’m not the super user of MySql.
Question: Is it possible that this parameter causes the character problem?

New Database:
There is no possibility to get a new Database by SuiteCRM.
My provider (Strato) requires first a request for a new database.In the second step an installation program (e.g. suiteCRM/install.php) can use this new database.
As an exception Strato provides an AppWizzard for installation of some popular programs like Joomla or SugarCRM (the old Open Source version)
Unfortunately SuiteCRM is not (yet;) part of it.

Trial with emptySugarCRM Database:
As remarked in a former post of this thread, a SugarCRM installation, generated by Strato’s AppWizzard, does not have the problem with characterset. Therfore I tried to use a SugarCRM database as a basis for a SuiteCRM installation, hoping to get a database with correct parameters:

  • Generation of a SugarCRM-installation with Strato’s AppWizzard (checked: correct characters)
  • Erasure of all tables of the new SugarCRM database
  • Installation of SuiteCRM using this empty database
    Result, however: same problem with wrong characters in all fields

Another idea?
Phpmyadmin indicates all character sets/-collation as “UTF8” except of the parameter “character_set_server” = “latin1”.
Unfortunately I cannot change this parameter, because I’m not the super user of MySql.
Question: Is it possible that this parameter causes the character problem?
However: in my local SuiteCRM installation (with correct characters!) this parameter ist “latin1”, too.

Maybe you could try installing on a different machine and then:

. export the database of the new installation (you may use phpMyAdmin or a linux command
. take a copy of all the files of the new installation

Then proceed in this way:
. open phpMyAdmin in the target machine and clear the target database and set the collation (utf8_general_ci) from phpMyAdmin
. copy all the files that you saved before to the new machine
. in the new machine open config.php and edit the following:

  • site_url
  • host_name
  • db_host_name
  • db_user_name
  • db_password
  • db_name
  • db_type
    . now edit .htaccess in the root folder of SuiteCRM and make sure that the new server folders are referred to correctly (there should be one place with a path to the SuiteCRM folder)
    . reset permissions correctly (if you are on a Linux machine, you may use http://github.com/amariussi/chperms )
    . run SuiteCRM as Admin and perform one or two Admin->Repairs->Quick Repair and Rebuild as well as clear the browser cache (Ctr+F5) and reset permissions again.

You may also check php.ini to make sure that it is using the correct character set.

Now you are ready to test.

In total it should take around 15 minutes.

1 Like

Hi amariussi,
You got it!

The magic word was “Use the database of a correct SuiteCRM installation”. I used the database of my local SuiteCRM installation

I found another, somewhat less extensive way:

  • Export the database of a correct SuiteCRM installation
  • Create an empty database on Strato server
  • Set the collation to “utf8-general-ci”
  • Import the file generated in step 1 into the server db
  • Delete the config.php of an old SuiteCRM installation
    (this was faster than copying all files to server)
  • Run install.php using the new database

Actually I see no problems any more. If anyone appears I willl use your complete proposual.

Perhaps this thread may help some german SuiteCRM user with similiar problems.
Thanks to you and pgr too.

Your provider’s policy isn’t very good, imho. If they have to create the database themselves, they need to let you set the collation when it is created. I’ve heard of tons of restrictions invented by hosting providers, but never of this one.

To complete this thread:

I checked the difference between
a) Database on server with wrong charset, generated by provider Strato
b) Database of my local installation, generated by SuiteCRM installer

In the exported sql files I found

a) in wrong Server database:
CREATE TABLE accounts (
id char(36) COLLATE latin1_german1_ci NOT NULL,
name varchar(150) COLLATE latin1_german1_ci DEFAULT NULL,
… the same in all columns of all tables

b) in correct local database (copied to server):
CREATE TABLE accounts (
id char(36) NOT NULL,
name varchar(150) DEFAULT NULL, …

pgr seems right, saying “provider’s policy isn’t very good”.

Hi Folks,

I face the same issues concerning special chars right now and I followed the nice instructions given above. I double-checked if the collations of my databases are correct and, in fact, the are. I exported the database as well and checked the existance of any not-utf8 collationd and could not find any. So everything should be fine.

However, if I provide special chars, e.g. in the names of contacts, they are stored incorrectly to the database. Something seems to go wrong en route from front-end to database. I also checked locale settings and anything I could come up with. Any suggestions how to solve this?

Thanks and best regards

@hybit what is your version of SuiteCRM?

If you try it in the online demo, does it work well there?

demo.suitecrm.com

1 Like

Hi @pgr,

Thanks for the swift reply. Yes, ít works perfectly well on both the online demo and on my local WSL-based debian server. I have no issue with special chars in any of these systems.

Edit: Maybe also a useful hint: Before exporting the local database and importing it to the online database (as suggested by @berndb) I created a contact with special chars. When browsing the online database with phpMyAdmin the special chars displayed correctly.

Again, what is your version of SuiteCRM?

Have you checked your php.ini for any collation settings? Maybe you can start by going into Admin / Diagnostic, select only phpinfo, run diagnostics, download, unzip, and check your effective codepage/collation settings in PHP (if there are any).

1 Like

Sorry,

it’s the latest one. 7.10.9. I’m going to check on any php settings. Checked config.php already. There it’s utf-8 as it’s supposed to be.

I checked the General MySQL Infos and probably found the root of problems:

MySQLi Version: 5.5.52
MySQLi Host Info: *************.de via TCP/IP
MySQLi Server Info: 5.6.41-log
MySQLi Client Encoding: utf8
MySQL Character Set Settings: character_set_client = utf8, character_set_connection = utf8, character_set_database = utf8, character_set_filesystem = binary, character_set_results = utf8, character_set_server = latin1, character_set_system = utf8

character_set_filesystem = binary <-- is this okay?
character_set_server = latin1 <-- this probably isn’t, right?

Okay, I found it.

The server works on iso-8859-1 by default. I created a php.ini file in my SuiteCRM directory with:
default_charset = “UTF-8”
and it did the job.

Cheers

I am glad you got it working! :slight_smile:

Hello,

I have the same problem. I work with version 7.11.10 and I am also on strato. I have already adjusted the database -> utf8_general_ci. I also created a php.ini file with the value default_charset = “UTF-8” and put it in the directory of SuiteCRM unfortunately without success. Do you have any idea what could be the reason?

Hello,

the following was my solution. In the root directory a php.ini file with the following values must be created.

default_charset = “UTF-8”
[iconv]
iconv.input_encoding = UTF-8
iconv.internal_encoding = UTF-8
iconv.output_encoding = UTF-8
[exif]
exif.encode_unicode = UTF-8
[mssql]
mssql.charset = “UTF-8”

The browser cache must then be cleared.

1 Like