🎯 Objective

Adjust the existing PowerFolder database collation from:

utf8mb4_uca1400_ai_ci

to:

utf8mb4_general_ci

This may be required if the target database server or environment does not support UCA 14.0 collations.

⚠️ Important Notes

This procedure requires application downtime
A full backup must be created before starting
Tested with MariaDB 10.x / 11.x
Should only be performed by a database administrator

The issue appears to occur only with MariaDB v11.x.
With MariaDB v10.x, everything seems to work as expected, and the upgrade to PowerFolder Server v26.1.100 can be performed without concerns.

🔧 Step-by-Step Instructions

1️⃣ Stop the Application (Downtime)

Stop the PowerFolder server:

systemctl stop powerfolder

2️⃣ Create a SQL Dump of the Database

mariadb-dump -u powerfolder -p --databases powerfolder > dump_db_powerfolder_backup.sql

Create a working copy:

cp -av dump_db_powerfolder_backup.sql dump_db_powerfolder_col_modified.sql

3️⃣ Replace the Collation in the Dump File

Replace:

COLLATE=utf8mb4_uca1400_ai_ci

with:

COLLATE=utf8mb4_general_ci

Automated replacement:

sed -i 's/COLLATE=utf8mb4_uca1400_ai_ci/COLLATE=utf8mb4_general_ci/g' dump_db_powerfolder_col_modified.sql

4️⃣ Verify the Changes

Show differences:

diff --color dump_db_powerfolder_{backup,col_modified}.sql

Count modified lines:

diff --color dump_db_powerfolder_{backup,col_modified}.sql | wc -l

Note:
Typically, one table modification results in 4 changed lines in the diff output.

5️⃣ Drop the Existing Database

Login to MariaDB and execute:

DROP DATABASE powerfolder;

6️⃣ Create a New Database

CREATE DATABASE powerfolder;

7️⃣ Verify utf8mb4 Support

Run:

STATUS;

Expected output:

Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4

If utf8mb4 is not configured, review your my.cnf server configuration.

8️⃣ Import the Modified Dump

Return to the shell and run:

mariadb -u powerfolder -p --one-database powerfolder < dump_db_powerfolder_col_modified.sql

Wait until the import is fully completed.

9️⃣ Verify the Result

Option 1 – Check Table Status

SHOW TABLE STATUS;

Option 2 – Detailed Verification

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
       COLUMN_NAME,
       COLUMN_TYPE,
       C.CHARACTER_SET_NAME,
       CCSA.COLLATION_NAME,
       ENGINE
  FROM information_schema.TABLES AS T
  JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
  JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
       ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
 WHERE TABLE_SCHEMA=SCHEMA()
   AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
 ORDER BY TABLE_SCHEMA,
          TABLE_NAME,
          COLUMN_NAME,
          CCSA.COLLATION_NAME,
          ENGINE;

Expected result:
All tables and columns use utf8mb4 and utf8mb4_general_ci.

🔟 Restart the Application

systemctl start powerfolder

Changing Database Collation to utf8mb4_general_ci