Mélange illégal de collations

Behavior:
When upgrading or using a DocuWare system with a MySQL database, you may encounter errors like those listed below:

DocuWare.Common.Exceptions.DWDBException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

Referencing column 'organizationId' and referenced column 'guid' in foreign key constraint 'dwfk_XXXXXXXXXXXXXXXXXXXXXXXXX' are incompatible. 

The error occurs because the database tables have different collations.

Solution:
To resolve the issue, you will need to convert all tables and databases to the correct supported collation.
This can be done either manually or using the following MySQL scripts.

*** Before making any changes, please ensure that you have a backup of all DocuWare databases. *** 

Step 1: Change the collation of all tables and databases
You can check your current installed MySQL version by executing 
SHOW VARIABLES LIKE "%version%";

Connect to the MySQL server via the MySQL Workbench or a similar tool and log in as a privileged user.
Open a new query window and run the script for your version of MySQL:

Make sure you have selected the dwsystem database as the default schema.
The script contains two sections. The first section is used to create a procedure that can change the collation of all the tables in a given database.
Copy the first section to the query window and execute it. Afterwards verify that the stored procedure exists in the dwsystem database.

The second section, as seen below, needs to be executed once per DocuWare database. To do this, replace the database name in the highlighted places and run all four statements in order for each database.
Always use the section included in the script file above, as the statements below are for demonstration purposes only.
SET @@FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE dwsystem CHARACTER SET = utf8 COLLATE = 'utf8_general_ci';
call UpdateCharsetAndCollation('dwsystem');
SET @@FOREIGN_KEY_CHECKS=1;

Step 2: Modify the my.ini file
*** Back up the my.ini file before proceeding. ***

Once the script has been executed for all databases, you need to modify the my.ini file of the database.
(Default location: C:\Program Files (x86)\DocuWare\Internal Database VX or C:\Program Files\DocuWare\Internal Database VX).

Search for 'collation-server' in the [mysqld] section and replace it with the correct collation for your MySQL version:

Save the my.ini file, then restart the MySQL server.
If the error occurred during an update, you can now go back to the Setup and click Retry.

KBA is applicable to On-Premise Organizations ONLY.