Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Syntax error or access violation: 1118 Row size too large #15648

Open
ovidiub13 opened this issue May 21, 2019 · 9 comments

Comments

Projects
None yet
2 participants
@ovidiub13
Copy link

commented May 21, 2019

Steps to reproduce

  1. Run Nextcloud 16.0.0 from Docker
  2. Get prompt that I need to Enable MySQL 4-byte support and follow guide from https://docs.nextcloud.com/server/16/admin_manual/configuration_database/mysql_4byte_support.html
  3. Last command to run is sudo -u www-data ./occ maintenance:repair.

Expected behaviour

Finish the change collation of all tables.

Actual behaviour

Errors out when changing the Collation of table oc_activity:

    [...]
     - Change row format for oc_notifications_pushtokens ...
     - Change collation for oc_notifications_pushtokens ...
     - Change row format for oc_activity ...
     - Change collation for oc_activity ...

In AbstractMySQLDriver.php line 125:

  An exception occurred while executing 'ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':                                                

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

In PDOStatement.php line 143:

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

In PDOStatement.php line 141:

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

maintenance:repair [--include-expensive]

Server configuration

Operating system:
Linux Running using Docker
Web server:
Nginx
Database:
mariaDB
PHP version:
PHP 7.3.4
Nextcloud version: (see Nextcloud admin page)

# sudo -u www-data ./occ --version         
Nextcloud is in maintenance mode - no apps have been loaded

Nextcloud 16.0.0

Updated from an older Nextcloud/ownCloud or fresh install:
Updated since Nextcloud 11 at least.
Where did you install Nextcloud from:
Docker
Signing status:
I don't know what this is.

Login as admin user into your Nextcloud and access 
http://example.com/index.php/settings/integrity/failed 
paste the results here.

List of activated apps:

App list
# sudo -u www-data php occ app:list
Nextcloud is in maintenance mode - no apps have been loaded

Enabled:
  - accessibility: 1.2.0
  - admin_audit: 1.6.0
  - cloud_federation_api: 0.2.0
  - dav: 1.9.2
  - federatedfilesharing: 1.6.0
  - federation: 1.6.0
  - files: 1.11.0
  - files_external: 1.7.0
  - files_pdfviewer: 1.5.0
  - files_rightclick: 0.13.0
  - files_sharing: 1.8.0
  - files_texteditor: 2.8.0
  - files_versions: 1.9.0
  - files_videoplayer: 1.5.0
  - firstrunwizard: 2.5.0
  - gallery: 18.3.0
  - logreader: 2.1.0
  - lookup_server_connector: 1.4.0
  - nextcloud_announcements: 1.5.0
  - notifications: 2.4.1
  - oauth2: 1.4.2
  - password_policy: 1.6.0
  - privacy: 1.0.0
  - provisioning_api: 1.6.0
  - recommendations: 0.4.0
  - serverinfo: 1.6.0
  - sharebymail: 1.6.0
  - support: 1.0.0
  - survey_client: 1.4.0
  - twofactor_backupcodes: 1.5.0
  - updatenotification: 1.6.0
  - viewer: 1.0.0
  - workflowengine: 1.6.0
Disabled:
  - activity
  - comments
  - encryption
  - files_trashbin
  - systemtags
  - theming
  - user_ldap

Nextcloud configuration:

Config report
# sudo -u www-data php occ config:list system
Nextcloud is in maintenance mode - no apps have been loaded

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "nextcloud.geekaliens.com"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "http:\/\/nextcloud.geekaliens.com",
        "dbtype": "mysql",
        "version": "16.0.0.9",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "logtimezone": "UTC+2",
        "installed": true,
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "ssl",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpauth": 1,
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "loglevel": 2,
        "maintenance": true,
        "theme": "",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "timeout": 0
        },
        "mysql.utf8mb4": true
    }
}

Are you using external storage, if yes which one: local

Are you using encryption: no

Are you using an external user-backend, if yes which one: No

Logs

Nextcloud log (data/nextcloud.log)

Nextcloud log
{"reqId":"LeIM6h7JVxy17xzhQSVw","level":3,"time":"2019-05-21T07:02:01+00:00","remoteAddr":"","user":"--","app":"no app in context","method":"","url":"--","message":{"Exception":"Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing 'ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':\n\nSQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs","Code":0,"Trace":[{"file":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php","line":184,"function":"convertException","class":"Doctrine\\DBAL\\Driver\\AbstractMySQLDriver","type":"->","args":["An exception occurred while executing 'ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':\n\nSQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs",{"errorInfo":["42000",1118,"Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"}]},{"file":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php","line":158,"function":"wrapException","class":"Doctrine\\DBAL\\DBALException","type":"::","args":[{"__class__":"Doctrine\\DBAL\\Driver\\PDOMySql\\Driver"},{"errorInfo":["42000",1118,"Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"},"An exception occurred while executing 'ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':\n\nSQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs"]},{"file":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Statement.php","line":178,"function":"driverExceptionDuringQuery","class":"Doctrine\\DBAL\\DBALException","type":"::","args":[{"__class__":"Doctrine\\DBAL\\Driver\\PDOMySql\\Driver"},{"errorInfo":["42000",1118,"Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs"],"__class__":"Doctrine\\DBAL\\Driver\\PDOException"},"ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;",[]]},{"file":"\/var\/www\/html\/lib\/private\/Repair\/Collation.php","line":100,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"\/var\/www\/html\/lib\/private\/Repair.php","line":100,"function":"run","class":"OC\\Repair\\Collation","type":"->","args":[{"__class__":"OC\\Repair"}]},{"file":"\/var\/www\/html\/core\/Command\/Maintenance\/Repair.php","line":123,"function":"run","class":"OC\\Repair","type":"->","args":[]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Command\/Command.php","line":255,"function":"execute","class":"OC\\Core\\Command\\Maintenance\\Repair","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":901,"function":"run","class":"Symfony\\Component\\Console\\Command\\Command","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":262,"function":"doRunCommand","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"OC\\Core\\Command\\Maintenance\\Repair"},{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":145,"function":"doRun","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/lib\/private\/Console\/Application.php","line":213,"function":"run","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/console.php","line":97,"function":"run","class":"OC\\Console\\Application","type":"->","args":[]},{"file":"\/var\/www\/html\/occ","line":11,"args":["\/var\/www\/html\/console.php"],"function":"require_once"}],"File":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/AbstractMySQLDriver.php","Line":125,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDOException","Message":"SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs","Code":"42000","Trace":[{"file":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Statement.php","line":169,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDOStatement","type":"->","args":[null]},{"file":"\/var\/www\/html\/lib\/private\/Repair\/Collation.php","line":100,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"\/var\/www\/html\/lib\/private\/Repair.php","line":100,"function":"run","class":"OC\\Repair\\Collation","type":"->","args":[{"__class__":"OC\\Repair"}]},{"file":"\/var\/www\/html\/core\/Command\/Maintenance\/Repair.php","line":123,"function":"run","class":"OC\\Repair","type":"->","args":[]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Command\/Command.php","line":255,"function":"execute","class":"OC\\Core\\Command\\Maintenance\\Repair","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":901,"function":"run","class":"Symfony\\Component\\Console\\Command\\Command","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":262,"function":"doRunCommand","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"OC\\Core\\Command\\Maintenance\\Repair"},{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":145,"function":"doRun","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/lib\/private\/Console\/Application.php","line":213,"function":"run","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/console.php","line":97,"function":"run","class":"OC\\Console\\Application","type":"->","args":[]},{"file":"\/var\/www\/html\/occ","line":11,"args":["\/var\/www\/html\/console.php"],"function":"require_once"}],"File":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOStatement.php","Line":143,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs","Code":"42000","Trace":[{"file":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOStatement.php","line":141,"function":"execute","class":"PDOStatement","type":"->","args":[null]},{"file":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Statement.php","line":169,"function":"execute","class":"Doctrine\\DBAL\\Driver\\PDOStatement","type":"->","args":[null]},{"file":"\/var\/www\/html\/lib\/private\/Repair\/Collation.php","line":100,"function":"execute","class":"Doctrine\\DBAL\\Statement","type":"->","args":[]},{"file":"\/var\/www\/html\/lib\/private\/Repair.php","line":100,"function":"run","class":"OC\\Repair\\Collation","type":"->","args":[{"__class__":"OC\\Repair"}]},{"file":"\/var\/www\/html\/core\/Command\/Maintenance\/Repair.php","line":123,"function":"run","class":"OC\\Repair","type":"->","args":[]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Command\/Command.php","line":255,"function":"execute","class":"OC\\Core\\Command\\Maintenance\\Repair","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":901,"function":"run","class":"Symfony\\Component\\Console\\Command\\Command","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":262,"function":"doRunCommand","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"OC\\Core\\Command\\Maintenance\\Repair"},{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/3rdparty\/symfony\/console\/Application.php","line":145,"function":"doRun","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/lib\/private\/Console\/Application.php","line":213,"function":"run","class":"Symfony\\Component\\Console\\Application","type":"->","args":[{"__class__":"Symfony\\Component\\Console\\Input\\ArgvInput"},{"__class__":"Symfony\\Component\\Console\\Output\\ConsoleOutput"}]},{"file":"\/var\/www\/html\/console.php","line":97,"function":"run","class":"OC\\Console\\Application","type":"->","args":[]},{"file":"\/var\/www\/html\/occ","line":11,"args":["\/var\/www\/html\/console.php"],"function":"require_once"}],"File":"\/var\/www\/html\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOStatement.php","Line":141}},"CustomMessage":"--"},"userAgent":"--","version":"16.0.0.9"}
@ovidiub13

This comment has been minimized.

Copy link
Author

commented May 21, 2019

I've tried running the SQL query manually:

MariaDB [nextcloud]> ALTER TABLE `oc_activity` CONVERT TO CHARACT  
    ->   ER SET utf8mb4 COLLATE utf8mb4_bin;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHARACT  
  ER SET utf8mb4 COLLATE utf8mb4_bin' at line 1
MariaDB [nextcloud]> ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

The table seems to have most columns set to VARCHAR:

MariaDB [nextcloud]> SHOW COLUMNS FROM oc_activity;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| activity_id   | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| timestamp     | int(11)       | NO   | MUL | 0       |                |
| priority      | int(11)       | NO   |     | 0       |                |
| type          | varchar(255)  | YES  |     | NULL    |                |
| user          | varchar(64)   | YES  |     | NULL    |                |
| affecteduser  | varchar(64)   | NO   | MUL | NULL    |                |
| app           | varchar(255)  | NO   |     | NULL    |                |
| subject       | varchar(255)  | NO   |     | NULL    |                |
| subjectparams | varchar(4000) | NO   |     | NULL    |                |
| message       | varchar(255)  | YES  |     | NULL    |                |
| messageparams | varchar(4000) | YES  |     | NULL    |                |
| file          | varchar(4000) | YES  |     | NULL    |                |
| link          | varchar(4000) | YES  |     | NULL    |                |
| object_type   | varchar(255)  | YES  |     | NULL    |                |
| object_id     | bigint(20)    | NO   |     | 0       |                |
+---------------+---------------+------+-----+---------+----------------+
15 rows in set (0.017 sec)

I'm not sure which rows should I manually change to TEXT. Any advice?

Meanwhile, my instance is stuck in maintenance mode. I can't use my system. I'm not sure how to revert this besides restoring a backup.

@HigH-HawK

This comment has been minimized.

Copy link

commented May 21, 2019

Hi ovidiub13

What version of MariaDB are you using? Since your last post suggests taht you got SSH access, you can just enter mysql -V.

Depending on what version you are using (I guess it's below 10.2), there may be a configuration issue.

Connect to your Database as you did in your last post and issue the following command and post your result:

SELECT @@GLOBAL.innodb_large_prefix, @@GLOBAL.innodb_file_format;

Mine for example is:

MariaDB [nextcloud]> SELECT @@GLOBAL.innodb_large_prefix, @@GLOBAL.innodb_file_format;
+------------------------------+-----------------------------+
| @@GLOBAL.innodb_large_prefix | @@GLOBAL.innodb_file_format |
+------------------------------+-----------------------------+
| true                         | barracuda                   |
+------------------------------+-----------------------------+
1 row in set (0.000 sec)
@ovidiub13

This comment has been minimized.

Copy link
Author

commented May 21, 2019

MySql version:

# mysql --version
mysql  Ver 15.1 Distrib 10.3.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
MariaDB [(none)]> SELECT @@GLOBAL.innodb_large_prefix, @@GLOBAL.innodb_file_format;
+------------------------------+-----------------------------+
| @@GLOBAL.innodb_large_prefix | @@GLOBAL.innodb_file_format |
+------------------------------+-----------------------------+
| NULL                         | NULL                        |
+------------------------------+-----------------------------+
1 row in set (0.005 sec)
@HigH-HawK

This comment has been minimized.

Copy link

commented May 21, 2019

Ok, I would suggest setting these two variables to what mine are (mentioned in my post):

MariaDB [(none)]> use nextcloud;
MariaDB [nextcloud]> set global innodb_large_prefix=on;
MariaDB [nextcloud]> set global innodb_file_format=Barracuda; 

Sine your config contains the variable "mysql.utf8mb4": true, I would suggest a repair via OCC. The repair notices the variable in your config and will start converting your tables/columns automatically.

$ sudo -u www-data php /path/to/your/nextcloud/main/folder/occ maintenance:repair

If that shouldn't work, you can do the conversion manually like so:

MariaDB [(none)]>  SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") AS AlterSQL
FROM information_schema.TABLES WHERE TABLE_SCHEMA = "nextcloud";

This will create a list of ALTER statements, which you can use to convert your tables.

List example:

+--------------------------------------------------------------------------------------------------------------------+
| AlterSQL                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------+
| ALTER TABLE `nextcloud`.`oc_talk_guests` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;              |
| ALTER TABLE `nextcloud`.`oc_login_flow_v2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;            |
| ALTER TABLE `nextcloud`.`oc_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                    |
| ALTER TABLE `nextcloud`.`oc_storages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                 |
| ALTER TABLE `nextcloud`.`oc_mimetypes` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                |
| ALTER TABLE `nextcloud`.`oc_talk_rooms` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;               |
| ALTER TABLE `nextcloud`.`oc_addressbooks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;             |
| ALTER TABLE `nextcloud`.`oc_comments_read_markers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;    |
| ALTER TABLE `nextcloud`.`oc_cards` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                    |
| ALTER TABLE `nextcloud`.`oc_calendarobjects` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;          |
| ALTER TABLE `nextcloud`.`oc_credentials` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;              |
| ALTER TABLE `nextcloud`.`oc_calendar_rooms` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;           |
| ALTER TABLE `nextcloud`.`oc_file_locks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;               |
| ALTER TABLE `nextcloud`.`oc_files_trash` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;              |
| ALTER TABLE `nextcloud`.`oc_activity_mq` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;              |
| ALTER TABLE `nextcloud`.`oc_flow_checks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;              |
| ALTER TABLE `nextcloud`.`oc_mounts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                   |
| ALTER TABLE `nextcloud`.`oc_filecache` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                |
| ALTER TABLE `nextcloud`.`oc_notifications` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;            |
| ALTER TABLE `nextcloud`.`oc_appconfig` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                |
| ALTER TABLE `nextcloud`.`oc_vcategory` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                |
| ALTER TABLE `nextcloud`.`oc_dav_shares` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;               |
| ALTER TABLE `nextcloud`.`oc_group_admin` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;              |
| ALTER TABLE `nextcloud`.`oc_oauth2_clients` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;           |
| ALTER TABLE `nextcloud`.`oc_comments` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                 |
| ALTER TABLE `nextcloud`.`oc_systemtag` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                |
| ALTER TABLE `nextcloud`.`oc_group_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;               |
| ALTER TABLE `nextcloud`.`oc_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                   |
| ALTER TABLE `nextcloud`.`oc_weather_city` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;             |
| ALTER TABLE `nextcloud`.`oc_authtoken` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                |
| ALTER TABLE `nextcloud`.`oc_weather_config` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;           |
| ALTER TABLE `nextcloud`.`oc_migrations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;               |
| ALTER TABLE `nextcloud`.`oc_calendarobjects_props` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;    |
| ALTER TABLE `nextcloud`.`oc_properties` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;               |
| ALTER TABLE `nextcloud`.`oc_share` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                    |
| ALTER TABLE `nextcloud`.`oc_calendars` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                |
| ALTER TABLE `nextcloud`.`oc_jobs` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                     |
| ALTER TABLE `nextcloud`.`oc_directlink` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;               |
| ALTER TABLE `nextcloud`.`oc_preferences` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;              |
| ALTER TABLE `nextcloud`.`oc_accounts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                 |
| ALTER TABLE `nextcloud`.`oc_whats_new` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                |
| ALTER TABLE `nextcloud`.`oc_notes_meta` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;               |
| ALTER TABLE `nextcloud`.`oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;                 |
| ALTER TABLE `nextcloud`.`oc_user_saml_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;          |
| ALTER TABLE `nextcloud`.`oc_calendarchanges` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;          |
| ALTER TABLE `nextcloud`.`oc_privacy_admins` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;           |
| ALTER TABLE `nextcloud`.`oc_addressbookchanges` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;       |
| ALTER TABLE `nextcloud`.`oc_bruteforce_attempts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;      |
| ALTER TABLE `nextcloud`.`oc_calendar_invitations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;     |
| ALTER TABLE `nextcloud`.`oc_calendar_resources` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;       |
| ALTER TABLE `nextcloud`.`oc_calendarsubscriptions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;    |
| ALTER TABLE `nextcloud`.`oc_cards_properties` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;         |
| ALTER TABLE `nextcloud`.`oc_external_applicable` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;      |
| ALTER TABLE `nextcloud`.`oc_external_config` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;          |
| ALTER TABLE `nextcloud`.`oc_external_mounts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;          |
| ALTER TABLE `nextcloud`.`oc_external_options` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;         |
| ALTER TABLE `nextcloud`.`oc_flow_operations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;          |
| ALTER TABLE `nextcloud`.`oc_systemtag_group` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;          |
| ALTER TABLE `nextcloud`.`oc_systemtag_object_mapping` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; |
| ALTER TABLE `nextcloud`.`oc_talk_participants` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;        |
| ALTER TABLE `nextcloud`.`oc_twofactor_backupcodes` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;    |
| ALTER TABLE `nextcloud`.`oc_twofactor_totp_secrets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;   |
| ALTER TABLE `nextcloud`.`oc_federated_reshares` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;       |
| ALTER TABLE `nextcloud`.`oc_user_saml_auth_token` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;     |
| ALTER TABLE `nextcloud`.`oc_collres_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;      |
| ALTER TABLE `nextcloud`.`oc_collres_resources` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;        |
| ALTER TABLE `nextcloud`.`oc_ldap_group_mapping` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;       |
| ALTER TABLE `nextcloud`.`oc_ldap_group_members` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;       |
| ALTER TABLE `nextcloud`.`oc_ldap_user_mapping` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;        |
| ALTER TABLE `nextcloud`.`oc_notifications_pushtokens` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; |
| ALTER TABLE `nextcloud`.`oc_oauth2_access_tokens` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;     |
| ALTER TABLE `nextcloud`.`oc_schedulingobjects` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;        |
| ALTER TABLE `nextcloud`.`oc_share_external` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;           |
| ALTER TABLE `nextcloud`.`oc_talk_signaling` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;           |
| ALTER TABLE `nextcloud`.`oc_trusted_servers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;          |
| ALTER TABLE `nextcloud`.`oc_twofactor_providers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;      |
| ALTER TABLE `nextcloud`.`oc_vcategory_to_object` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;      |
| ALTER TABLE `nextcloud`.`oc_collres_accesscache` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;      |
+--------------------------------------------------------------------------------------------------------------------+
78 rows in set (0.001 sec)
@ovidiub13

This comment has been minimized.

Copy link
Author

commented May 21, 2019

MariaDB [(none)]> use nextcloud;
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 [nextcloud]> set global innodb_large_prefix=on;
ERROR 1238 (HY000): Variable 'innodb_large_prefix' is a read only variable
MariaDB [nextcloud]> set global innodb_file_format=Barracuda; 
ERROR 1238 (HY000): Variable 'innodb_file_format' is a read only variable

So I can't change those variables like that.

Regarding running the repair.... that's what's triggering my problem. When running the occ:

root@534d19b3404d:/var/www/html# sudo -u www-data ./occ maintenance:repair
Nextcloud is in maintenance mode - no apps have been loaded

 - Repair MySQL collation
     - Change row format for oc_activity ...
     - Change collation for oc_activity ...

In AbstractMySQLDriver.php line 125:

  An exception occurred while executing 'ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':

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

In PDOStatement.php line 143:

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

In PDOStatement.php line 141:

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

maintenance:repair [--include-expensive]
@HigH-HawK

This comment has been minimized.

Copy link

commented May 21, 2019

That's actually weird but could well be that I missed the memo where it says, that these can only be set via the /etc/my.cnf

Try adding the following parameters to your /etc/my.cnf

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
transaction_isolation = READ-COMMITTED
binlog_format = ROW
innodb_file_per_table = 1
innodb_large_prefix=true
innodb_file_format=barracuda

And restart your MySQL/MariaDB

Rehel/EPEL
#: systemctl restart mariadb

Ubuntu/Debian based
#: /etc/init.d/mariadb restart

If you now issue the following command, it should not show NULL anymore but the actual value:

MariaDB [(none)]> use nextcloud;
SELECT @@GLOBAL.innodb_large_prefix, @@GLOBAL.innodb_file_format;
@ovidiub13

This comment has been minimized.

Copy link
Author

commented May 21, 2019

Config set, and I get the values now.

MariaDB [nextcloud]> SELECT @@GLOBAL.innodb_large_prefix, @@GLOBAL.innodb_file_format;
+------------------------------+-----------------------------+
| @@GLOBAL.innodb_large_prefix | @@GLOBAL.innodb_file_format |
+------------------------------+-----------------------------+
| true                         | barracuda                   |
+------------------------------+-----------------------------+
1 row in set (0.000 sec)

But the occ maintenance:repair command still fails with the same error.

@HigH-HawK

This comment has been minimized.

Copy link

commented May 22, 2019

I think there is still an issue with the ROW_FORMAT of your tables preventing the Alter statements to change your Collation.

The ROW_FORMAT can either be COMPRESSED or DYNAMIC and means that InnoDB stores varchar/text/blob fields that don't fit in the page completely off-page.

Now it's up to you what you are going to use, depending on how much data you have and how often this data is being accessed. I went for DYNAMIC instead of COMPRESSED as I don't really have any space issues.

To change the ROW_FORMAT, use the following statement, which will create a set of ALTER statements for you to copy and paste:

SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM information_schema.TABLES WHERE TABLE_SCHEMA = "nextcloud";

Example output:

MariaDB [(none)]> SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM information_schema.TABLES WHERE TABLE_SCHEMA = "nextcloud";
+---------------------------------------------------------------------------+
| MySQLCMD                                                                  |
+---------------------------------------------------------------------------+
| ALTER TABLE `nextcloud`.`oc_talk_guests` ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE `nextcloud`.`oc_login_flow_v2` ROW_FORMAT=DYNAMIC;            |
| ALTER TABLE `nextcloud`.`oc_users` ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE `nextcloud`.`oc_storages` ROW_FORMAT=DYNAMIC;                 |
| ALTER TABLE `nextcloud`.`oc_mimetypes` ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE `nextcloud`.`oc_talk_rooms` ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE `nextcloud`.`oc_addressbooks` ROW_FORMAT=DYNAMIC;             |
| ALTER TABLE `nextcloud`.`oc_comments_read_markers` ROW_FORMAT=DYNAMIC;    |
| ALTER TABLE `nextcloud`.`oc_cards` ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE `nextcloud`.`oc_calendarobjects` ROW_FORMAT=DYNAMIC;          |
| ALTER TABLE `nextcloud`.`oc_credentials` ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE `nextcloud`.`oc_calendar_rooms` ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE `nextcloud`.`oc_file_locks` ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE `nextcloud`.`oc_files_trash` ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE `nextcloud`.`oc_activity_mq` ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE `nextcloud`.`oc_flow_checks` ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE `nextcloud`.`oc_mounts` ROW_FORMAT=DYNAMIC;                   |
| ALTER TABLE `nextcloud`.`oc_filecache` ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE `nextcloud`.`oc_notifications` ROW_FORMAT=DYNAMIC;            |
| ALTER TABLE `nextcloud`.`oc_appconfig` ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE `nextcloud`.`oc_vcategory` ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE `nextcloud`.`oc_dav_shares` ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE `nextcloud`.`oc_group_admin` ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE `nextcloud`.`oc_oauth2_clients` ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE `nextcloud`.`oc_comments` ROW_FORMAT=DYNAMIC;                 |
| ALTER TABLE `nextcloud`.`oc_systemtag` ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE `nextcloud`.`oc_group_user` ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE `nextcloud`.`oc_groups` ROW_FORMAT=DYNAMIC;                   |
| ALTER TABLE `nextcloud`.`oc_weather_city` ROW_FORMAT=DYNAMIC;             |
| ALTER TABLE `nextcloud`.`oc_authtoken` ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE `nextcloud`.`oc_weather_config` ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE `nextcloud`.`oc_migrations` ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE `nextcloud`.`oc_calendarobjects_props` ROW_FORMAT=DYNAMIC;    |
| ALTER TABLE `nextcloud`.`oc_properties` ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE `nextcloud`.`oc_share` ROW_FORMAT=DYNAMIC;                    |
| ALTER TABLE `nextcloud`.`oc_calendars` ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE `nextcloud`.`oc_jobs` ROW_FORMAT=DYNAMIC;                     |
| ALTER TABLE `nextcloud`.`oc_directlink` ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE `nextcloud`.`oc_preferences` ROW_FORMAT=DYNAMIC;              |
| ALTER TABLE `nextcloud`.`oc_accounts` ROW_FORMAT=DYNAMIC;                 |
| ALTER TABLE `nextcloud`.`oc_whats_new` ROW_FORMAT=DYNAMIC;                |
| ALTER TABLE `nextcloud`.`oc_notes_meta` ROW_FORMAT=DYNAMIC;               |
| ALTER TABLE `nextcloud`.`oc_activity` ROW_FORMAT=DYNAMIC;                 |
| ALTER TABLE `nextcloud`.`oc_user_saml_users` ROW_FORMAT=DYNAMIC;          |
| ALTER TABLE `nextcloud`.`oc_calendarchanges` ROW_FORMAT=DYNAMIC;          |
| ALTER TABLE `nextcloud`.`oc_privacy_admins` ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE `nextcloud`.`oc_addressbookchanges` 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_calendarsubscriptions` ROW_FORMAT=DYNAMIC;    |
| ALTER TABLE `nextcloud`.`oc_cards_properties` 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_flow_operations` 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_talk_participants` ROW_FORMAT=DYNAMIC;        |
| ALTER TABLE `nextcloud`.`oc_twofactor_backupcodes` ROW_FORMAT=DYNAMIC;    |
| ALTER TABLE `nextcloud`.`oc_twofactor_totp_secrets` ROW_FORMAT=DYNAMIC;   |
| ALTER TABLE `nextcloud`.`oc_federated_reshares` ROW_FORMAT=DYNAMIC;       |
| ALTER TABLE `nextcloud`.`oc_user_saml_auth_token` ROW_FORMAT=DYNAMIC;     |
| ALTER TABLE `nextcloud`.`oc_collres_collections` ROW_FORMAT=DYNAMIC;      |
| ALTER TABLE `nextcloud`.`oc_collres_resources` ROW_FORMAT=DYNAMIC;        |
| ALTER TABLE `nextcloud`.`oc_ldap_group_mapping` ROW_FORMAT=DYNAMIC;       |
| ALTER TABLE `nextcloud`.`oc_ldap_group_members` ROW_FORMAT=DYNAMIC;       |
| ALTER TABLE `nextcloud`.`oc_ldap_user_mapping` 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_schedulingobjects` ROW_FORMAT=DYNAMIC;        |
| ALTER TABLE `nextcloud`.`oc_share_external` ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE `nextcloud`.`oc_talk_signaling` ROW_FORMAT=DYNAMIC;           |
| ALTER TABLE `nextcloud`.`oc_trusted_servers` ROW_FORMAT=DYNAMIC;          |
| ALTER TABLE `nextcloud`.`oc_twofactor_providers` ROW_FORMAT=DYNAMIC;      |
| ALTER TABLE `nextcloud`.`oc_vcategory_to_object` ROW_FORMAT=DYNAMIC;      |
| ALTER TABLE `nextcloud`.`oc_collres_accesscache` ROW_FORMAT=DYNAMIC;      |
+---------------------------------------------------------------------------+
78 rows in set (0.001 sec)

After executing the set of statements, you should be able to execute the repair command without any issue now.

@ovidiub13

This comment has been minimized.

Copy link
Author

commented May 22, 2019

Changing the ROW_FORMAT to DYNAMIC did not solve the issue.

I've managed to solve this by changing the message and file columns to text instead of varchar(4000).

MariaDB [nextcloud]> SHOW COLUMNS FROM oc_activity;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| activity_id   | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| timestamp     | int(11)       | NO   | MUL | 0       |                |
| priority      | int(11)       | NO   |     | 0       |                |
| type          | varchar(255)  | YES  |     | NULL    |                |
| user          | varchar(64)   | YES  |     | NULL    |                |
| affecteduser  | varchar(64)   | NO   | MUL | NULL    |                |
| app           | varchar(255)  | NO   |     | NULL    |                |
| subject       | varchar(255)  | NO   |     | NULL    |                |
| subjectparams | varchar(4000) | NO   |     | NULL    |                |
| message       | varchar(255)  | YES  |     | NULL    |                |
| messageparams | varchar(4000) | YES  |     | NULL    |                |
| file          | varchar(4000) | YES  |     | NULL    |                |
| link          | varchar(4000) | YES  |     | NULL    |                |
| object_type   | varchar(255)  | YES  |     | NULL    |                |
| object_id     | bigint(20)    | NO   |     | 0       |                |
+---------------+---------------+------+-----+---------+----------------+
15 rows in set (0.001 sec)

MariaDB [nextcloud]> ALTER TABLE oc_activity MODIFY COLUMN message TEXT;
Query OK, 0 rows affected (0.197 sec)              
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [nextcloud]> SHOW COLUMNS FROM oc_activity;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| activity_id   | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| timestamp     | int(11)       | NO   | MUL | 0       |                |
| priority      | int(11)       | NO   |     | 0       |                |
| type          | varchar(255)  | YES  |     | NULL    |                |
| user          | varchar(64)   | YES  |     | NULL    |                |
| affecteduser  | varchar(64)   | NO   | MUL | NULL    |                |
| app           | varchar(255)  | NO   |     | NULL    |                |
| subject       | varchar(255)  | NO   |     | NULL    |                |
| subjectparams | varchar(4000) | NO   |     | NULL    |                |
| message       | text          | YES  |     | NULL    |                |
| messageparams | varchar(4000) | YES  |     | NULL    |                |
| file          | varchar(4000) | YES  |     | NULL    |                |
| link          | varchar(4000) | YES  |     | NULL    |                |
| object_type   | varchar(255)  | YES  |     | NULL    |                |
| object_id     | bigint(20)    | NO   |     | 0       |                |
+---------------+---------------+------+-----+---------+----------------+
15 rows in set (0.006 sec)

MariaDB [nextcloud]> ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [nextcloud]> ALTER TABLE oc_activity MODIFY COLUMN file TEXT;
Query OK, 0 rows affected (0.213 sec)                  
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [nextcloud]> SHOW COLUMNS FROM oc_activity;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| activity_id   | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| timestamp     | int(11)       | NO   | MUL | 0       |                |
| priority      | int(11)       | NO   |     | 0       |                |
| type          | varchar(255)  | YES  |     | NULL    |                |
| user          | varchar(64)   | YES  |     | NULL    |                |
| affecteduser  | varchar(64)   | NO   | MUL | NULL    |                |
| app           | varchar(255)  | NO   |     | NULL    |                |
| subject       | varchar(255)  | NO   |     | NULL    |                |
| subjectparams | varchar(4000) | NO   |     | NULL    |                |
| message       | text          | YES  |     | NULL    |                |
| messageparams | varchar(4000) | YES  |     | NULL    |                |
| file          | text          | YES  |     | NULL    |                |
| link          | varchar(4000) | YES  |     | NULL    |                |
| object_type   | varchar(255)  | YES  |     | NULL    |                |
| object_id     | bigint(20)    | NO   |     | 0       |                |
+---------------+---------------+------+-----+---------+----------------+
15 rows in set (0.004 sec)

MariaDB [nextcloud]> ALTER TABLE `oc_activity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.206 sec)              
Records: 0  Duplicates: 0  Warnings: 0

After this the # sudo -u www-data ./occ maintenance:repair finished successfully.
I've opened this issue in the idea that the occ command should be able to handle this issue, but feel free to close it if there is no intention for it do be able to.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.