Difference between revisions of "Store filenames with emoticons"

From LogicalDOC Community Wiki
Jump to navigationJump to search
(Update the database schema)
Line 24: Line 24:
 
[[File:Mysql8-charset-for-emoticons.png|360px|frame|center|MySQL queries to check charset and collation to store emoticons in LogicalDOC filenames]]
 
[[File:Mysql8-charset-for-emoticons.png|360px|frame|center|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<br>
 +
If it is not set to utf8mb4 you must backup the database schema of logicaldoc and change that<br>
 +
For more information [https://stackoverflow.com/questions/44591895/utf8mb4-in-mysql-workbench-and-jdbc utf8mb4 in MySQL Workbench and JDBC]
  
 
Execute the following SQL statements on the relevant field of the tables: ld_document, ld_version, ld_history
 
Execute the following SQL statements on the relevant field of the tables: ld_document, ld_version, ld_history

Revision as of 07:50, 19 September 2019

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;