Another "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='"

Using my Dolibarr
, , , ,
1

Hi there,

There have been a lot of posts of this topic and I even made a post myself, the fix back then was the force update:
Illegal mix of collations

Fix:
Error when checking Latest movement - Full List

But now I run into the problem again and this time face flat against the wall like last time, when trying to run the /install/repair.php?force_utf8_on_tables=confirmed nothing happens.

I have now tried to update the tables manually (I am no database expert) but now I get an error that the “specified key is too long”. I am a little uncertain what is wrong now.

Please see this screenshot and appreciate any support again :blush:

Screenshot_1
Screenshot_12145Ă—814 95.7 KB

Screenshot_2
Screenshot_21832Ă—1093 128 KB

Info from the installation Admin Tools page:
Dolibarr current version (Programs) 14.0.4
Initial install version 3.6.1
PHP_OS Linux
Version Linux 2.6.32-954.3.5.lve1.4.86.el6.x86_64 #1 SMP Tue Aug 31 17:08:39 UTC 2021 x86_64

PHP 7.4.23
Version MySQL or MariaDB 5.6.51-cll-lve
Database host localhost
Database charset to store data utf8mb4
Database charset to sort data utf8mb4_unicode_ci

2

I’m as clueless as you but I once had a backup not restoring issue for a long time (few versions)

… my solution may give you some hints…

  1. The config file has the database type as well, it was wrong in my config file.
  2. I changed the collation for the whole database to the correct one and in PHPMyAdmin there was an option to apply the same to all the tables which worked.

…Maybe look into the above two points also. Don’t worry about the error, it just means that the request cannot be action for that reason so no changes have been made.

3

You have the error because you are using utf8mb4 instead of normal utf8

All your tables should be in utf8_unicode_ci

4

I’ve a similar problem now, and 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"

I hope this be helpful to other admin users :grin:

5

Hello,

Perfect !

For non UNIX user, you can use the DOlibarr repair script : /install/repair.php?force_utf8_on_tables=confirmed

1 Like
6

It worked perfect too! :slight_smile:
Thanks!

7

Where do you run this in windows?

8

Hello

Windows or other it’s the same, just have to open your browser and type :
http://my-bieautifull-dolibarr.com/install/repair.php?force_utf8_on_tables=confirmed