search

Dolibarr / dolibarr

Dolibarr ERP CRM is a modern software package to manage your company or foundation's activity (contacts, suppliers, invoices, orders, stocks, agenda, accounting, ...). it's an open source Web application (written in PHP) designed for businesses of any sizes, foundations and freelancers.
https://www.dolibarr.org
GNU General Public License v3.0
5.29k stars 2.74k forks source link

Special characters on text fields #23113

Closed valtbern closed 1 year ago

valtbern commented 1 year ago

Bug

Special character letters are stored incorrectly in the DB, particularly multiline (text) fields.

Environment Version

16.0.3

Environment OS

Linux 3.10.0-962.3.2.lve1.5.75.el7.x86_64 # 1 SMP Wed Nov 9 14:38:37 UTC 2022 x86_64

Environment Web server

LiteSpeed

Environment PHP

7.4.33

Environment Database

MySQL or MariaDB 5.5.5-10.3.37-MariaDB-cll-lve

Environment URL(s)

n/a

Expected and actual behavior

Issue is very confusing and repeating itself from version to version. Upon using special characters, like Cyrillic for example, single line fields are storing the letters correctly (in readable manner), however the values of multiline fields become unreadable after saving (see attached, number 1 is the label, number 2 is the description with all the same word, but unreadable). It wouldn't be much of an issue, however certain customers are requiring their services and products be properly described on the invoices. Any suggestion would be very appreciated!

Our conf file:

$dolibarr_main_db_character_set='utf8'; $dolibarr_main_db_collation='utf8_general_ci';

Steps to reproduce the behavior

No response

Attached files

product_charsets

milenmk commented 1 year ago

I've had a similar problem with a few installs, where ALL cyrillic text was stored incorrectly. The problem was from the hosting database server configuration (never could figure it out exactly) and was not related to Dolibarr. I could see the query passes the strings as expected, in cyrillic characters, but once out into the database, they become unreadable. I mean the values in the database are non-readable characters, not the real Cyrillic text.

  1. Can you look through the log file and see how these strings are passed? Maybe ask your hosting to check the log files of the database server?

  2. You can check the collation of the tables in the database and the database itself, not only the config file.

  3. As a final step, I can suggest trying with utf8mb4_general_ci instead of utf8_general_ci

As I have multiple clients and personal installations using the Bulgarian language, I can definitely say that this is not a Dolibarr issue but a server-side issue.

Screenshot_1

valtbern commented 1 year ago

Thanks a lot, Milen for the suggestions! Problem is indeed very similar to what you have described however, that is not really related to the hosting as we have several installations on the same shared hosting account, hereby only one installation is misfunctioning, this being said, whenever I install fresh instance of an ERP within the same account no issues with the characters persist. I also have tried to alter the table collations on the DB level however that has never seemed to resolve the multiline text fields readability, unfortunately.

Post method returns readable result, not the DB update

database_update

text_method_post

milenmk commented 1 year ago

Try changing $dolibarr_main_db_collation='utf8_general_ci'; to $dolibarr_main_db_collation='utf8_unicode_ci';

JoAnMaFe commented 1 year ago

For a similar problem, I work utf8mb4_unicode_ci

valtbern commented 1 year ago

Thank you guys. Tried all of the suggestions, including config and DB collations, however still the texts are not readable...

milenmk commented 1 year ago

Thank you guys. Tried all of the suggestions, including config and DB collations, however still the texts are not readable...

You've tried creating a new record after any change, right? Because changing the collation cannot change what already is stored into the database.

valtbern commented 1 year ago

yes yes, of course, I tried both inserting and updating. See, whenever I overwrite or insert the row directly inside of DB, there are no issues with the text representation, neither from DB nor application perspective. The problem only occurs when I create or overwrite the Cyrillic texts using the application itself.

valtbern commented 1 year ago

The issue is in the global parameters. If you use any special characters within whatever language, avoid using two global settings from https://wiki.dolibarr.org/index.php?title=Setup_Other#Security_Options

Set these two to 0 or remove from "Other setup" completely. That solves an issue with all of the languages we use.

Cheers :)