Update to next cloud 21.0.2 has get an error

tardar · May 24, 2021, 7:08pm

Steps to reproduce

Start Updater
Run the updater
Error on step9

Expected behaviour

I’d expected a working upgrade to 21.0.2, but through this error this hasn’t happened.

Actual behaviour

I got this error:
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

more detailed:
Update vorbereiten Log-Level auf "debug" gesetzt Wartungsmodus eingeschaltet Reparaturschritt: Repair MySQL collation Reparaturinformation: All tables already have the correct collation -> nothing to do Reparaturschritt: Repair SQLite autoincrement Reparaturschritt: Copy data from accounts table when migrating from ownCloud Reparaturschritt: Drop account terms table when migrating from ownCloud Das Datenbankschema wird aktualisiert Datenbank aktualisiert Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE. Das Update ist fehlgeschlagen. Bitte melden Sie dieses Problem an die Nextcloud Community.

If I try to start the upgrade on the terminal, I get this error:
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE. Update failed

Server configuration

Operating system: Debian 10

Web server: Apache2

Database: mariaDB 10.6.1

PHP version: 8.0

Nextcloud version: updated from 21.0.2 R2 to 21.0.2

Updated from an older Nextcloud/ownCloud or fresh install: updated from RC to final version

**Where did you install Nextcloud from:**I installed with downloaded package from the next cloud servers

List of activated apps:

App list

Nextcloud or one of the apps require upgrade - only a limited number of commands are available You may use your browser or the occ upgrade command to do the upgrade Enabled: - accessibility: 1.7.0 - activity: 2.14.3 - bruteforcesettings: 2.1.0 - cloud_federation_api: 1.4.0 - comments: 1.11.0 - contacts: 3.5.1 - contactsinteraction: 1.2.0 - dashboard: 7.1.0 - dav: 1.17.1 - federatedfilesharing: 1.11.0 - federation: 1.11.0 - files: 1.16.0 - files_pdfviewer: 2.1.0 - files_rightclick: 1.0.0 - files_sharing: 1.13.1 - files_trashbin: 1.11.0 - files_versions: 1.14.0 - files_videoplayer: 1.10.0 - logreader: 2.6.0 - lookup_server_connector: 1.9.0 - oauth2: 1.9.0 - password_policy: 1.11.0 - photos: 1.3.0 - privacy: 1.5.0 - provisioning_api: 1.11.0 - recommendations: 1.0.0 - serverinfo: 1.11.0 - settings: 1.3.0 - sharebymail: 1.11.0 - support: 1.4.0 - survey_client: 1.9.0 - tasks: 0.13.6 - text: 3.2.0 - theming: 1.12.0 - twofactor_backupcodes: 1.10.0 - updatenotification: 1.11.0 - user_status: 1.1.1 - viewer: 1.5.0 - weather_status: 1.1.0 - whiteboard: 0.0.3 - workflowengine: 2.3.0 Disabled: - admin_audit - encryption - files_external - firstrunwizard - nextcloud_announcements - notifications - systemtags - user_ldap

Nextcloud configuration:

Config report

{ "system": { "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "updater.secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "cloud.*.*", "*.*.*.*", "*.*.*.*" ], "debug": true, "datadirectory": "***REMOVED SENSITIVE VALUE***", "dbtype": "mysql", "version": "21.0.2.0", "overwrite.cli.url": "https:\/\/*.*.*", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbport": "", "dbtableprefix": "oc_", "mysql.utf8mb4": true, "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "maintenance": true, "theme": "", "loglevel": 2, "mail_smtpmode": "smtp", "mail_sendmailmode": "smtp", "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_smtpport": "25", "mail_smtpauth": 1, "mail_smtpauthtype": "LOGIN", "mail_from_address": "***REMOVED SENSITIVE VALUE***", "mail_domain": "***REMOVED SENSITIVE VALUE***", "mail_smtpname": "***REMOVED SENSITIVE VALUE***", "mail_smtppassword": "***REMOVED SENSITIVE VALUE***", "overwriteprotocol": "https", "trusted_proxies": "***REMOVED SENSITIVE VALUE***", "updater.release.channel": "stable", "app_install_overwrite": [ "whiteboard" ], "twofactor_enforced": "false", "twofactor_enforced_groups": [], "twofactor_enforced_excluded_groups": [], "default_phone_region": "DE", "memcache.local": "\\OC\\Memcache\\APCu" } }

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

Are you using encryption: no

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

Client configuration

Browser: Safari 14.1

Operating system: macOS 11.3.1

Logs

Nextcloud log (data/nextcloud.log)

Nextcloud log

Thanks if there’s anyone who could help

Have a nice day
Tardar

j-ed · May 25, 2021, 4:57pm

tardar · May 25, 2021, 6:45pm

Do you have a possible solution for me that might work?
Thanks a lot

lethargos · June 10, 2021, 11:32pm

I’ve run into the exact same problem after trying to upgrade to 21.0.02 and changing the mysql character set to utf8mb4.
What I did is the following:

php occ maintenance:mode --on

on the mysql db (maria 10.6.1):

ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

then in the app again:

php occ config:system:set mysql.utf8mb4 --type boolean --value="true"
php occ maintenance:repair
Nextcloud is in maintenance mode - no apps have been loaded

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

In ExceptionConverter.php line 114:

  An exception occurred while executing a query: SQLSTATE[HY000]: General error: 4047 InnoDB refuses
   to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.


In Exception.php line 26:

  SQLSTATE[HY000]: General error: 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or
  KEY_BLOCK_SIZE.


In Statement.php line 82:

  SQLSTATE[HY000]: General error: 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or
  KEY_BLOCK_SIZE.


maintenance:repair [--include-expensive]

This is according to the official instructions here:
https://docs.nextcloud.com/server/21/admin_manual/configuration_database/mysql_4byte_support.html

Any ideas?

j-ed · June 11, 2021, 5:45pm

I think the problem has nothing to do with the utf8mb4 setting but the InnoDB compression setting. I would assume that seting “innodb_compression_default = off” should fix the problem. Check-out the provided link in my previous post.

tardar · June 11, 2021, 6:40pm

Hey,

i changed the compression_default to off, but the error exists after this change, too.

Do you have any more ideas ?

Thanks
Tardar

lethargos · June 13, 2021, 11:44am

You might be right that it isn’t related to changing the encoding, but just like tardar says, I get the same error nonetheless even after disabling the compression.
And I’d have thought that the default compression refers to the any new changes, not to the existing tables that have compression on.

[later edit:]
It seems it was related after all to the enconding.
So what I did was:

docker exec -it --user www-data next-app php occ config:system:set mysql.utf8mb4 --type boolean --value="false"

Then I came across another error:

In ValidatePhoneNumber.php line 64:

  Can not validate phone numbers without `default_phone_region` being set in the config file

I edited config/config.php and added:

  'default_phone_region' => 'NE' # choose some country; the important thing is that you define it.

And then I ran the repair command again and turned off maintaince:

docker exec -it --user www-data next-app php occ maintenance:repair --include-expensive
docker exec -it --user www-data next-app php occ maintenance:mode --off

Now I’m also able to install apps (which threw the same error in the GUI before this)

The only problem I now have is the warning (in settings - overview) that led to all this in the first place:

MySQL is used as database but does not support 4-byte characters. To be able to handle 4-byte characters (like emojis) without issues in filenames or comments for example it is recommended to enable the 4-byte support in MySQL. For further details read

tardar · June 13, 2021, 2:16pm

Hey, i found this one:

You can switch this off and next cloud works again.

Maybe not the best solution, but it works

lethargos · June 13, 2021, 3:43pm

Thanks for the tip.
I just don’t understand the logic though, I don’t understand what’s happening when nextcloud upgrades the application. Does it set the row_format to compressed and then it tried to change them even though they’re read only?
Doesn’t make much sense

GieltjE · July 7, 2021, 12:26pm

As MariaDB 10.6.3 has now been declared stable this could become an issue for a lot of people quickly.

Mitigated it for now by switching all tables to DYNAMIC.

mattdale77 · July 8, 2021, 2:30pm

I have experienced this error on a brand new install of Nextcloud 22 with MariaDB 10.6.2, both in Docker containers, so it’s not just upgrades which cause the error. Nextcloud is not functional at all.

I’m not sure how to deal with it other than using MariaDB 10.5.11 instead. I’ll see if it’s possible to pass the command line options to turn this off to the MariaDB container.

Edit: Swapping to MariaDB 10.5.11 solved the issue for me. Not ideal but it works

Zerout · July 11, 2021, 4:45pm

Is this issue going to be addressed in a new maintenance release of both 21 and 22 branches?

bb77 · July 11, 2021, 6:02pm

One can easy avoid issues like that by not upgrading to new MariaDB, PHP and even new Nextcloud major releases on day one. I have long since moved to a more conservative strategy when it comes to upgrading Nextcloud and it’s main dependencies. This way I was able to avoid many of the problems that came up here in the forums. I am still on NC 21.0.3, MariaDB 10.3 and PHP7.4 on my production instance and I run a seperate instance, where I test new versions of Nextcloud and it’s dependencies before I upgrade my main instance.

Why is that nod ideal? As for MariaDB, each major release is supported for 5 years. And Nextcloud itself always supports at least the two or three latest versions of MariaDB. So there is no reason to upgrade immediately just because there is a new number on it.

pbek · July 13, 2021, 7:35pm

I was hit by this too today since the mariadb:latest docker image got bumped to 10.6.3.
Doing a image: mariadb:10.5 helped!

See: mariadb needs to be locked to 10.5 · Issue #1536 · nextcloud/docker · GitHub

swiesend · July 14, 2021, 8:11am

Thank you guys!

For whatever reason I even had a wrong charset/collation

MariaDB [nextcloud]> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.004 sec)

MariaDB [nextcloud]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+
1 row in set (0.002 sec)

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

MariaDB [nextcloud]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+
1 row in set (0.001 sec)

Without dowgrading to image: mariadb:10.5 or running with command --skip-innodb-read-only-compressed I run also into, when reprairing
docker-compose exec --user www-data app php occ maintenance:repair --include-expensive into:

     - ERROR: An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8E\x82 V...' for column `nextcloud`.`oc_calendarobjects_props`.`value` at row 29

Zageron · July 14, 2021, 8:23am

This resolved my problem, thank you!

Phlogi · July 14, 2021, 8:06pm

set
innodb_read_only_compressed=0
in your mysql config.

Derkades · July 15, 2021, 6:48pm

Add --innodb_read_only_compressed=OFF after the run command or add command: ['--innodb_read_only_compressed=OFF'] to your docker-compose file. Thanks to @tardar for finding this!

MrksHfmn · July 16, 2021, 6:09am

Thank you! Fixed it for me! :>

nextDee · July 16, 2021, 4:21pm

works 4 me. Thank you! @tardar @Derkades