Server: Enabling MySQL 4-byte support failed (Ubuntu 18.04/mysql)

Created on 8 May 2018 · 7Comments · Source: nextcloud/server

Steps to reproduce

https://docs.nextcloud.com/server/13/admin_manual/configuration_database/mysql_4byte_support.html
sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="true"
sudo -u www-data php occ maintenance:repair

Expected behaviour

no errors

Actual behaviour

sudo -u www-data php occ maintenance:repair
      - Repair MySQL collation
     - Change row format for oc_accounts ...
     - Change collation for oc_accounts ...
     - Change row format for oc_activity ...
     - Change collation for oc_activity ...


  [Doctrine\DBAL\Exception\DriverException]
  An exception occurred while executing 'ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb
  4_bin;':
  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used ta
  ble type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change som
  e columns to TEXT or BLOBs



  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used ta
  ble type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change som
  e columns to TEXT or BLOBs



  [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used ta
  ble type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change som
  e columns to TEXT or BLOBs

I did also:

SELECT NAME, SPACE, FILE_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME like "owncloud%";
+---------------------------------------+-------+-------------+
| NAME                                  | SPACE | FILE_FORMAT |
+---------------------------------------+-------+-------------+
| owncloud/oc_accounts                  |   930 | Barracuda   |
| owncloud/oc_activity                  |   945 | Barracuda   |
| owncloud/oc_activity_mq               |   857 | Barracuda   |
| owncloud/oc_addressbookchanges        |   858 | Barracuda   |
| owncloud/oc_addressbooks              |   859 | Barracuda   |
| owncloud/oc_admin_sections            |   860 | Barracuda   |
| owncloud/oc_admin_settings            |   861 | Barracuda   |
| owncloud/oc_appconfig                 |   862 | Barracuda   |
| owncloud/oc_authtoken                 |   863 | Barracuda   |
| owncloud/oc_bookmarks                 |   864 | Barracuda   |
| owncloud/oc_bookmarks_tags            |   865 | Barracuda   |
| owncloud/oc_bruteforce_attempts       |   866 | Barracuda   |
| owncloud/oc_calendarchanges           |   867 | Barracuda   |
| owncloud/oc_calendarobjects           |   868 | Barracuda   |
| owncloud/oc_calendarobjects_props     |   869 | Barracuda   |
| owncloud/oc_calendars                 |   870 | Barracuda   |
| owncloud/oc_calendarsubscriptions     |   871 | Barracuda   |
| owncloud/oc_cards                     |   872 | Barracuda   |
| owncloud/oc_cards_properties          |   873 | Barracuda   |
| owncloud/oc_comments                  |   874 | Barracuda   |
| owncloud/oc_comments_read_markers     |   875 | Barracuda   |
| owncloud/oc_contacts_cards_properties |   876 | Barracuda   |
| owncloud/oc_credentials               |   877 | Barracuda   |
| owncloud/oc_dav_properties            |   878 | Barracuda   |
| owncloud/oc_dav_shares                |   879 | Barracuda   |
| owncloud/oc_documents_invite          |   880 | Barracuda   |
| owncloud/oc_documents_member          |   881 | Barracuda   |
| owncloud/oc_documents_op              |   882 | Barracuda   |
| owncloud/oc_documents_revisions       |   883 | Barracuda   |
| owncloud/oc_documents_session         |   884 | Barracuda   |
| owncloud/oc_e2e_encryption_lock       |   885 | Barracuda   |
| owncloud/oc_external_applicable       |   886 | Barracuda   |
| owncloud/oc_external_config           |   887 | Barracuda   |
| owncloud/oc_external_mounts           |   888 | Barracuda   |
| owncloud/oc_external_options          |   889 | Barracuda   |
| owncloud/oc_federated_reshares        |   890 | Barracuda   |
| owncloud/oc_file_locks                |   891 | Barracuda   |
| owncloud/oc_filecache                 |   892 | Barracuda   |
| owncloud/oc_files_trash               |   893 | Barracuda   |
| owncloud/oc_flow_checks               |   894 | Barracuda   |
| owncloud/oc_flow_operations           |   895 | Barracuda   |
| owncloud/oc_group_admin               |   896 | Barracuda   |
| owncloud/oc_group_user                |   897 | Barracuda   |
| owncloud/oc_groups                    |   898 | Barracuda   |
| owncloud/oc_jobs                      |   899 | Barracuda   |
| owncloud/oc_lucene_status             |   900 | Barracuda   |
| owncloud/oc_migrations                |   901 | Barracuda   |
| owncloud/oc_mimetypes                 |   902 | Barracuda   |
| owncloud/oc_mounts                    |   903 | Barracuda   |
| owncloud/oc_notes_meta                |   904 | Barracuda   |
| owncloud/oc_notifications             |   905 | Barracuda   |
| owncloud/oc_notifications_pushtokens  |   906 | Barracuda   |
| owncloud/oc_oauth2_access_tokens      |   907 | Barracuda   |
| owncloud/oc_oauth2_clients            |   908 | Barracuda   |
| owncloud/oc_personal_sections         |   909 | Barracuda   |
| owncloud/oc_personal_settings         |   910 | Barracuda   |
| owncloud/oc_preferences               |   911 | Barracuda   |
| owncloud/oc_privatedata               |   912 | Barracuda   |
| owncloud/oc_properties                |   913 | Barracuda   |
| owncloud/oc_schedulingobjects         |   914 | Barracuda   |
| owncloud/oc_share                     |   915 | Barracuda   |
| owncloud/oc_share_external            |   916 | Barracuda   |
| owncloud/oc_storages                  |   917 | Barracuda   |
| owncloud/oc_systemtag                 |   918 | Barracuda   |
| owncloud/oc_systemtag_group           |   919 | Barracuda   |
| owncloud/oc_systemtag_object_mapping  |   920 | Barracuda   |
| owncloud/oc_trusted_servers           |   921 | Barracuda   |
| owncloud/oc_twofactor_backupcodes     |   922 | Barracuda   |
| owncloud/oc_users                     |   923 | Barracuda   |
| owncloud/oc_vcategory                 |   924 | Barracuda   |
| owncloud/oc_vcategory_to_object       |   925 | Barracuda   |
+---------------------------------------+-------+-------------+

so File Format is as expected

Activity App was not installed, I installed it and it updated to v2.6.1
tryed again.. failed.
I did something that something was added to Activity and tryed again..
failed.

Now it did:
sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="false"
sudo -u www-data php occ maintenance:repair
and it passes.

Do I have to revert:
[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=1

ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
??
Or can I leave it to run Nextcloud without problems ? Ive no idea about Databases ;)

Server configuration

Operating system:
Ubuntu Server 18.04
Web server:
Apache 2.4
Database:
mysql
PHP version:
7.2
Nextcloud version: (see Nextcloud admin page)
13.0.2
Updated from an older Nextcloud/ownCloud or fresh install:
ownCloud 9.x
Where did you install Nextcloud from:
updater

Signing status:

bug activity and notification

Source

LarsBel

All 7 comments

cc @nickvergessen

MorrisJobke on 8 May 2018

Any ideas ?

LarsBel on 17 May 2018

ALTER TABLE oc_activity MODIFY activity_id bigint(20) AUTO_INCREMENT;
ALTER TABLE oc_activity MODIFY subjectparams longtext;
ALTER TABLE oc_activity MODIFY messageparams longtext;
ALTER TABLE oc_activity MODIFY object_id bigint(20);

that helps that the repair passes.. but no clue if it breaks something..

Thanks goes to https://github.com/owncloud/activity/issues/609

LarsBel on 25 May 2018

👍1

Hey, this issue has been closed because the label stale is set and there were no updates for 14 days. Feel free to reopen this issue if you deem it appropriate.

(This is an automated comment from GitMate.io.)

nextcloud-bot on 10 Jul 2018

@nickvergessen Does this make sense?

MorrisJobke on 10 Jul 2018

Well bigint migration is there since a long time:
https://github.com/nextcloud/activity/blob/master/lib/Migration/Version2006Date20170919095939.php
And with the other fields I never saw anyone having a problem.
In case someone has a problem the solution is above

nickvergessen on 10 Jul 2018

👍1

Thanks to @LarsBel for the solution!

ALTER TABLE oc_activity MODIFY activity_id bigint(20) AUTO_INCREMENT;
ALTER TABLE oc_activity MODIFY subjectparams longtext;
ALTER TABLE oc_activity MODIFY messageparams longtext;
ALTER TABLE oc_activity MODIFY object_id bigint(20);
that helps that the repair passes.. but no clue if it breaks something..

Thanks goes to owncloud/activity#609

Thanks for this solution! After executing this on my MariaDB database (though it did not modify anything, i.e. 0 rows affected), I was able to successfully run the occ maintenance:repair command afterwards.

I think this should also be added to the documentation.