nextcloud

Enabling MariaDB 4-byte support

Nextcloud 11 & 12: In order to use Emojis on your Nextcloud server with a MariaDB database, the installation needs to be tweaked a bit. But before applying any changes to your Nextcloud system, backup your database and files first.

I used Nextcloud’s 4-byte support-documentation as a template and added screenshots and comments to it. So let’s start:


  1. Update your Nextcloud server to Nextcloud 11 or later.
  2. Make sure the following InnoDB settings are set on your MariaDB server:

    sudo -s
    vi /etc/mysql/my.cnf

    Check or add the red lines:

    ...
    [mysqld]
    ...
    innodb_large_prefix=true
    innodb_file_format=barracuda
    innodb_file_per_table=1

  3. Restart the MariaDB server in case you changed the configuration in step 2.
    service mysql restart
  4. Figure out whether the file formate was changed to Barracuda:
    mysql -uroot -p
    SELECT NAME, SPACE, FILE_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like "nextcloud%";


    If the file format is “Barracuda” for every single table, nothing is left to do. While testing, all tables’ file format was “Antelope”:

  5. the tables needs to be migrated to “Barracuda” manually, one by one. SQL commands can be created easily, however:
    USE INFORMATION_SCHEMA;
    SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "nextcloud";

    This will return an SQL command for each table in the nextcloud database. The rows can be quickly copied into a text editor, the “|”s replaced and the SQL commands copied back to the MariaDB shell.

    examplarily sql output


    replace the |‘s in your preferred editor


    copy and paste all statements to MariaDB

    If no error appeared all is done and nothing is left to do here. It can be proceded with the step 6.

  6. Change your databases character set and collation:
    ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

  7. Set the mysql.utf8mb4 config to true in your config.php:
    cd /var/www/nextcloud
    sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="true"

  8. Convert all existing tables to the new collation by running the repair step:
    sudo -u www-data php occ maintenance:repair


    Now you should be able to use emojis like ? in your file names, calendar events, comments and many more.

Enjoy your Nextcloud!