Database Configuration on Linux

Introduction

ownCloud requires a database in which administrative data is stored. The following databases are currently supported:

MySQL or MariaDB databases are the recommended database engines.

Requirements

Choosing to use MySQL / MariaDB or PostgreSQL as your database requires, that you install and set up the server software first.

The steps for configuring a third party database are beyond the scope of this document. Please refer to the documentation below, for your database vendor.

MySQL / MariaDB

Enabling Binary Logging

ownCloud is currently using a TRANSACTION_READ_COMMITTED transaction isolation to avoid data loss under high load scenarios (e.g., by using the sync client with many clients/users and many parallel operations). This requires a disabled or correctly configured binary logging when using MySQL or MariaDB. Your system is affected if you see the following in your log file during the installation or update of ownCloud:

An unhandled exception has been thrown: exception `PDOException' with message `SQLSTATE[HY000]: General error: 1665 Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.'

There are two solutions:

  • One is to disable binary logging. Binary logging records all changes to your database, and how long each change took. The purpose of binary logging is to enable replication and to support backup operations.

  • The other is to change the BINLOG_FORMAT = STATEMENT in your database configuration file, or possibly in your database startup script, to BINLOG_FORMAT = MIXED or BINLOG_FORMAT = ROW. See Overview of the Binary Log and The Binary Log for detailed information.

Set READ COMMITTED as the Transaction Isolation Level

As discussed above, ownCloud is using the TRANSACTION_READ_COMMITTED transaction isolation level. Some database configurations are enforcing other transaction isolation levels. To avoid data loss under high load scenarios (e.g., by using the sync client with many clients/users and many parallel operations), you need to configure the transaction isolation level accordingly. Please refer to the MySQL manual for detailed information.

Configuring the Storage Engine

Since ownCloud 7, only InnoDB is supported as a storage engine. Some shared hosts do not support InnoDB and only MyISAM. Running ownCloud in such an environment is not supported.

Parameters

For setting up ownCloud to use any database, use the instructions in Installing With Docker. You should not have to edit the respective values in the ownCloud config. However, in exceptional cases, for example, if you want to connect your ownCloud instance to a database created by a previous installation of ownCloud, some modification might be required.

MySQL / MariaDB

MariaDB is backward compatible with MySQL. All instructions work for both, so you will not need to replace or revise any existing MySQL client commands.

An ownCloud instance configured with MySQL would contain the hostname on which the database is running, a valid username and password to access it, and the name of the database. The config as created by the Installing with Docker procedure will therefore contain entries such as:

<?php

"dbtype"        => "mysql",
"dbname"        => "owncloud",
"dbuser"        => "username",
"dbpassword"    => "password",
"dbhost"        => "localhost",
"dbtableprefix" => "oc_",

Configure MySQL for 4-byte Unicode Support

For supporting such features as emoji, both MySQL (or MariaDB) and ownCloud need to be capable of using 4-byte Unicode instead of the default 3-byte and configured accordingly.

If you are setting up

  • a new ownCloud installation, using version 10.0 or above, and

  • you’re using a minimum MySQL version of 5.7

then you don’t need to do anything, as support is checked during setup and used if available.

However, if

  • you have an existing ownCloud installation that you need to convert to use 4-byte Unicode support
    or

  • you are working with a MySQL version earlier than version 5.7

then you need to do two things:

  1. In your MySQL configuration, add the configuration settings below. If you already have them configured, update them to reflect the values specified:

    [mysqld]
innodb_large_prefix=ON
innodb_file_format=Barracuda
innodb_file_per_table=ON

  2. Run the following occ command:

    docker compose exec owncloud occ db:convert-mysql-charset

    When this is done, tables will be created with:

    • A utf8mb4 character set.

    • A utf8mb4_bin collation.

    • row_format set to compressed.

For more information, please either refer to config.sample.php, or have a read through the following links:

PostgreSQL

An ownCloud instance configured with PostgreSQL will contain the hostname on which the database is running, a valid username and password to access it, and the name of the database. The config as created by Installing With Docker would contain entries like this:

<?php

"dbtype"        => "pgsql",
"dbname"        => "owncloud",
"dbuser"        => "username",
"dbpassword"    => "password",
"dbhost"        => "localhost",
"dbtableprefix" => "oc_",

Troubleshooting

How to Workaround General Error: 2006 MySQL Server Has Gone Away

The database request takes too long, and therefore the MySQL server times out. It’s also possible that the server is dropping a packet that is too large. Please refer to the manual of your database for how to raise the configuration options wait_timeout and/or max_allowed_packet.

Some shared hosts are not allowing access to these config options. For such systems, ownCloud is providing a dbdriveroptions configuration option within your config/config.php where you can pass such options to the database driver. Please refer to the sample PHP configuration parameters for an example.

Useful SQL Commands

Show Database Users

MySQL PostgreSQL

SELECT User,Host FROM mysql.user;

SELECT * FROM pg_user;

Show Available Databases

MySQL PostgreSQL

SHOW DATABASES;

\l

Show ownCloud Tables in Database

MySQL PostgreSQL

USE owncloud; SHOW TABLES;

\c owncloud; \d

Quit Database

MySQL PostgreSQL

quit;

\q

How to Solve Deadlock Errors

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

Explanation

This error occurs when two transactions write and commit to the same rows in separate cluster nodes. Only one of them can successfully commit. The failing one will be aborted. For cluster level aborts, Galera Cluster returns a deadlock error.

Solution

The solution, for Galera Cluster, would be to send all write requests to a single DB node, instead of all of them. Here is a useful guide, when using HAProxy.

The same concept applies when MaxScale is used as a DB proxy. It needs to be configured to send all write requests to a single DB node instead all of them and balance read statements across the rest of the nodes. Here is a useful guide on how to configure MaxScale with Read/Write splitting.

Enabling Causality Checks

Additionally, to solve this issue, when using Galera Cluster, customers should try to set wsrep_sync_wait=1. When enabled, the node triggers causality checks in response to certain types of queries. This is disabled by default.