DB_ERROR_1366 & DB_ERROR_1452

caos30 · May 26, 2017, 11:29pm

Hi, i’ve downloaded the version 5.0.2 from here:

https://sourceforge.net/projects/dolibarr/files/Dolibarr%20ERP-CRM/5.0.2/dolibarr-5.0.2.zip/download

I think that it’s the last stable version just now? Am i wrong?

The problem is during install process, in the step when the SQL are executed to create tables and insert some reference data (regions, departments, etc…). I’ve attached here a text document containing the list of errors when trying to insert regions and departments:

DB_ERROR_1366 - INSERT INTO llx_c_regions ( code_region, fk_pays, cheflieu, tncc, nom, active) values ( 10201, 102, NULL, NULL, ‘Αττική’, 1); - Incorrect string value: ‘\xCE\x91\xCF\x84\xCF\x84…’ for column ‘nom’ at row 1

…

DB_ERROR_1452 - insert into llx_c_departements (fk_region, code_departement,cheflieu,tncc,ncc,nom) values ( 1,‘971’,‘97105’,3,‘GUADELOUPE’,‘Guadeloupe’); - Cannot add or update a child row: a foreign key constraint fails (dolibarr.llx_c_departements, CONSTRAINT fk_departements_fk_region FOREIGN KEY (fk_region) REFERENCES llx_c_regions (code_region))

…

Before write here i’ve searched in this forum and i’ve not found NOTHING regarding this ¿?¿? In google i found that this error did exist in some versions 3.X

Please, help. Indeed, i’m using MySQL over Apache/Ubuntu.
Thanks in advance.
Sergi

eldy · May 27, 2017, 4:41pm

Do you have errors with 5.0.2 when doing installation on an empty environment ?

caos30 · May 27, 2017, 6:16pm

Thanks for your attention Eldy. Yes, i’ve installed an empty environment of the 5.02 on my Ubuntu PC (so local machine). Previously i had installed the Github 0.0.0-alpha version, but i deleted it and afterwards i downloaded the 5.0.2 from SourceForge to work with the “last” stable version. So, before to run the installation of 5.0.2 i removed ALL the tables from database (i named it “dolibarr”) from PhpMyAdmin.

I attach now 3 files:
- dump of the errors printed by dolibarr on web browser during installation
- screenshot of tables llx_c_regions and llx_c_departements

Indeed, i’m searching any webpage where to learn about differences about 3.X versions and 5.0.X. I’ve seen that the most of external modules are for 3.X Dolibarr versions, so i suppose that a 3.X installation of Dolibarr is (by now) more completed for a production environment. Am i wrong? What do you recommend to me to have a best Dolibarr running just now.

For example, my customer is on Mexico (i’m catalan living on Mexico), and here the finance and accounting is slightly different to the used in Europe. So, someone else did a module (CONTAB, from Auribox Consulting) for Dolibarr 3.X … so i would need to migrate this module to be able to use it on Dolibarr 5.X And i can suppose that the same thing happen on other external modules. Do you understand me?

Thanks in advance, hopefully you can guide to me.
Cheers,
Sergi

errorDolibarrinstallSQLregionsdepartments.txt (525 KB)

caos30 · May 27, 2017, 6:23pm

I suspect that maybe the SQL execution got interrupted due to odd russian characters… could it possible? certainly i’m not so good in MySQL management. If we look the error message that i shared with you in my firs comment:

DB_ERROR_1366 - INSERT INTO llx_c_regions ( code_region, fk_pays, cheflieu, tncc, nom, active) values ( 10201, 102, NULL, NULL, ‘Αττική’, 1); - Incorrect string value: ‘\xCE\x91\xCF\x84\xCF\x84…’ for column ‘nom’ at row 1

It really say: “Incorrect string value”… ! so this probably interrumped the execution of that SQL file, which insert regions on database and afterwards the departements. What do you think?

If this was the reason of the error, then maybe it could be avoided with one of these ways:
- removing from SQL file the russian queries
- modifying default settings of MySQL server (maybe using PHPMyadmin), like charsets, collation, etc… (i really hate this part, hehehe).

I suppose that the second way is the correct, but sincerely it overpass my SQL abilities.

caos30 · May 27, 2017, 7:51pm

Arggg… silly boy!!!

Eldy, i think that i know the cause of the error: i not stted properly the charset for dolibarr database to utf8 !!!

I’m really sorry for make waste your time…

I will remove now the database and i will create again from zero doing again the installation process. I will confirm to you later the result.

Sergi

caos30 · May 27, 2017, 8:39pm

Effectively, the problem was the charset collation assigned to database. So i removed the first database (from PhpMyAdmin) and added a new one with the same name but collation utf8_unicode_ci, and now the installation went on without errors.

Eldy, thanks for your attention, i only hope that this thread will be helpful for other newbies like me

Cheers,
Sergi

eldy · May 28, 2017, 9:52pm

Thanks for your feedback. This will save us time if we get similar trouble.

futurehousestore · September 24, 2017, 1:28am

I had the same problem and I found the solution.
I have logged into my phpMyAdmin and in console, I had to paste the codes to convert latin1_swedish_ci to utf8_unicode_ci

ALTER TABLE llx_c_regions CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE llx_c_departements CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE llx_c_tva CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE llx_c_forme_juridique CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
that have sorted the issue and installation went smoothly.
there is also a code to convert the whole database but it didn’t work in my case.

[code]
ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;[/code

caos30 · August 26, 2023, 8:26pm

Now, 6 years after… i had a similar problem

Now my problem is that i’ve the database setted to utf8mb4_general_ci (i don’t remember why i choose this, i think that because to be compatible with text fields containing emojis. But it gave me now an error of collation when doing some things in compta module… so i searched on internet (i’m not very expert on MySQL) and i found that emojis are also usable with utf8_unicode_ci , which is the recommended charset for Doli (now).

So, i solved it with this UNIQUE linux terminal command:

DB="my_dolibarr_dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_unicode_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;' ) | mysql "$DB"

And modifying the /htdocs/conf/conf.php file to set:

$dolibarr_main_db_collation='utf8_unicode_ci';

I hope this be helpful to other admin users