Store filenames with emoticons

You must have a MySQL 5.7-8/MariaDB database with utf8md4 settings

Since this is not a risk-free operation it is necessary to back up the database before proceeding.

Update the configuration of LogicalDOC

First you have to add a couple of parameters to the jdbc connection url of LogicalDOC

1. Shutdown LogicalDOC system service/daemon
2. Locate the file context.properties in <LOGICALDOC_INSTALLATION_DIR>/conf
3. Edit the file context.properties by adding a couple of parameters to the value of key jdbc.url
4. Locate the key jdbc.url and add the parameters characterEncoding=utf8&allowPublicKeyRetrieval=true
   

   e.g.: jdbc.url=jdbc:mysql://localhost:3306/logicaldoc?useSSL=false&characterEncoding=utf8&allowPublicKeyRetrieval=true

5. Save the file and restart LogicalDOC system service/daemon

Update the database schema

Connect to the database using mysql client

mysql -u root -p logicaldoc

Check the status of the charset settings are OK with the query below

 SHOW VARIABLES LIKE 'char%';

MySQL queries to check charset and collation to store emoticons in LogicalDOC filenames

Here you should check that character_set_server is set to utf8mb4
If it is not set to utf8mb4 you must backup the database schema of logicaldoc and change that
For more information utf8mb4 in MySQL Workbench and JDBC

Execute the following SQL statements on the relevant field of the tables: ld_document, ld_version, ld_history

ALTER TABLE
    ld_document
    CHANGE ld_filename ld_filename
    VARCHAR(255)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE
    ld_version
    CHANGE ld_filename ld_filename
    VARCHAR(255)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE
    ld_history
    CHANGE ld_filename ld_filename
    VARCHAR(255)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Add some files with emoticons in LogicalDOC

Now you can upload some files with emoticons from your desktop or just change the filename of a stored document adding an emoticon at the start of the File name

The document explorer in LogicalDOC DMS 8.3.3 showing some files with emoticons

Additional information

• Show and change MySQL default character set
• Show the character set and the collation of your MySQL tables
• How do I see what character set a MySQL database / table / column is?
• How to store Emoji Character in MySQL Database
• utf8mb4 in MySQL Workbench and JDBC