MySQL/Postgres/SQLite Support¶
ownCloud requires a database where administrative data will be held. Four different database types are currently supported, MySQL / MariaDB, SQLite, PostgreSQL and Oracle. MySQL or MariaDB are the recommended database engines. By default SQLite is chosen because it is a file based database with the least administrative overhead.
Note
Because SQLite handles multiple users very badly SQLite is only recommended for single user ownCloud installations.
Requirements¶
If you decide to use MySQL / MariaDB, PostgreSQL or Oracle, you need to install and set up the server software first. These steps will not be covered by this description as they are easy to find elsewhere.
Parameters¶
Note
For setting up ownCloud to use any of these databases, use the Installation Wizard. You usually don’t need to edit the respective values in the config/config.php, just in special cases e.g. if you want to connect your ownCloud instance to a database created by a previous installation of ownCloud.
MySQL/MariaDB Database¶
If you decide to use a MySQL or MariaDB database make sure that you have installed and enabled the MySQL extension in PHP and that the mysql.default_socket points to the correct socket (if the database runs on same server as ownCloud).
Please note that MariaDB is backwards compatible with MySQL, so all instructions will work for both. You will not need to replace mysql with anything.
The PHP configuration in /etc/php5/conf.d/mysql.ini could look like this:
# configuration for PHP MySQL module
extension=pdo_mysql.so
extension=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 login 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 USER 'username'@'localhost' IDENTIFIED BY 'password';
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_",
SQLite Database¶
If you decide to use a SQLite database make sure that you have installed and enabled the SQLite extension in PHP. The PHP configuration in /etc/php5/conf.d/sqlite3.ini could look like this:
# configuration for PHP SQLite3 module
extension=pdo_sqlite.so
extension=sqlite3.so
It is not necessary to create a database and a database user in advance because this will automatically be done by ownCloud when you login for the first time.
An ownCloud instance configured to use sqlite only needs to contain the reference to a writable data directory (which is required for successful operation of ownCloud in general anyway). The config/config.php as created by the Installation Wizard could therefore contain entries like this:
<?php
"dbtype" => "sqlite",
"dbname" => "owncloud",
"dbuser" => "",
"dbpassword" => "",
"dbhost" => "",
"dbtableprefix" => "",
"datadirectory" => "/var/www/html/owncloud/data",
PostgreSQL Database¶
If you decide to use a PostgreSQL database make sure that you have installed and enabled the PostgreSQL extension in PHP. The PHP configuration in /etc/php5/conf.d/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
Now 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 login for the first time.
To start the postgres 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 therefore contain entries like this:
<?php
"dbtype" => "pgsql",
"dbname" => "owncloud",
"dbuser" => "username",
"dbpassword" => "password",
"dbhost" => "localhost",
"dbtableprefix" => "oc_",
Oracle Database¶
If you are deploying to an Oracle database make sure that you have installed and enabled the Oracle extension in PHP. The PHP configuration in /etc/php5/conf.d/oci8.ini could look like this:
# configuration for PHP Oracle extension
extension=oci8.so
Make sure that the Oracle environment has been set up for the process trying to use the Oracle extension. For a local Oracle XE installation this can be done by exporting the following environment variables (eg. in /etc/apache2/envvars for Apache)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
Installing and configuring Oracle support for PHP is way out of scope for this document. The official Oracle documentation called The Underground PHP and Oracle Manual should help you through the process.
Creating a database user for ownCloud can be done by using the sqlplus command line interface or the Oracle Application Express web interface. The database tables will be created by ownCloud when you login for the first time.
To start the Oracle command line mode with a DBA account use:
sqlplus system AS SYSDBA
After entering the password a SQL> prompt will appear. Now enter the following lines and confirm them with the enter key:
CREATE USER owncloud IDENTIFIED BY password;
ALTER USER owncloud DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA unlimited ON users;
GRANT create session
, create table
, create procedure
, create sequence
, create trigger
, create view
, create synonym
, alter session
TO owncloud;
Note
In Oracle creating a user is the same as creating a database in other RDBMs, so no CREATE DATABASE statement is necessary.
You can quit the prompt by entering:
exit
An ownCloud instance configured with Oracle 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" => "oci",
"dbname" => "XE",
"dbuser" => "owncloud",
"dbpassword" => "password",
"dbhost" => "localhost",
Note
This example assumes you are running an Oracle Express Edition on localhost. The dbname is the name of the Oracle instance. For Oracle Express Edition it is always XE.
Trouble Shooting¶
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 host name (db.server.com in this example):
ping db.server.dom
PING db.server.dom (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:
SQLite:
sqlite3 /www/htdocs/owncloud/data/owncloud.db
sqlite> .version
SQLite 3.7.15.1 2012-12-19 20:39:10 6b85b767d0ff7975146156a99ad673f2c1a23318
sqlite> .quit
MySQL:
Assuming the database server is installed on the same sytem you’re running, the command from, use:
mysql -uUSERNAME -p
To acess a MySQL installation on a different machine, add the -h option with the respective host name:
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 sytem you’re running the command from, use:
psql -Uusername -downcloud
To acess a MySQL installation on a different machine, add the -h option with the respective host name:
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
Oracle:
On the machine where your Oracle database is installed, type:
sqlplus username
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> exit
Useful SQL commands¶
Show Database Users:
SQLite : No database user is required.
MySQL : SELECT User,Host FROM mysql.user;
PostgreSQL: SELECT * FROM pg_user;
Oracle : SELECT * FROM all_users;
Show available Databases:
SQLite : .databases (normally one database per file!)
MySQL : SHOW DATABASES;
PostgreSQL: \l
Oracle : SELECT name FROM v$database; (requires DBA privileges)
Show ownCloud Tables in Database:
SQLite : .tables
MySQL : USE owncloud; SHOW TABLES;
PostgreSQL: \c owncloud; \d
Oracle : SELECT table_name FROM user_tables;
Quit Database:
SQLite : .quit
MySQL : quit
PostgreSQL: \q
Oracle : quit