Enabling MySQL 4-byte support for Nextcloud on Ubuntu 18.04

image_print
mysql -V
 mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Instructions for MariaDB 10.1

vi /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=1
service mysqld restart
mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 120
Server version: 10.1.38-MariaDB-0ubuntu0.18.04.2 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Show databases

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| dbispconfig        |
| information_schema |
| mysql              |
| nextcloud          |
| performance_schema |
| phpmyadmin         |
| roundcube          |
+--------------------+
7 rows in set (0.00 sec)

Display file format

MariaDB [(none)]> SELECT NAME, SPACE, FILE_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like "nextcloud%";
+---------------------------------------------+-------+-------------+
| NAME                                        | SPACE | FILE_FORMAT |
+---------------------------------------------+-------+-------------+
| nextcloud/oc_accounts                       |  1364 | Antelope    |
| nextcloud/oc_activity                       |  1365 | Antelope    |
| nextcloud/oc_activity_mq                    |  1366 | Antelope    |
| nextcloud/oc_addressbookchanges             |  1367 | Antelope    |
| nextcloud/oc_addressbooks                   |  1166 | Antelope    |
| nextcloud/oc_appconfig                      |  1169 | Antelope    |
| nextcloud/oc_authtoken                      |  1172 | Antelope    |
| nextcloud/oc_bruteforce_attempts            |  1175 | Antelope    |
| nextcloud/oc_calendar_invitations           |  1178 | Antelope    |
| nextcloud/oc_calendar_resources             |  1181 | Antelope    |
| nextcloud/oc_calendar_rooms                 |  1184 | Antelope    |
| nextcloud/oc_calendarchanges                |  1187 | Antelope    |
| nextcloud/oc_calendarobjects                |  1190 | Antelope    |
| nextcloud/oc_calendarobjects_props          |  1193 | Antelope    |
| nextcloud/oc_calendars                      |  1196 | Antelope    |
| nextcloud/oc_calendarsubscriptions          |  1199 | Antelope    |
| nextcloud/oc_cards                          |  1202 | Antelope    |
| nextcloud/oc_cards_properties               |  1205 | Antelope    |
| nextcloud/oc_comments                       |  1208 | Antelope    |
| nextcloud/oc_comments_read_markers          |  1211 | Antelope    |
| nextcloud/oc_credentials                    |  1214 | Antelope    |
| nextcloud/oc_dav_shares                     |  1217 | Antelope    |
| nextcloud/oc_directlink                     |  1220 | Antelope    |
| nextcloud/oc_external_applicable            |  1223 | Antelope    |
| nextcloud/oc_external_config                |  1226 | Antelope    |
| nextcloud/oc_external_mounts                |  1229 | Antelope    |
| nextcloud/oc_external_options               |  1232 | Antelope    |
| nextcloud/oc_federated_reshares             |  1363 | Antelope    |
| nextcloud/oc_file_locks                     |  1235 | Antelope    |
| nextcloud/oc_filecache                      |  1238 | Antelope    |
| nextcloud/oc_files_trash                    |  1241 | Antelope    |
| nextcloud/oc_flow_checks                    |  1244 | Antelope    |
| nextcloud/oc_flow_operations                |  1247 | Antelope    |
| nextcloud/oc_group_admin                    |  1250 | Antelope    |
| nextcloud/oc_group_user                     |  1253 | Antelope    |
| nextcloud/oc_groups                         |  1256 | Antelope    |
| nextcloud/oc_jobs                           |  1259 | Antelope    |
| nextcloud/oc_migrations                     |  1262 | Antelope    |
| nextcloud/oc_mimetypes                      |  1265 | Antelope    |
| nextcloud/oc_mounts                         |  1268 | Antelope    |
| nextcloud/oc_notifications                  |  1271 | Antelope    |
| nextcloud/oc_notifications_pushtokens       |  1274 | Antelope    |
| nextcloud/oc_oauth2_access_tokens           |  1277 | Antelope    |
| nextcloud/oc_oauth2_clients                 |  1280 | Antelope    |
| nextcloud/oc_preferences                    |  1283 | Antelope    |
| nextcloud/oc_preview_generation             |  1286 | Antelope    |
| nextcloud/oc_properties                     |  1289 | Antelope    |
| nextcloud/oc_richdocuments_assets           |  1292 | Antelope    |
| nextcloud/oc_richdocuments_direct           |  1295 | Antelope    |
| nextcloud/oc_richdocuments_member           |  1298 | Antelope    |
| nextcloud/oc_richdocuments_wopi             |  2723 | Antelope    |
| nextcloud/oc_schedulingobjects              |  1304 | Antelope    |
| nextcloud/oc_sensorlogger_data_types        |  1307 | Antelope    |
| nextcloud/oc_sensorlogger_device_data_types |  1310 | Antelope    |
| nextcloud/oc_sensorlogger_device_groups     |  1313 | Antelope    |
| nextcloud/oc_sensorlogger_device_types      |  1316 | Antelope    |
| nextcloud/oc_sensorlogger_devices           |  1319 | Antelope    |
| nextcloud/oc_sensorlogger_logs              |  1322 | Antelope    |
| nextcloud/oc_share                          |  1325 | Antelope    |
| nextcloud/oc_share_external                 |  1328 | Antelope    |
| nextcloud/oc_storages                       |  1331 | Antelope    |
| nextcloud/oc_systemtag                      |  1334 | Antelope    |
| nextcloud/oc_systemtag_group                |  1337 | Antelope    |
| nextcloud/oc_systemtag_object_mapping       |  1340 | Antelope    |
| nextcloud/oc_trusted_servers                |  1343 | Antelope    |
| nextcloud/oc_twofactor_backupcodes          |  1346 | Antelope    |
| nextcloud/oc_twofactor_providers            |  1349 | Antelope    |
| nextcloud/oc_users                          |  1352 | Antelope    |
| nextcloud/oc_vcategory                      |  1355 | Antelope    |
| nextcloud/oc_vcategory_to_object            |  1358 | Antelope    |
| nextcloud/oc_whats_new                      |  1361 | Antelope    |
+---------------------------------------------+-------+-------------+
71 rows in set (0.00 sec)

File format is Antelope.

Generate SQL migration commands for each table

MariaDB> USE INFORMATION_SCHEMA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [INFORMATION_SCHEMA]>
MariaDB [INFORMATION_SCHEMA]> SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA,".", TABLE_NAME, " ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE TABLE_SCHEMA = "nextcloud";
+-----------------------------------------------------------------------------+
| MySQLCMD                                                                    |
+-----------------------------------------------------------------------------+
| ALTER TABLE nextcloud.oc_accounts ROW_FORMAT=DYNAMIC;                       |
| ALTER TABLE nextcloud.oc_activity ROW_FORMAT=DYNAMIC;                       |
| ALTER TABLE nextcloud.oc_activity_mq ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE nextcloud.oc_addressbookchanges ROW_FORMAT=DYNAMIC;             |
| ALTER TABLE nextcloud.oc_addressbooks ROW_FORMAT=DYNAMIC;                   |
| ALTER TABLE nextcloud.oc_appconfig ROW_FORMAT=DYNAMIC;                      |
| ALTER TABLE nextcloud.oc_authtoken ROW_FORMAT=DYNAMIC;                      |
| ALTER TABLE nextcloud.oc_bruteforce_attempts ROW_FORMAT=DYNAMIC;            |
| ALTER TABLE nextcloud.oc_calendar_invitations ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE nextcloud.oc_calendar_resources ROW_FORMAT=DYNAMIC;             |
| ALTER TABLE nextcloud.oc_calendar_rooms ROW_FORMAT=DYNAMIC;                 |
| ALTER TABLE nextcloud.oc_calendarchanges ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE nextcloud.oc_calendarobjects ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE nextcloud.oc_calendarobjects_props ROW_FORMAT=DYNAMIC;          |
| ALTER TABLE nextcloud.oc_calendars ROW_FORMAT=DYNAMIC;                      |
| ALTER TABLE nextcloud.oc_calendarsubscriptions ROW_FORMAT=DYNAMIC;          |
| ALTER TABLE nextcloud.oc_cards ROW_FORMAT=DYNAMIC;                          |
| ALTER TABLE nextcloud.oc_cards_properties ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE nextcloud.oc_comments ROW_FORMAT=DYNAMIC;                       |
| ALTER TABLE nextcloud.oc_comments_read_markers ROW_FORMAT=DYNAMIC;          |
| ALTER TABLE nextcloud.oc_credentials ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE nextcloud.oc_dav_shares ROW_FORMAT=DYNAMIC;                     |
| ALTER TABLE nextcloud.oc_directlink ROW_FORMAT=DYNAMIC;                     |
| ALTER TABLE nextcloud.oc_external_applicable ROW_FORMAT=DYNAMIC;            |
| ALTER TABLE nextcloud.oc_external_config ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE nextcloud.oc_external_mounts ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE nextcloud.oc_external_options ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE nextcloud.oc_federated_reshares ROW_FORMAT=DYNAMIC;             |
| ALTER TABLE nextcloud.oc_file_locks ROW_FORMAT=DYNAMIC;                     |
| ALTER TABLE nextcloud.oc_filecache ROW_FORMAT=DYNAMIC;                      |
| ALTER TABLE nextcloud.oc_files_trash ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE nextcloud.oc_flow_checks ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE nextcloud.oc_flow_operations ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE nextcloud.oc_group_admin ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE nextcloud.oc_group_user ROW_FORMAT=DYNAMIC;                     |
| ALTER TABLE nextcloud.oc_groups ROW_FORMAT=DYNAMIC;                         |
| ALTER TABLE nextcloud.oc_jobs ROW_FORMAT=DYNAMIC;                           |
| ALTER TABLE nextcloud.oc_migrations ROW_FORMAT=DYNAMIC;                     |
| ALTER TABLE nextcloud.oc_mimetypes ROW_FORMAT=DYNAMIC;                      |
| ALTER TABLE nextcloud.oc_mounts ROW_FORMAT=DYNAMIC;                         |
| ALTER TABLE nextcloud.oc_notifications ROW_FORMAT=DYNAMIC;                  |
| ALTER TABLE nextcloud.oc_notifications_pushtokens ROW_FORMAT=DYNAMIC;       |
| ALTER TABLE nextcloud.oc_oauth2_access_tokens ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE nextcloud.oc_oauth2_clients ROW_FORMAT=DYNAMIC;                 |
| ALTER TABLE nextcloud.oc_preferences ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE nextcloud.oc_preview_generation ROW_FORMAT=DYNAMIC;             |
| ALTER TABLE nextcloud.oc_properties ROW_FORMAT=DYNAMIC;                     |
| ALTER TABLE nextcloud.oc_richdocuments_assets ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE nextcloud.oc_richdocuments_direct ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE nextcloud.oc_richdocuments_member ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE nextcloud.oc_richdocuments_wopi ROW_FORMAT=DYNAMIC;             |
| ALTER TABLE nextcloud.oc_schedulingobjects ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE nextcloud.oc_sensorlogger_data_types ROW_FORMAT=DYNAMIC;        |
| ALTER TABLE nextcloud.oc_sensorlogger_device_data_types ROW_FORMAT=DYNAMIC; |
| ALTER TABLE nextcloud.oc_sensorlogger_device_groups ROW_FORMAT=DYNAMIC;     |
| ALTER TABLE nextcloud.oc_sensorlogger_device_types ROW_FORMAT=DYNAMIC;      |
| ALTER TABLE nextcloud.oc_sensorlogger_devices ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE nextcloud.oc_sensorlogger_logs ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE nextcloud.oc_share ROW_FORMAT=DYNAMIC;                          |
| ALTER TABLE nextcloud.oc_share_external ROW_FORMAT=DYNAMIC;                 |
| ALTER TABLE nextcloud.oc_storages ROW_FORMAT=DYNAMIC;                       |
| ALTER TABLE nextcloud.oc_systemtag ROW_FORMAT=DYNAMIC;                      |
| ALTER TABLE nextcloud.oc_systemtag_group ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE nextcloud.oc_systemtag_object_mapping ROW_FORMAT=DYNAMIC;       |
| ALTER TABLE nextcloud.oc_trusted_servers ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE nextcloud.oc_twofactor_backupcodes ROW_FORMAT=DYNAMIC;          |
| ALTER TABLE nextcloud.oc_twofactor_providers ROW_FORMAT=DYNAMIC;            |
| ALTER TABLE nextcloud.oc_users ROW_FORMAT=DYNAMIC;                          |
| ALTER TABLE nextcloud.oc_vcategory ROW_FORMAT=DYNAMIC;                      |
| ALTER TABLE nextcloud.oc_vcategory_to_object ROW_FORMAT=DYNAMIC;            |
| ALTER TABLE nextcloud.oc_whats_new ROW_FORMAT=DYNAMIC;                      |
+-----------------------------------------------------------------------------+
71 rows in set (0.00 sec)

Copy the output to the editor, remove the | and execute the resulting sql commands in the shell.

File format has changed to Barracuda.

MariaDB [(none)]> SELECT NAME, SPACE, FILE_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like "nextcloud%";
+---------------------------------------------+-------+-------------+
| NAME                                        | SPACE | FILE_FORMAT |
+---------------------------------------------+-------+-------------+
| nextcloud/oc_accounts                       |  2779 | Barracuda   |
| nextcloud/oc_activity                       |  2780 | Barracuda   |
| nextcloud/oc_activity_mq                    |  2781 | Barracuda   |
| nextcloud/oc_addressbookchanges             |  2782 | Barracuda   |
| nextcloud/oc_addressbooks                   |  2783 | Barracuda   |
| nextcloud/oc_appconfig                      |  2784 | Barracuda   |
| nextcloud/oc_authtoken                      |  2785 | Barracuda   |
| nextcloud/oc_bruteforce_attempts            |  2786 | Barracuda   |
| nextcloud/oc_calendar_invitations           |  2787 | Barracuda   |
| nextcloud/oc_calendar_resources             |  2788 | Barracuda   |
| nextcloud/oc_calendar_rooms                 |  2789 | Barracuda   |
| nextcloud/oc_calendarchanges                |  2790 | Barracuda   |
| nextcloud/oc_calendarobjects                |  2791 | Barracuda   |
| nextcloud/oc_calendarobjects_props          |  2792 | Barracuda   |
| nextcloud/oc_calendars                      |  2793 | Barracuda   |
| nextcloud/oc_calendarsubscriptions          |  2794 | Barracuda   |
| nextcloud/oc_cards                          |  2795 | Barracuda   |
| nextcloud/oc_cards_properties               |  2796 | Barracuda   |
| nextcloud/oc_comments                       |  2797 | Barracuda   |
| nextcloud/oc_comments_read_markers          |  2798 | Barracuda   |
| nextcloud/oc_credentials                    |  2799 | Barracuda   |
| nextcloud/oc_dav_shares                     |  2800 | Barracuda   |
| nextcloud/oc_directlink                     |  2801 | Barracuda   |
| nextcloud/oc_external_applicable            |  2802 | Barracuda   |
| nextcloud/oc_external_config                |  2803 | Barracuda   |
| nextcloud/oc_external_mounts                |  2804 | Barracuda   |
| nextcloud/oc_external_options               |  2805 | Barracuda   |
| nextcloud/oc_federated_reshares             |  2806 | Barracuda   |
| nextcloud/oc_file_locks                     |  2807 | Barracuda   |
| nextcloud/oc_filecache                      |  2808 | Barracuda   |
| nextcloud/oc_files_trash                    |  2809 | Barracuda   |
| nextcloud/oc_flow_checks                    |  2810 | Barracuda   |
| nextcloud/oc_flow_operations                |  2811 | Barracuda   |
| nextcloud/oc_group_admin                    |  2812 | Barracuda   |
| nextcloud/oc_group_user                     |  2813 | Barracuda   |
| nextcloud/oc_groups                         |  2814 | Barracuda   |
| nextcloud/oc_jobs                           |  2815 | Barracuda   |
| nextcloud/oc_migrations                     |  2816 | Barracuda   |
| nextcloud/oc_mimetypes                      |  2817 | Barracuda   |
| nextcloud/oc_mounts                         |  2818 | Barracuda   |
| nextcloud/oc_notifications                  |  2819 | Barracuda   |
| nextcloud/oc_notifications_pushtokens       |  2820 | Barracuda   |
| nextcloud/oc_oauth2_access_tokens           |  2821 | Barracuda   |
| nextcloud/oc_oauth2_clients                 |  2822 | Barracuda   |
| nextcloud/oc_preferences                    |  2823 | Barracuda   |
| nextcloud/oc_preview_generation             |  2824 | Barracuda   |
| nextcloud/oc_properties                     |  2825 | Barracuda   |
| nextcloud/oc_richdocuments_assets           |  2826 | Barracuda   |
| nextcloud/oc_richdocuments_direct           |  2827 | Barracuda   |
| nextcloud/oc_richdocuments_member           |  2828 | Barracuda   |
| nextcloud/oc_richdocuments_wopi             |  2829 | Barracuda   |
| nextcloud/oc_schedulingobjects              |  2830 | Barracuda   |
| nextcloud/oc_sensorlogger_data_types        |  2831 | Barracuda   |
| nextcloud/oc_sensorlogger_device_data_types |  2832 | Barracuda   |
| nextcloud/oc_sensorlogger_device_groups     |  2833 | Barracuda   |
| nextcloud/oc_sensorlogger_device_types      |  2834 | Barracuda   |
| nextcloud/oc_sensorlogger_devices           |  2835 | Barracuda   |
| nextcloud/oc_sensorlogger_logs              |  2836 | Barracuda   |
| nextcloud/oc_share                          |  2837 | Barracuda   |
| nextcloud/oc_share_external                 |  2838 | Barracuda   |
| nextcloud/oc_storages                       |  2839 | Barracuda   |
| nextcloud/oc_systemtag                      |  2840 | Barracuda   |
| nextcloud/oc_systemtag_group                |  2841 | Barracuda   |
| nextcloud/oc_systemtag_object_mapping       |  2842 | Barracuda   |
| nextcloud/oc_trusted_servers                |  2843 | Barracuda   |
| nextcloud/oc_twofactor_backupcodes          |  2844 | Barracuda   |
| nextcloud/oc_twofactor_providers            |  2845 | Barracuda   |
| nextcloud/oc_users                          |  2846 | Barracuda   |
| nextcloud/oc_vcategory                      |  2847 | Barracuda   |
| nextcloud/oc_vcategory_to_object            |  2848 | Barracuda   |
| nextcloud/oc_whats_new                      |  2849 | Barracuda   |
+---------------------------------------------+-------+-------------+
71 rows in set (0.00 sec)

Change database character set and collation

MariaDB [(none)]> ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>

Set the mysql.utf8mb4 config to true in config.php

sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="true"
System config value mysql.utf8mb4 set to boolean true

Convert all existing tables to the new collation

sudo -u www-data php occ maintenance:repair
 - Repair MySQL collation
     - All tables already have the correct collation -> nothing to do
 - Repair mime types
 - Clean tags and favorites
     - 0 tags of deleted users have been removed.
     - 0 tags for delete files have been removed.
     - 0 tag entries for deleted tags have been removed.
     - 0 tags with no entries have been removed.
 - Repair invalid shares
 - Remove shares of a users root folder
 - Move .step file of updater to backup location
 - Fix potential broken mount points
     - No mounts updated
 - Repair invalid paths in file cache
 - Add log rotate job
 - Clear frontend caches
     - Image cache cleared
     - SCSS cache cleared
     - JS cache cleared
 - Clear every generated avatar on major updates
 - Add preview background cleanup job
 - Queue a one-time job to cleanup old backups of the updater
 - Repair pending cron jobs
     - No need to repair pending cron jobs.
 - Extract the vcard uid and store it in the db
 - Fix component of birthday calendars
     - 1 birthday calendars updated.
 - Fix broken values of calendar objects
    0 [>---------------------------]
 - Registering building of calendar search index as background job
     - Repair step already executed
 - Registering background jobs to update cache for webcal calendars
     - Added 0 background jobs to update webcal calendars
 - Remove activity entries of private events
     - Removed 0 activity entries
 - Fix the share type of guest shares when migrating from ownCloud
 - Copy the share password into the dedicated column
 - Update OAuth token expiration times
 - Add background job to check for backup codes

Finished!

If you use mysqldump make sure to add the --default-character-set=utf8mb4 option.

See also