Enabling MySQL 4-byte support for Nextcloud on Ubuntu 18.04
mysql -V
mysql Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2Instructions 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=1service 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 MySQL 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 trueConvert 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 codesFInished!
If you use mysqldump make sure to add the --default-character-set=utf8mb4 option.
