Convert OC10 from sqlite to my/mariasql

Server
,
1

Hello all,

The OC server I installed a while ago as a showcase, has now climbed the hierarchy to the production server level.
Initially I configured the server as a simple plain vanilla stand-alone instance, using sqlite and no particular mem caches etc.

Now my superiors want to make this really serious and add a plethora of new user accounts, files, folders and whatnot.

Thus I proceeded to convert the sqlite3 db to mariasql, which supposedly is a mysql equivalent and reported to be just fine with OC.

I almost immediately ran into problems.
Please see the below conversion routine I got from here; https://doc.owncloud.com/server/10.0/admin_manual/configuration/database/db_conversion.html?highlight=database.

[root@owncloud owncloud]# sudo -u apache ./occ db:convert-type mysql owncloud localhost owncloud
Enter a new password:
Creating schema in new database
The following tables will not be converted:
oc_account_terms
oc_accounts
oc_addressbookchanges
oc_addressbooks
oc_calendarchanges
oc_calendarobjects
oc_calendars
oc_calendarsubscriptions
oc_cards
oc_cards_properties
oc_dav_shares
oc_external_applicable
oc_external_config
oc_external_mounts
oc_external_options
oc_federated_reshares
oc_files_trash
oc_migrations
oc_notifications
oc_properties
oc_schedulingobjects
oc_share
oc_share_external
oc_trusted_servers
Please note that tables belonging to available but currently not installed apps
can be included by specifying the --all-apps option.
Continue with the conversion (y/n)? [n] y
oc_activity
389/389 [============================] 100%oc_activity_mq
0 [>---------------------------]oc_appconfig
104/104 [============================] 100%oc_authtoken
0 [>---------------------------]oc_comments
0 [>---------------------------]oc_comments_read_markers
0 [>---------------------------]oc_credentials
0 [>---------------------------]oc_file_locks
9/9 [============================] 100%oc_filecache
452/452 [============================] 100%oc_group_admin
4/4 [============================] 100%oc_group_user
30/30 [============================] 100%oc_groups
5/5 [============================] 100%oc_jobs
1/14 [==>-------------------------] 7%

[Doctrine\DBAL\Exception\InvalidFieldNameException]
An exception occurred while executing 'INSERT INTO oc_jobs (id, class, argument, last_run, last_checked, reserved_at, exe
cution_duration) VALUES(?, ?, ?, ?, ?, ?, ?)' with params ["1", "OCA\Activity\BackgroundJob\EmailNotification", "null", "1510665308"
, "1510666207", "0", "0"]:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'execution_duration' in 'field list'

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'execution_duration' in 'field list'

[PDOException]
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'execution_duration' in 'field list'

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--]

[root@owncloud owncloud]#

I did try to run the conversion with the --all-apps flag after this failed, but ran into the same problem.

I can't make any sense of what's wrong, except that some column in a table is missing.
Sql databases is not quite my forté yet, but I try, so please, can anybody hint or point me in the direction of what to do next?

I run this OC instance on a CentOS 7 x64-server and php is v5.6.

Thanks in advance for any feedback.

2

Did you choose MariaDB 10.2.7 by any chance? That one doesn't work yet ... https://github.com/owncloud/core/pull/29100

3

I think i have read somewhere in the release notes of oC 10 that the conversion doesn't work at all.

4

oh, you are right: https://github.com/owncloud/core/issues/27075
No way currently :frowning:

5

@hodyroff; Don't think so.

[root@owncloud ~]# mysql -V
mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

6

@tom42; Oh, cr@p...

What now then?

Any suggestions on how to proceed?
I'd rather not have to remake this whole thing. 8-/

7

Right now, there is no other path. Thats why we discourage sqlite usage but its great for some testing, etc.
Discussions are ongoing about deprecating it in the future.

8

We need to discuss this at work.

How much can sqlite handle in real life situations?

This is a fairly low-traffic OC-server, but every once in a while the members upload plenty of big stuff.

9

Big stuff is not an issue. Challenge is the number of files and folders, the number of users and the number of shares as well as the usage of the desktop client or mobile client sync functions which are very database heavy. You will see the load on the sqlite database when you monitor it.

10

Couldn't find any out-of-the-box solutions for monitoring sqlite performance and load, but did however find this;
http://www.sqlite.org/whentouse.html

It might be that sqlite will work for us anyway, as long as I also set up some kind of caching.

And this too!
"Anyway, SQLite is an embedded library, so you'd have to monitor your actual application. Tools like Nagios allow to monitor a server's CPU load and disk usage, but you can also use any other tool of your OS."

From https://stackoverflow.com/questions/37557078/how-to-monitor-sqlite-performance.

We already monitor our servers with monit, so keeping an eye on the actual server will maybe give us a hint about the state of things.

1 Like
11

SQLite should be a decent enough solution for small sites and will often give similar performance as an unoptimized MySQL . You could probably still use memcache and there used to be a way of converting within OwnCloud (https://doc.owncloud.org/server/8.0/admin_manual/configuration_database/db_conversion.html).

I haven't tried yet for OwnCloud, but I've converted other apps. Typically it goes something like this:
-Clean the database up (eg. remove session caches and anything else that's temporary etc)
-Dump the SQLite database into plain SQL statements
-Create the app's MySQL structure (install it fresh as if it were a MySQL-powered instance)
-Generally, the SQL dump from SQLite will work in MySQL (it's almost the same syntax after all). There might be some minor things that don't convert easy, just monitor them, edit the data or statements where necessary.

It's a big PITA but it's not "impossible".

1 Like
12

I think the main issue is not performance / high load but parallel / concurrent requests which AFAIK can't be handled by sqlite.

1 Like
13

@guruevi
We've settled on using sqlite for now. Should the performance hit become a problem later on, I'll look into reinstalling with mysql instead.
I'll look into the caching today actually. :slight_smile:

14

Is there any update for this critical issue? I want to migrate from SQlite (2GB) to MySQL and this bug is stopping me.

Can I use 3rd parties tools like DBConvert to convert SQlite to MySQL? This tool costs around 149$.

Thanks in Advance.

15

Don't count on a fix, per the bug tracker for the community version.

If you're running an enterprise level OC, I'm guessing support is included - The helpdesk should be able to help you - file a ticket and see what they say.

If not, I'm thinking your enterprise should be able to shell out with USD 149 for the conversion tool.