Database Configuration on Linux
Introduction
ownCloud requires a database in which administrative data is stored. The following databases are currently supported:
The MySQL or MariaDB databases are the recommended database engines. |
Requirements
Choosing to use MySQL / MariaDB, PostgreSQL, or Oracle as your database requires that you install and set up the server software first.
Oracle users, see the Oracle Database Configuration guide. |
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 COMMITED
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.
Parameters
For setting up ownCloud to use any database, use the instructions in the Installation Wizard.
You should not have to edit the respective values in the config/config.php
.
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
If you decide to use a MySQL or MariaDB database, ensure the following:
-
That you have installed and enabled the
pdo_mysql
extension in PHP. -
That the
mysql.default_socket
points to the correct socket (if the database runs on the same server as ownCloud).
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.
The PHP configuration in /etc/php/7.4/apache2/conf.d/20-mysql.ini
could look like this:
# configuration for PHP MySQL module
extension=pdo_mysql.so
[mysql]
mysql.allow_local_infile=On
mysql.allow_persistent=On
mysql.cache_size=2000
mysql.max_persistent=-1
mysql.max_links=-1
mysql.default_port=
mysql.default_socket=/var/lib/mysql/mysql.sock # Debian squeeze: /var/run/mysqld/mysqld.sock
mysql.default_host=
mysql.default_user=
mysql.default_password=
mysql.connect_timeout=60
mysql.trace_mode=Off
Now you need to create a database user and the database itself by using the MySQL command-line interface. The database tables will be created by ownCloud when you log in for the first time. To start the MySQL command-line mode use:
mysql -uroot -p;
Then a mysql>
or MariaDB [root]>
prompt will appear.
Now enter the following lines and confirm them with the enter key:
CREATE DATABASE IF NOT EXISTS owncloud;
GRANT ALL PRIVILEGES ON owncloud.*
TO 'username'@'localhost'
IDENTIFIED BY 'password';
You can quit the prompt by entering:
quit;
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/config.php
as created by the installation wizard would therefore contain entries like this:
<?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 configured to use 4-byte Unicode support instead of the default 3-byte. 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 MySQL earlier than version 5.7, then you need to do two things:
-
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
-
Run the following occ command:
./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.
-
PostgreSQL
If you decide to use a PostgreSQL database, make sure that you have installed and enabled the PostgreSQL extension and the PostgreSQL PDO extension in PHP.
The PHP configuration in /etc/php/7.4/apache2/conf.d/20-pgsql.ini
could look like this:
# configuration for PHP PostgreSQL module
extension=pdo_pgsql.so
extension=pgsql.so
[PostgresSQL]
pgsql.allow_persistent = On
pgsql.auto_reset_persistent = Off
pgsql.max_persistent = -1
pgsql.max_links = -1
pgsql.ignore_notice = 0
pgsql.log_notice = 0
The default configuration for PostgreSQL (at least in Ubuntu 14.04) is to use the peer authentication method.
Check |
To start the PostgreSQL command-line mode use:
sudo -u postgres psql -d template1
Then a template1=\#
prompt will appear.
Now enter the following lines - changing <username>
to something appropriate - and confirm them with the enter key:
CREATE USER <username> CREATEDB;
CREATE DATABASE owncloud OWNER <username>;
You can quit the prompt by entering:
\q
An ownCloud instance configured with PostgreSQL contains:
-
The path to the socket on which the database is running as the hostname
-
The system username the PHP process is using
-
An empty password to access it
-
The name of the database
The config/config.php
as created by the Installation Wizard would therefore contain entries like this:
<?php
"dbtype" => "pgsql",
"dbname" => "owncloud",
"dbuser" => "username",
"dbpassword" => "",
"dbhost" => "/var/run/postgresql",
"dbtableprefix" => "oc_",
The host points to the socket that is used to connect to the database.
Using localhost
here will not work if PostgreSQL is configured to use peer authentication.
Also note, that no password is specified, because this authentication method doesn’t use a password.
If you use another authentication method (not peer), you’ll need to use the following steps to get the database setup. First, you need to create a database user and the database itself by using the PostgreSQL command-line interface. The database tables will be created by ownCloud when you log in for the first time. To start the PostgreSQL command-line mode use:
psql -hlocalhost -Upostgres
Then a postgres=\# prompt will appear. Now enter the following lines and confirm them with the enter key:
CREATE USER <username> WITH PASSWORD 'password';
CREATE DATABASE owncloud TEMPLATE template0 ENCODING 'UNICODE';
ALTER DATABASE owncloud OWNER TO <username>;
GRANT ALL PRIVILEGES ON DATABASE owncloud TO <username>;
You can quit the prompt by entering:
\q
An ownCloud instance configured with PostgreSQL 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/config.php
as created by the Installation Wizard 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.
How Can I Find Out If My MySQL/PostgreSQL Server Is Reachable?
To check the server’s network availability, use the ping command on the server’s hostname (db.server.com
in this example):
ping db.server.com
PING db.server.com (ip-address) 56(84) bytes of data.
64 bytes from your-server.local.lan (192.168.1.10): icmp_req=1 ttl=64 time=3.64 ms
64 bytes from your-server.local.lan (192.168.1.10): icmp_req=2 ttl=64 time=0.055 ms
64 bytes from your-server.local.lan (192.168.1.10): icmp_req=3 ttl=64 time=0.062 ms
For a more detailed check whether the access to the database server software itself works correctly, see the next question.
How Can I Find Out If a Created User Can Access a Database?
The easiest way to test if a database can be accessed is by starting the command-line interface:
MySQL
Assuming the database server is installed on the same system you’re running the command from, use:
mysql -uUSERNAME -p
To access a MySQL installation on a different machine, add the -h option with the respective hostname:
mysql -uUSERNAME -p -h HOSTNAME
mysql> SHOW VARIABLES LIKE "version";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.1.67 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> quit
PostgreSQL
Assuming the database server is installed on the same system you’re running the command from, use:
psql -Uusername -downcloud
To access a PostgreSQL installation on a different machine, add the -h
option with the applicable hostname:
psql -Uusername -downcloud -h HOSTNAME
postgres=# SELECT version();
PostgreSQL 8.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.3 20080704 (prerelease), 32-bit
(1 row)
postgres=# \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.