DokuWiki

h1. Migrate owncloud 5 from SQLite3 to MySQL

Maybe at some point you realize, that it was not a good idea to have owncloud initially set up with the SQLite-Backend and want to change to MySQL.

h2. Preliminary Note

this is basically taken from http://fabianpeter.de/cloud/owncloud-migrating-from-sqlite-to-mysql/ 

and extended with information from

* http://trac.edgewall.org/wiki/SqLiteToMySql

* http://pario.no/2008/01/27/mysql-create-utf8-database/

* http://www.redmine.org/boards/2/topics/12793

h2. Migration

h3. Install MySQL and necessary php-modules

<pre><code class="bash">

yum install mysql mysql-server php-mysql

</code></pre>

h3. Setup MySQL

We need to make sure that @MySQL@ is started up automatically at system start and need to set a root password for @MySQL@

<pre><code class="bash">

chkconfig mysqld on

/etc/init.d/mysqld start

/usr/bin/mysql_secure_installation

</code></pre>

h3. Create owncloud Database

<pre><code class="sql">

mysql -p

CREATE DATABASE owncloud CHARACTER SET utf8 COLLATE utf8_general_ci;

GRANT ALL PRIVILEGES ON owncloud.* TO owncloud IDENTIFIED BY "pasword";

flush privileges;

exit

</code></pre>

h3. Dump SQLite3-Database

<pre><code class="bash">

sqlite3 /var/www/html/owncloud/data/owncloud.db .dump > /tmp/dump.sql

</code></pre>

h3. Convert SQLite3 to MySQL

Since MySQL uses a pretty special comment/escape format and SQLite uses some commands MySQL doesn’t understand, the SQL output has to be converted. Since my database is quite big and I don’t want to repeat the work others did before me, a little Google magic came up with a python script that does the converter job.

<pre><code class="python">

#! /usr/bin/env python

import sys

def main():

    print "SET sql_mode='NO_BACKSLASH_ESCAPES';"

    lines = sys.stdin.read().splitlines()

    for line in lines:

        processLine(line)

def processLine(line):

    if (

        line.startswith("PRAGMA") or 

        line.startswith("BEGIN TRANSACTION;") or

        line.startswith("COMMIT;") or

        line.startswith("DELETE FROM sqlite_sequence;") or

        line.startswith("INSERT INTO \"sqlite_sequence\"")

       ):

        return

    line = line.replace("AUTOINCREMENT", "AUTO_INCREMENT")

    line = line.replace("DEFAULT 't'", "DEFAULT '1'")

    line = line.replace("DEFAULT 'f'", "DEFAULT '0'")

    line = line.replace(",'t'", ",'1'")

    line = line.replace(",'f'", ",'0'")

    in_string = False

    newLine = @

    for c in line:

        if not in_string:

            if c == "'":

                in_string = True

            elif c == '"':

                newLine = newLine + '`'

                continue

        elif c == "'":

            in_string = False

        newLine = newLine + c

    print newLine

if __name__ == "__main__":

    main()

</code></pre>

download above file to your @/home@, make it executable and perform the conversion

<pre><code class="bash">

chmod a+x /~/sqlite3-to-mysql.py

cat /tmp/dump.sql | python sqlite3-to-mysql.py > /tmp/owncloud.sql

</code></pre>

h3. Import the dump

One thing to note is that SQLite uses utf8 as default. If you have your server configure with encoding other than utf8 and you can't change that, you are going to get your data bad encoded.

Despite the fact that you may probably already set your MySQL database to use utf8 as a default character set, you could have problems when you import data. It's very important to keep in mind that your connection encoding counts. So be sure that whatever software you use as client you set your charset as utf8. As an example for the console client: 

<pre><code class="bash">

mysql -u owncloud -p owncloud --default-character-set=utf8 < /tmp/owncloud.sql

</code></pre>

h3. Change owncloud-config

While most people seem to change the “installed” key in the config array (@config/config.php@) from **true** to **false**, I found it simpler to just add the correct database values.

<pre><code class="php">

...

‘dbtype’ => ‘mysql’,

‘dbname’ => ‘owncloud’,

‘dbuser’ => ‘owncloud’,

‘dbpassword’ => ‘password’,

‘dbhost’ => ’127.0.0.1',

‘dbtableprefix’ => ‘oc_’,

...

</code></pre>