Core: occ db:convert-type doesn't work with migration

[PVince81]

Steps

1. Setup ownCloud with sqlite
2. Use occ db:convert-type to convert to MySQL
3. Refresh the page

Expected

All tables migrated

Actual

Some tables were omitted: oc_external_config, etc. These tables are created not with MDB2 schemas but with migrations.

Versions

master.

We need to adjust the command to also run the migrations on the new database to initially create the tables. I'm not sure if this will work correctly because a migration isn't always only creating tables...

± % occ db:convert-type --all-apps --password owncloud42 mysql owncloud localhost owncloudmb4
Creating schema in new database
The following tables will not be converted:
oc_external_applicable
oc_external_config
oc_external_mounts
oc_external_options
oc_migrations
Continue with the conversion (y/n)? [n] y
oc_activity
    0 [>---------------------------]oc_activity_mq
    0 [>---------------------------]oc_addressbookchanges
    0 [>---------------------------]oc_addressbooks
 1/1 [============================] 100%oc_appconfig
 66/66 [============================] 100%oc_authtoken
 1/1 [============================] 100%oc_calendarchanges
    0 [>---------------------------]oc_calendarobjects
    0 [>---------------------------]oc_calendars
 1/1 [============================] 100%oc_calendarsubscriptions
    0 [>---------------------------]oc_cards
    0 [>---------------------------]oc_cards_properties
    0 [>---------------------------]oc_comments
    0 [>---------------------------]oc_comments_read_markers
    0 [>---------------------------]oc_credentials
    0 [>---------------------------]oc_dav_shares
    0 [>---------------------------]oc_federated_reshares
    0 [>---------------------------]oc_file_locks
 3/3 [============================] 100%oc_filecache
 14/14 [============================] 100%oc_files_trash
    0 [>---------------------------]oc_group_admin
    0 [>---------------------------]oc_group_user
 1/1 [============================] 100%oc_groups
 1/1 [============================] 100%oc_jobs
 14/14 [============================] 100%oc_mimetypes
 8/8 [============================] 100%oc_mounts
 1/1 [============================] 100%oc_preferences
 2/2 [============================] 100%oc_privatedata
    0 [>---------------------------]oc_properties
    0 [>---------------------------]oc_schedulingobjects
    0 [>---------------------------]oc_share
    0 [>---------------------------]oc_share_external
    0 [>---------------------------]oc_storages
 2/2 [============================] 100%oc_systemtag
    0 [>---------------------------]oc_systemtag_group
    0 [>---------------------------]oc_systemtag_object_mapping
    0 [>---------------------------]oc_trusted_servers
    0 [>---------------------------]oc_users
 1/1 [============================] 100%oc_vcategory
    0 [>---------------------------]oc_vcategory_to_object
    0 [>---------------------------]

@DeepDiver1975

[PVince81]

I'm afraid that there will be no solution for this.
Migrations cannot provide a list of tables to migrate

Maybe convert-type needs to work differently: instead of using the schemas, just migrate whatever data is there regardless... in which case the command itself becomes generic and could be written as a completely separate tool (not part of ownCloud).

I'm already hearing @DeepDiver1975 from far away saying "kill convert-type" 😉

[PVince81]

Wait a minute... The option --all-apps is supposed to also migrate extra tables.

But the code seems to just through away the extra tables instead of doing so.
When the option is not set, a hint tells that you can set it.

But then it doesn't do anything. WTF

[PVince81]

Ok, found the code.

Actually it finds the extra tables only for the warnings.
When you specify --all-apps it uses getAllApps() instead of getEnabledApps() and reads database.xml to find out how to create the tables...

So no luck there.

[DeepDiver1975]

I'm already hearing @DeepDiver1975 from far away saying "kill convert-type" :wink:

:grimacing:

[DeepDiver1975]

We could clear the migrations table on the target schema and run all migration steps for all apps.

somthing like:

```sh
./occ migrations:migrate --all --restart

[PVince81]

You don't even need to clear the migration table, the convert script doesn't copy it over.

My worry about running the migration scripts is that some might want to operate on existing data. But the data hasn't been copied over yet. On the other hand, the data in the source tables is already converted to whatever format is required by the migrations once they all ran. So it might work.

I guess there can still be some possible migration scenarios where this wouldn't work, if for example a migration expects data to exist and fails if it doesn't (ex: some kind of orphaned share situation where the missing data wasn't copied yet to the target tables)

[PVince81]

We need to sort this out before final. So far I don't see any good solution apart from deprecating the tool... Or extracting it to be a general DB conversion tool not part of OC releases.

[PVince81]

Assigning to @DeepDiver1975 to make a decision

[PVince81]

We'll need a decision here, this feature will be completely broken in 10.0 and I don't see any possible solution because DB migrations work completely differently than what this feature here expects.

I'd suggest removing this feature. Maybe a separate generic tool could be provided to convert a SQLite DB to MySQL but nothing bundled or OC specific.

@pmaier1

[felixboehm]

Let the command only say:
"This function is not working in ownCloud 10.0. Please contact your ownCloud Support for consulting."
Then exit.
We can add the functionality / extra tool later, when we have a good solution.

[PVince81]

not adding this now -> 10.0.1

I'm not even sure we can even make it work correctly without a huge effort, and I have the feeling that we don't have time to spend that effort for this little benefit.

[PVince81]

@pmaier1 how important is this feature ? I don't think it is fixable at all currently without major rework.

Should we remove it and/or add a message that it doesn't work ?

[PVince81]

moving to triage for decision and scheduling

[PVince81]

kill?

[PVince81]

☠️ https://github.com/owncloud/core/pull/28744

[joe-eklund]

I just did a fresh install with Owncloud and Postgres in their respective Docker containers. Having to rely on the sqlite db from a fresh install and being unable to migrate to something like Postgresql makes Owncloud quite unable to scale.

Has there been any update on this issue?

Is there a way to install Owncloud and during the installation process point it to Postgres since the migration tool is broken? If I could skip sqlite altogether that would be a solution for new installs at least.

Sorry if I seem like I don't know what I am talking about, because I don't really; just installed this an hour ago. :)

[ghost]

Is there a way to install Owncloud and during the installation process point it to Postgres since the migration tool is broken? If I could skip sqlite altogether that would be a solution for new installs at least.

Yes, this is possible: https://doc.owncloud.org/server/latest/admin_manual/installation/installation_wizard.html#database-choice-label

[ccwb-102]

After all that said I still need to migrate my existing OC X server from sqlite to mysql.

What I have in mind is:

• do a fresh install of OC X with mysql as backend on a separate server
• export the mysql DB scheme
• import the scheme on the mysql instance of my existing production OC X server
• dump sqlite DB of the production server
• import the sqlite DB dump into the mysql database scheme imported from vanilla OC X installation (with quite a few 'manual' adjustments to make up for SQL differences between sqlite and mysql) -> that's where it probably gets dirty
• reconfigure OC X to use the new DB backend

How does that sound to people in this forum. Is that doable at all. Or am I aiming for a dead end?

[msaeedm]

I also need to convert from sqlite to mysql. Are there other workaround methods that we can use until db:convert gets fixed?

[TwistedLucidity]

To move between databases you'll have to make use of third party utilities. I have not done it myself and I cannot over emphasise the need to back-ups and rigorous testing.
Here is one example of SQLite -> MySQL from 2014. Bear in mind, it may no longer work due to database changes.
As I have no personal experience here, I am afraid I can't offer any more advice than saying you'll need to do your own research.

[ghost]

According to the original page, this will break owncloud 6 installation.http://fabianpeter.de/cloud/owncloud-migrating-from-sqlite-to-mysql/ So use this only for owncloud version 5

from the linked page. So i think you will get in trouble with such third party migrations.

[ccwb-102]

What I have done now:

• fresh install of OC X with mysql as backend and with those apps enabled that I currently have in production
• wrote a Perl script that
• compares which tables exist in my production sqlite database AND in the fresh mysql DB -> should take of legacy tables in my production DB that are not needed any more
• migrates the content of the common tables from sqlite to the mysql DB

It is still a bit rough... but at first glance it seems to work. I will spend more time on testing by creating a complete clone of my production OC X instance with mysql as backend. And then: testing testing testing.

[msaeedm]

@TwistedLucidity @kdslkdsaldsal I have OC X so I guess I can not even try outdated db conversion tools :(

@ccwb-102 your way of doing this is tempting. I tried couple different python scripts I randomly found on the internet and they did not work. Not sure if that is bc the code is imperfect or bc my data is encrypted. Does your Perl script work reasonably well? Would you be generous and share it with me? I realize it may not be 100% reliable but I am open to testing testing testing ;)

[ccwb-102]

@msaeedm: That depends on your definition of "reasonably well" ;-)
Currently it is more in "proof of concept" stage. But it worked for me to
clone my existing OC X production site to a newly installed mysql based OC
X installation.

I do not know about your level of Perl experience. If you can wait until
next week I can probably share a more "complete" version of the script. As
it is now there is a lot of hard-coded definitions in there. And it will
overwrite existing data if not handled carefully.

And my OC X is not encrypted. So your mileage may vary if you are
encrypted. I have no information on the type of encryption used. Basically
my script is agnostic to the content it shifts from one DB to the other.
So if the encryption is DB independent it should still work.

[ghost]

I just want to repeat my expression that you might get in a lot of trouble when doing such own conversations even if the initial conversations might have worked.

In the past e.g. issues where showing up only when doing an upgrade to a next major release months later. See e.g. https://github.com/owncloud/core/issues/999, https://github.com/owncloud/core/issues/999#issuecomment-32021546, https://github.com/owncloud/core/issues/4797 and so on.

[ccwb-102]

@kdslkdsaldsal: Understood your concerns. But can you be a bit more
concrete? If we do a 1:1 copy from one DB backend to another that should
only hurt if there is business logic dependency with DB type (e.g. stored
DB procedures, special data types of a specific DB backend used, ...).
Currently I do not see such dependencies.

There might possibly be some issues with auto-increment key attributes.
Will do more testing around that. But apart from that I only see pretty
vanilla SQL types used.

To further eliminate issues I do not create the schema with my migration
script. Instead I will let the installation wizard of OC and the apps take
care of that. I will then migrate into this schema.

[ghost]

@ccwb-102 I can't be concrete about this as i don't have any knowledge 😄 . Just want to point out that such self written stuff had caused tons of issues in the past where people reported issue no one was able to reproduce. And in the end they where caused by such migration scripts.

[msaeedm]

How about this..I will make two copies of my data. One to experiment with using @ccwb-102 conversion method. The other one would be fresh with my data re-uploaded again on a fresh OC X/MySQL installation..I have few users who can re-upload their stuff...Nothing super professional :D
So yes I can wait until you make your code more complete @ccwb-102 . My Perl experience is very limited.

[henriec]

Hi there, is there any activity regarding this issue? Or is there an alternative/workaround available? Can i drop my database and restart owncloud using a new empty one?

[PVince81]

@henriec we're still considering how to fix this.

In the meantime, if you only have files with no shares and no calendar/contact or any metadata, you could setup OC from scratch and run occ files:scan --all and keeping your old "data" directory.

[henriec]

Ok, thx for the response. I think that will be the route for me.... I managed using pgloader to build a postgresql DB with all tables and data in it, but i'm not sure about the data-types. The auto-increment-id's is a fixable thing but the columns other than plain integer/number/varchar i'm not sure about.....

For what it is worth: this issue is not a mysql problem. Converting to postgresql is also broken.

[henriec]

Ok, this is wat i did: moved config.php and owncloud.db files. Visit http://server/owncloud. OC ask some stuff to "finish" the installation. Entered credentials and postgres database info and finished installation. After that i recreated all users and did the occ files:scan --all. Everything is working fine now.

Maybe you should give a very clear message to the user that sqlite is not a good idea. Or better: disable this option! A mysql or pg database is an easy thing to create and disabling will prevent users from making bad decisions...

[ghost]

@henriec

you should already get a warning to not use SQLite during the installation:

SQLite will be used as database.
For larger installations we recommend to choose a different database backend.
Especially when using the desktop client for file syncing the use of SQLite is discouraged.

-> https://github.com/owncloud/core/blob/v10.0.3/core/templates/installation.php#L146-L151

There is as well quite a lot of documentation to not use SQLite in production use, see e.g.

https://doc.owncloud.org/server/latest/admin_manual/installation/system_requirements.html
https://doc.owncloud.org/server/latest/admin_manual/configuration/server/oc_server_tuning.html#using-mariadb-mysql-instead-of-sqlite
https://doc.owncloud.org/server/latest/admin_manual/installation/installation_wizard.html#sqlite

And finally the "Admin backend" is also warning about SQLite after the installation:

SQLite is used as database. For larger installations we recommend to switch to a different database backend.
Especially when using the desktop client for file syncing the use of SQLite is discouraged.

-> https://github.com/owncloud/core/blob/v10.0.3/settings/templates/panels/admin/securitywarning.php#L105-L115
--> Funny enough this message seems to still reference to the broken migration tool

Personally i think those are more then enough points where the usage of SQLite is discouraged. Do you think there is something else needed here to warn users about SQLite?

[henriec]

Thanks for your response. You're totally right. With me it worked like this: hey let's try out owncloud. I created a database because i don't like sqlite in the first place. After some testing i decided i did not want to use OC at that moment. Later on (multiple months) i came back and decided to use the owncloud for my home-family file-server. At that point i didn't see (also didn't check) the database i created was not used by owncloud and in stead it used the sqlitedb. After some time when i was looking around in the admin panel i noticed the warning i was using sqlite and so i found out the conversion was broken and i ended up here. Pointing here to some links were i can read why i shouldn't use sqlite isn't helping me. I didn't want it in the first place and i was trying to get rid of it. I think a lot of people don't read (and/or understand right away) the full docs of a product before installing or test-driving it.

In my opinion the best thing to do is to drop the usage of the sqlite option. When you want to setup and run an own owncloud server you should be able to create a database..... So why would you not force this from the beginning?

When you insist on continuing to use sqlite maybe you should place a big red warning on every admin page telling the user he can't use this installation for production without reinstalling because he is not using a real database.

[PVince81]

Raised https://github.com/owncloud/core/issues/29625 to discuss dropping Sqlite support

[fawzy-ibrhim]

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.

[adhusch]

Actually, the official OC docs (https://doc.owncloud.org/server/10.0/admin_manual/configuration/database/db_conversion.html) point the user to use the occ db:convert-type command to migrate from sqllite.

The admin dashbord of an upgraded OC 10 install using sqllite even prints a nice red line reading

To migrate to another database use the command line tool: 'occ db:convert-type'"

However, when a user is doing what you suggest he is stuck with this bug that seems considered a "wont fix" when i follow this discussion

Actually thats pretty bizarre to use a friendly term...

[PVince81]

This is not a "won't fix" yet, so the documentation hasn't been updated. There are still some options to consider for rewriting the conversion code.

Last week @felixboehm suggested running all schema-related migrations on the new DB and then just copying over all the data. Currently OC has three types of migrations:

• DB-schema-related (schema object)
• DB-data-related
• general

Considering that the source DB is at the same OC version level, all data is already migrated to the latest state, so running only the DB-schema-related migrations on the target DB should be enough.

@DeepDiver1975

[termagl]

I'm very keen on getting this resolved as i want to move away from Sqlite as I run into several issues with my instance. Is there a timeline for this issue or something I could help/test?

[DJGummikuh]

Just out of curiosity. Is the convert-type feature generally broken or only in conjunction with MySQL? I'm having the same issue that I was lazy when creating my owncloud (using sqlite) and now run in that bug with the sql queries having too many arguments because I have +1400 Files in a single folder causing all kinds of headaches. My SQLite File is rather small (around 25mb) but contains around 2 dozens of users I don't really want to recreate :-( So if there is a fix (or maybe a workaround, some kind of only take over skeleton configuration so at least the users and their core settings are preserved) I'd greatly appreciate it.

Also I agree with @adhusch that it's really annoying that the documentation does not at least state that db:convert-type is currently broken: I spent a couple hours of waiting (I don't even know why it took so long) trying to migrate the schema twice (to ensure I didn't make an error) before I thought of looking if this might be a recurring error.

Edit: Regarding that "Don't use sqlite for larger installations" - at the time when I installed my owncloud, it wasn't large. It rarely is. It only grew over the last 1 1/2 years.

[PVince81]

@DeepDiver1975 @pmaier1 let's remove this unmaintained feature until someone steps up to fix it and maintain it. I expect it will take some time especially on the QA side.

[DanielWeeber]

So what should we do know while this is not fixed yet?
I stumbled on the red error message, too and tried to execute it.

What is stored in the DB? Only the user accounts, nothing file-related? Then how to delete the database and create a SQL one?

[PVince81]

The database contains a lot more than that:

• list of users and groups
• list of files indexed (oc_filecache)
• metadata attached to files like shares, favorite, tags, webdav properties, etc
• and any other information provided by apps like calendars, contacts, etc

if you don't have any shares or any metadata and also no encryption, you could recreate the database with existing data and run occ files:scan --all to have the data indexed.

[hampma]

I have the same problem (as probably many other users, too): I cannot migrate from SQLite to MySQL. And I am really dissapointed reading the history of this thread: This issue is now open for more than one year and some people even think about just not fixing it.
Will this issue just be dropped? Please let me know, because in this case I will move to NextCloud immediately.

[DanielWeeber]

I downgraded to v9 and migrated the DB there. After that was done I updated to ownCloud X. That was working just fine.
Just put in a text when updating "You will need to upgrade to MySQL, this has to be done first"

[readonly24]

Just switched to 10, wanted to convert to mysql and now I'm here. Is someone working on this issue?

[fpoulain]

This worked nice for me:

• migrate to nextcloud : https://nextcloud.com/migration/
• migrate db : https://docs.nextcloud.com/server/9/admin_manual/configuration_database/db_conversion.html

PS: I am not involved nor in OC neither in NC projects and don't want to laucnh a flame; I only wanted to repair OC for a friend of me.

[VicDeo]

Work in progress (works, but needs testing) https://github.com/owncloud/core/pull/30643

[huttarl]

@ghost wrote,

you should already get a warning to not use SQLite during the installation:

SQLite will be used as database.
For larger installations we recommend to choose a different database backend.
Especially when using the desktop client for file syncing the use of SQLite is discouraged.

I've installed owncloud 3 times today, and it took me this long to figure out how to follow the above advice. It turns out there is a "Storage & database" dropdown on that page, small and transparent on a detailed background, sandwiched between large, bright, opaque input objects like the login credentials fields and the "Finish installation" button. If you notice that little dropdown and click it, you get access to the ability to change database settings. If instead you first enter your credentials and press "Enter" to log in, as the primary elements at the top of the page suggest, you miss the chance to configure database settings.

The warning that SQLite is discouraged, with documentation saying that you can migrate to something else once you get the service up and running, is very different from a warning that says use of SQLite is discouraged and it will be difficult to change later.

When I was installing oc, and encountered the above message, I searched the documentation for how to choose MySQL instead of SQLite. All I found was advice about running occ to convert the database to MySQL after installation. So I went with that option. Of course that led down a frustrating trail.

If you decide not to fix the conversion-to-MySQL tool, please make the Storage and Database settings on the installation page much more prominent. Make them visible instead of hidden by default. And maybe even ask for confirmation if the user tries to click the Finish Installation button with SQLite still selected, letting them know that there is not a conversion path from SQLite.

[PVince81]

or better: drop sqlite support altogether https://github.com/owncloud/core/issues/29625#issuecomment-347941769

[PVince81]

need time from QA at some point to test this when time permits @patrickjahns

[rienex]

is there any news about the bug? Is there any workaround?

[PVince81]

you can help testing whether the PR works for you: https://github.com/owncloud/core/pull/30643

[TByte007]

After 2 years and no fix and now market app stopped working with SQLite.

[pmaier1]

THX @VicDeo !

The above mentioned fix will be part of oC Server 10.3. Please try it and provide feedback here!

[jacobklitzke]

Thanks @pmaier1 ! I have tested this on OwnCloud version 10.3.0 RC1. On migration from SQlite to MariaDB using the following syntax:

sudo -u apache php occ db:convert-type mysql owncloud 127.0.0.1 owncloud

I receive the following errors:

This feature is currently experimental.
Enter a password to access a target database: 
Creating schema in new database

In AbstractMySQLDriver.php line 115:                                                                                                     
  An exception occurred while executing 'ALTER TABLE oc_calendarsubscriptions CHANGE `lastmodified` lastmodified INT UNSIGNED DEFAULT NULL NOT NULL':                                                                                                                      
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'lastmodified'                                                     

In PDOConnection.php line 106:                                                  
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'lastmodified'  

In PDOConnection.php line 104:                                                         
  SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'lastmodified'  

[VicDeo]

@jacobklitzke sounds similar to https://github.com/owncloud/core/issues/29445
What is your MariaDB version?

[VicDeo]

ok. got it.
Until PHP7.0 support is dropped we use a column listener workaround to fix migrations produced by doctrine/dbal 2.5 for MariaDB 10.2.7+

The workaround is activated by Platform name and version. When migrations are generated the platform is not MariaDB (source DB is checked in lines below while MariaDB is a target DB )

https://github.com/owncloud/core/blob/master/lib/private/DB/MySqlSchemaColumnDefinitionListener.php#L51
https://github.com/owncloud/core/blob/master/lib/private/DB/MySqlSchemaColumnDefinitionListener.php#L54

[VicDeo]

@jacobklitzke
For MariaDB 10.2.7+ please try changing https://github.com/owncloud/core/blob/master/lib/private/DB/MySqlSchemaColumnDefinitionListener.php#L58
to
if (true) {
it will activate the workaround unconditionally.

I don't think it worth fixing as 10.4 will probably unlock doctrine/dbal 2.7 by dropping PHP 7.0.
doctrine/dbal 2.7 has native MariaDB 10.2.7+ support.

[jacobklitzke]

@VicDeo After making the change you recommended, the migration worked successfully. Thanks for all your help and work on this issue! BTW, I was using MariaDB 10.4.7.

[KlausThornProgrammfabrik]

Worked for me: sqlite to mysql conversion with the official docs:

https://doc.owncloud.com/server/10.5/admin_manual/configuration/database/db_conversion.html
(but table_cache is now table_open_cache)

owncloud server 10.5.0.10

mysql-server 5.7.31-0ubuntu0.18.04.1

PHP 7.2.24-0ubuntu0.18.04.6 (but php 7.0 is also installed. No clue which one is used)