MariaDB 10.6: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED

[ruifung]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Steps to reproduce

On my MariaDB installation where I have page compression enabled by default (https://mariadb.com/kb/en/innodb-page-compression/) which the official documentation recommends on the page for COMPRESSED row format (https://mariadb.com/kb/en/innodb-compressed-row-format/).

While page compression is enabled by default, DB migrations that create new tables fail as ROW_FORMAT=COMPRESSED can not be used with a page compressed table. While this is primarily a database configuration matter, is it possible to override the default create options specified at (https://github.com/nextcloud/server/blob/905e1918d2796b9a79025283cd6edf2c40f49d77/lib/private/DB/ConnectionFactory.php#L228) from the nextcloud configuration?

See https://github.com/nextcloud/mail/issues/4443

[kesselb]

from the nextcloud configuration?

No.

If you want to implement this feature yourself: A few lines earlier is the code to read mysql.utf8mb4. Together with an update to https://github.com/nextcloud/server/blob/master/config/config.sample.php#L1513 that's more or less it. We may have to update some tests. We can look at that if the CI runs your pull request.

[ruifung]

That being said, I think that it shouldn't be defaulting to compressed especially since according to (https://mariadb.com/kb/en/innodb-compressed-row-format/)

From MariaDB 10.6.0, tables that are of the COMPRESSED row format are read-only by default. This is the first step towards removing write support and deprecating the feature.

[iangilfillan]

To follow the progress of deprecating/removing this feature on MariaDB's issue tracker, see https://jira.mariadb.org/browse/MDEV-22367

[Tsaukpaetra]

+1 on this. Every time I update NextCloud I have to alter the source code with the above change from kesselb https://github.com/nextcloud/server/pull/25474#issuecomment-773134487 .

In my opinion, trying to force the row_format when creating tables seems to be unnecessary; the conversion script to enable utfmb4 should instead try to alter the global variable innodb_default_row_format and just warn if/when it fails. Thence, every table created automatically gets that row_format applied (something that is attempted to be done manually anyways), and if for whatever reason it is not able to, the creation logic does not fail due to having an invalid option specified.

Easy fix for everyone by default and no need to expose willy-nilly options for users to break.

[GieltjE]

This fix should get some priority, as MariaDB 10.6.3 has been declared stable and marks tables with COMPRESSION as read only: https://mariadb.com/docs/reference/mdb/system-variables/innodb_read_only_compressed/

[pbek]

Today the mariadb:latest docker image got bumped to 10.6.3 and broke my Nextcloud 21. :fearful: Meanwhile I opened https://github.com/nextcloud/docker/issues/1536 and created https://github.com/nextcloud/docker/pull/1537.

[hankjordan]

Spent all day debugging this exact problem with a very cryptic error "read only" error message. +1.

In the example docker-compose.yml as seen in https://github.com/nextcloud/docker mariadb:latest is used. As mariadb:latest is now >= 10.6.3, this breaks Nextcloud by default as stated above.

This means that as of right now the official docker compose file is no longer working.

This issue should be elevated to "needs triage" and the label changed to "bug". As row_format => compression is now deprecated, the default setting in ConnectionFactory.php should be changed to dynamic.

A workaround is available, however. Launch your mariadb instance with the --skip-innodb-read-only-compressed setting.

This workaround is applied in the compose file as shown:

services:
  db:
    image: mariadb
    restart: always
    command: --transaction-isolation=READ-COMMITTED --binlog-format=ROW --skip-innodb-read-only-compressed
    volumes:
      - db:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_PASSWORD=
      - MYSQL_DATABASE=nextcloud
      - MYSQL_USER=nextcloud

Or, you can lock the mariadb version to 10.5 as officially suggested:

services:
  db:
    image: mariadb:10.5
    restart: always
    command: --transaction-isolation=READ-COMMITTED --binlog-format=ROW
    volumes:
      - db:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_PASSWORD=
      - MYSQL_DATABASE=nextcloud
      - MYSQL_USER=nextcloud

[pbek]

https://github.com/nextcloud/docker/pull/1537 would also work until there is a better solution.

[ax-meyer]

This should get higher priority. After a restart, my docker automatically pulled the images for Nextcloud 22 and mariadb 10.6.3, and got completely unusable afterwards. Every page request ended in an errror. Log showed an error with the carmararawprewviews plugin - and only when I tried deactivating this through occ, I got a hint on the mariadb problem and found this issue.

The --skip-innodb-read-only-compressed fix works, but it is a really bad experience as a user. An upgrade on the stable channel, with the recommended docker-file breaks an instance completely and the first error / hint found does not point at the actual error.

This is not just an enhancement, as it is labeled now, but a (critical) bug.

[My1]

This is not just an enhancement, as it is labeled now, but a (critical) bug.

completely agree. our Nextcloud got shot too, and well the error message really is something one cannot just easily point out to be a deprecation. (although that's likely on MariaDB).

honestly I wonder how this could have gone considering the deprecation has been known for almost 3 months as of @iangilfillan's comment.

[markuman]

All proxysql users can handle this error - more or less - easily : https://github.com/nextcloud/server/issues/16448#issuecomment-512749219

but an upstream fix would be more helpful.
just disable the compression. and maybe partition the table when you're afraid it becomes to large.

[nursoda]

just disable the compression. and maybe partition the table when you're afraid it becomes to large.

Can't https://mariadb.com/kb/en/innodb-page-compression/ be used instead as suggested by MariaDB?

[GieltjE]

@nursoda MySQL 5.6 (2013) supports DYNAMIC row format and 5.7 (2015) supports page compression the situation doesn't seem to differ between MariaDB and MySQL.

[markuman]

just disable the compression. and maybe partition the table when you're afraid it becomes to large.

Can't https://mariadb.com/kb/en/innodb-page-compression/ be used instead as suggested by MariaDB?

sure.
but compression is always compromise between performance (de/compress time) and storage usage.

My private nextcloud instance has 288 tables, and all are compressed nowadays.

mysqldump -h 127.1 --single-transaction --skip-comments --skip-extended-insert --no-data  nextcloud | grep -i compressed | wc -l
288

I think for small to medium nextcloud setups, compression is not necessary and useless.
Our buisness nextcloud setup has ~300 users and exists now since 2014. The entire database is 1.2GB. That is almost nothing for a database.

An option to turn it on/off would be awesome.

[GieltjE]

@markuman As DYNAMIC is supported by both MariaDB and MySQL, also page compression is supported by both. The documentation (https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html) shows no difference between DYNAMIC and COMPRESSED row formats. This implies that we should be able to switch to DYNAMIC and leave the page compression setting to the server administrator (recovery might be easier without it?).

[kesselb]

Nextcloud does not support MariaDB 10.6 for now. The reason is the compressed row format.

A possible solution is:

• To stick with MariaDB 10.5
• --innodb-read-only-compressed=OFF to make compressed tables writeable again.

It's on the roadmap. A solution requires a migration of the row format to something different. Don't expect this migration for Nextcloud 22 / 23.

[Tsaukpaetra]

what I find particularly funny is that, were it not for the utf8mb4 option, there would be no issue as this is (to my knowledge) the literal only time it gets set on tables, and that omitting it would have saved everyone headaches...

On Tue, Jul 20, 2021, 1:41 AM kesselb @.***> wrote:

Nextcloud does not support MariaDB 10.6 for now. The reason is the compressed row format.

A possible solution is:

• To stick with MariaDB 10.5
• --innodb-read-only-compressed=OFF to make compressed tables writeable again.

It's on the roadmap. A solution requires a migration of the row format to something different. Don't expect this migration for Nextcloud 22 / 23.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/nextcloud/server/issues/25436#issuecomment-883213001, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAPFTQAAKSEEZV3XG4OVE3TTYUZCPANCNFSM4W6UY4DA .

[My1]

Nextcloud does not support MariaDB 10.6 for now. The reason is the compressed row format.

A possible solution is:

• To stick with MariaDB 10.5
• --innodb-read-only-compressed=OFF to make compressed tables writeable again.

Is there a reason why it even uses that compressed in the first place rather than just not using compression at all?

It's on the roadmap. A solution requires a migration of the row format to something different. Don't expect this migration for Nextcloud 22 / 23.

okay, then at least it should be properly marked in the docs and maybe also get a special highlight on the download page.

[came88]

Nextcloud does not support MariaDB 10.6 for now.

In the my understanding of the word supported that's not true: official documentation says:

Database: MySQL 8.0+ or MariaDB 10.2+ (recommended)

and MariaDB 10.6.3 is a stable release of 10.2+ so is supported (and recommended).

As a user when I read supported I expect a configuration that mostly works and when does not work is because of a bug.

I understand that MariaDB 10.6 in the standard configuration is technically incompatible with Nextcloud and that the resolution of this incompatibility may not be easy and may not be done in a short time frame, but as long as the official documentation does not change this should be marked as a bug.

Moreover, if the official documentation does not change at all I think that this should be marked as a critical bug, as it's make the application unusable at all.

If the documentation is being changed just now please just ignore my comments...

[My1]

In the my understanding of the word supported that's not true: [official documentation]

well they havent updated that yet and they deferred the entire analysis of the issue "till 10.6 gets stable", and now it's kinda chaos, as it is stable and in docker. their docker already pinned it and the docs have an open PR for this. https://github.com/nextcloud/documentation/pull/6972

at least currently nextcloud wont work with a normally configged 10.6 due to the deprecation of the compressed rows.

[AlexG-78]

Also this Issue #28036. It was closed but the issue still exist. Also a discussion in the nextcloud forum https://help.nextcloud.com/t/an-exception-occurred-while-executing-a-query-sqlstate-42000-syntax-error-or-access-violation-1064/120240

[mikefarmer01]

Workaround --skip-innodb-read-only-compressed for MariaDB instance started with systemd:

1. Create file /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf with the following content:

   [Service]
   Environment="MYSQLD_OPTS=--skip-innodb-read-only-compressed"

2. Execute systemctl daemon-reload

3. Restart mariadb.service

For further information, check output of systemctl cat mariadb

[alerque]

[Service]
Environment="MYSQLD_OPTS=--skip-innodb-read-only-compressed"

That syntax might work but it seems like kind of the long way around. You can just add a section like this to the conf file as well to achieve the same thing:

[mariadb-10.6]
innodb_read_only_compressed=OFF

[My1]

[Service]
Environment="MYSQLD_OPTS=--skip-innodb-read-only-compressed"

That syntax might work but it seems like kind of the long way around. You can just add a section like this to the conf file as well to achieve the same thing:

[mariadb-10.6]
innodb_read_only_compressed=OFF

depends on your setup. on docker you likely dont have much control over the config file

[alerque]

depends on your setup. on docker you likely dont have much control over the config file

The post I was responding to was manifestly not about Docker, it was a systemd config file for a local system service where the environment variable was being added by the service. In that case adjusting the conf file should be just as much an option.

For managing Docker containers of course different steps will be necessary.

[dr-m]

I am the author of the InnoDB ROW_FORMAT=COMPRESSED. I just came across this ticket, and I have now linked it in MDEV-23497.

[assen-totin]

Wow, what a long thread for such a simple thing. How come Nextcloud decided that setting the ROW_FORMAT is a good idea at the first place?! This should never be set. It is up to the DBA to decide which particular row format he wants to use. In fact, even the storage engine should not be set to InnoDB - what if I want to run with another one (e.g., MyRocks is ACID compliant too and gives me much better compression)?

Yes, there is a "workaround" to set innodb_read_only_compressed to "off"... but, first, this does not fix the actual defect (stop telling me which DB engine and row format to use on my database server) and, second, it will only last you few months... because in MariaDB 10.7 support for COMPRESSED will be removed entirely. So, I can only recommend the Nextcloud devs to fix this the proper way - stop doing stupid hings in your DDL statements.

Just 2 cents from a MariaDB dev.

[kesselb]

Hi @dr-m, @assen-totin :wave:

Thanks for stepping by. COMPRESSED ROW_FORMAT has been introduced by https://github.com/owncloud/core/pull/17978 back at ownCloud. I'm not familiar with all the details but it looks related to emojis :sunglasses:

I guess the default row format dynamic is capable of storing emojis? Do you have some recommendations for us how to migrate the existing tables?

[GieltjE]

The compression doesn't have anything to do with the collation, just how it's stored on disk. Collation wise utf8mb4_bin seems to be the default now, which we should not touch. This appears to be the important change in the mentioned git issue.

Don't think we should allow users switching between storage engines, they might be acid compliant, but there usualy may be some silent breaking features (e.g. https://www.percona.com/doc/percona-server/5.7/myrocks/limitations.html) that might cause immediate or issues in the long run.

Long ago when we switched from MyISAM to InnoDB there were some sneaky isolation issues that took quite some time to debug.

As mentioned the newer page compression could be used (https://mariadb.com/kb/en/innodb-page-compression/), but I don't think it should be enforced, maybe mentioned in the manual with some proper test results for storage savings and performance impact. Personaly I don't like anything that modifies the data on disk, it's just another layer that can cause corruption and make recovery harder for those without a proper continuous replication.

[assen-totin]

Hey, @kesselb,

As @GieltjE already explained, emojis are innocent. You can store them safely in any character set (even "latin1") - but if you need to process them inside the database (i.e. do inside the DB any string-related operations that rely on character count and not on byte count), then you need (on MariaDB) the utf8mb4 character set. It suffices to have it on the columns where emojis will go. (On a side node, MariaDB is moving from having utf8 as "main" UTF-8 character set towards having utf8mb4 - eventually they will even be renamed, so utf8mb4 will become just "utf8" and what is now utf8 will become something like "utf8mb3"; but that still lies ahead of us.)

To convert existing tables it is enough to do "ALTER TABLE xyz ROW_FORMAT=dynamic". Dynamic is the default format since MariaDB 10.2 (4 years ago) and is stable and performant. On bigger tables this conversion may take some time (in my perspective, this is a table above 1 GB of data or above 1-10 million rows).

To prevent this error from biting users when installing or upgrading, simply remove the "ROW_FORMAT=compressed" from your DDL statements. MariaDB always has a default InnoDB row type set and if the user has a particular reason, he may even change it (although with "compressed" being effectively gone, I see very little reason to go back to any of the other two, "compact" or "redundant").

[AlexAndBear]

@assen-totin Thank you for clarification, what do you expect if row_format is already dynamic for the specific table, running ALTER TABLE xyz ROW_FORMAT=dynamic again on a big table, does this take some time or will be executed in no time, as there is no conversion necessary?

[GieltjE]

@assen-totin Thank you for clarification, what do you expect if row_format is already dynamic for the specific table, running ALTER TABLE xyz ROW_FORMAT=dynamic again on a big table, does this take some time or will be executed in no time, as there is no conversion necessary?

When running an alter statement it will be rewritten, this should be avoided.

Keep in mind that we do not need to specify the row format (https://mariadb.com/kb/en/innodb-system-variables/#innodb_default_row_format), as the chosen row format should not alter any of the innodb behaviour. In theorie whe shouldn't even need to specify a storage engine (https://mariadb.com/kb/en/server-system-variables/#default_storage_engine), but I would highly recommend we do.

As I've stated before deviating from InnoDB to some (not all) other storage engine's should not cause issues, but might cause very hard to trace/reproduce issues. So we should default to InnoDB (or whatever the current default is in the future) as that's being tested by everyone and should be most stable.

In my opinion it would be best to make the row format (and possibly storage engine) configurable with a default to InnoDB - Dynamic, and detect if they aren't properly set to those values. And only if they aren't properly set in the DB run the update commands.

Keep in mind that I don't know much about PostgreSQL, this would need to be addressed in a similar fashion by someone more knowledgable about it.

[assen-totin]

Hi, Jan,

Yes, running ALTER TABLE... ROW_FORMAT=dynamic will rewrite the tablespace even if the row format is already dynamic.

You can easily avoid this by getting only the list of tables that are not with dynamic format and belong to your schema, like this:

select TABLE_NAME from information_schema.TABLES where ENGINE='InnoDB' and ROW_FORMAT != 'Dynamic' and TABLE_SCHEMA='my_nextcloud_database_name';

[leon1995]

For everyone where all tables in the nextcloud db had the ROW_FORMAT==Compressed and dont want to change every one by one (for me there were 219 tables), here is a little script that I copied from here and adapted it in order to work with my docker instance

#!/bin/bash

DB="the name of the nextcloud db"
CONTAINER="the name of the database container"
PW="password of the root user"

PW="--password=""$PW"

echo $DB
echo "USE $DB; SHOW TABLES;" | docker-compose exec -T $CONTAINER mysql -s "$PW" | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE $TABLE ROW_FORMAT=Dynamic;" | docker-compose exec -T $CONTAINER mysql "$PW" $DB
    done
)

[Aaron-Ritter]

I have a few questions: All of these are temporary fixes, what's the idea on a permanent fix, is this something which can run in an minor upgrade automatically?

Could I apply ROW_FORMAT=Dynamic in my MariaDB 10.5 and then go to the latest build?

By the way if I run this now in my current installation what's the chance that an upgrade will screw up things again?

[GieltjE]

I have a few questions: All of these are temporary fixes, what's the idea on a permanent fix, is this something which can run in an minor upgrade automatically?

There need to be a few patches, nothing to mayor, but the above mentioned are not "temporary"

Could I apply ROW_FORMAT=Dynamic in my MariaDB 10.5 and then go to the latest build? By the way if I run this now in my current installation what's the chance that an upgrade will screw up things again?

Upgrading Nextcloud will reset them to COMPRESSED and break everything untill you manually reset them all to DYNAMIC or modify the MySQL/MariaDB config to allow it for now (will work untill 10.7 arrives).

[AlexAndBear]

@Aaron-Ritter It is necessary, that Nextcloud does not enforce a row_format, which might be handeled by the connectionFactory.

The problem, if you convert the current tables, without a proper fix, it might work as long no new tables gets created, which happens for example if you install a new app.

I would rather use MariaDB 10.5 until Nextcloud official supports 10.6 and upgrade later.

[Aaron-Ritter]

I would rather use MariaDB 10.5 until Nextcloud official supports 10.6 and upgrade later.

Thats exactly what we are doing for now :) thanks for the clarifications

[markuman]

Could I apply ROW_FORMAT=Dynamic in my MariaDB 10.5 and then go to the latest build? By the way if I run this now in my current installation what's the chance that an upgrade will screw up things again?

Upgrading Nextcloud will reset them to COMPRESSED and break everything untill you manually reset them all to DYNAMIC or modify the MySQL/MariaDB config to allow it for now (will work untill 10.7 arrives).

When you got proxysql between nextcloud and mariadb, you can rewrite the create statements on-the-fly

    - name: ansible proxysql configuration to remove row format compressed
      proxysql_query_rules:
        login_user: "{{ proxy_sql_admin_user }}"
        login_password: "{{ proxy_sql_admnextcloud 16 fixin_password }}"
        match_digest: "CREATE.*"
        match_pattern: "ROW_FORMAT=compressed"
        replace_pattern: " "
        destination_hostgroup: "{{ hostgroup_master }}"
        active: 1
        retries: 3
        state: present
        load_to_runtime: True

[myke666]

if it can help : i've used mariadb 10.6 since 3 month now without issue with nextcloud 21.x but by thweaking those vars in my_init.cnf (note that you need to comment some vars about barracuda if you use it)

(but got a little one with nextcloud 22.x )

innodb_compression_level = 9 innodb_compression_algorithm = zlib

innodb_file_format = 'Barracuda'

innodb_large_prefix = 1

innodb_file_format_max = 'Barracuda'

innodb_file_per_table = 1 innodb_read_only_compressed = off

[My1]

@myke666 well read only compressed is going to be axed in 10.7, in fact the entire support for compressed is going to be removed. so that is not a tweak that will be standing very long

[GieltjE]

In 10.7 they are moving to a pure plugin based compression system: https://mariadb.com/kb/en/compression-plugins/

Which in my opinion is a good move, the storage engine shouldn't have anything to do with a compression algorithm. Do one thing and do one thing well.

[dr-m]

@My1 No decision has been made yet regarding removing ROW_FORMAT=COMPRESSED support. I think that there already was a feature freeze of MariaDB 10.7, and nothing was changed in this respect.

I recently learned that page_compressed may be risky due to Linux kernel bugs. Also, there may be differences between file systems: some can punch more holes than others. @GieltjE my ROW_FORMAT=COMPRESSED implementation always used only zlib; it is deeply tied with some zlib features (scatter/gather type invocation).

[myke666]

Interesting ! Thanks for your comments.

[waja]

@My1 No decision has been made yet regarding removing ROW_FORMAT=COMPRESSED support. I think that there already was a feature freeze of MariaDB 10.7, and nothing was changed in this respect.

Which just means, that this may just be delayed a little more until this will be removed. So nothing changed here in the end.

[dr-m]

I updated MDEV-22367 with an idea how write support for ROW_FORMAT=COMPRESSED could be retained even when the buffer pool code was simplified.

[obel1x]

so everyone changing the "allow write" for compressed tables will most likely fail at mariadb 10.7, right? Why is nextcloud even forcing this row_format the hard way coded in /lib/private/DB/ConnectionFactory.php? can't we just remove that line and let the db-maintainers take care when to set the default row_format in their database by setting the default rowtype by themselves?

what bad things would happen, if we just remove that line 231?

For me, today i stranded doing an app- update, after i converted all tables to dynamic with mariadb 10.6 last server update - only while that line was forcing the "wrong" row_type for my database. Going back to mariadb 10.5 is no solution for me, as it would not solve, but only delay that problem...

[My1]

Going back to mariadb 10.5 is no solution for me, as it would not solve, but only delay that problem...

^- this. It will be supported till 2025 but still this isnt ideal.

[Tsaukpaetra]

In my opinion there should be effectively no impact of removing the affecting line from the source. Anecdotally I've been using it that way for the better part of a year plus (due to other issues) so it's totally fine. The original idea behind it is not an issue anymore (I forget even why it was like that except as an attempt to keep all row data in the index for faster access maybe?) and keeping it is clearly a no-go, so the resolution seems clear to me.

[GieltjE]

There is no reason to not fix this, the ROW_FORMAT shouldn't have been enforced in the first place (granted it was a owncloud commit).

There will always be people running newer versions, I get that that cannot be fully supported. But at the very least when simple bugs like this occur fix them so that by the time the big enterprices upgrade they don't run into this and we can continue testing (and don't have to run 2 databases just to provide an older version for one service).