Db migration from postgres to mysql fails with a duplicate key on admin

ℹ️ Support
1

I’m trying to migrate off of postgres back to mysql (mariadb) using 24.0.4, and having a difficult time with it completing. It fails with the error below:

 php8 occ db:convert-type --port 3306 --password password --all-apps mysql nextcloud 192.168.50.50 nextcloud

 - files_versions
 - federatedfilesharing
 - ransomware_protection
 - dav
 - serverinfo
 - accessibility
 - twofactor_backupcodes
 - comments
 - activity
 - weather_status
 - admin_audit
 - oc_accounts
  0/13 [>---------------------------]   0% < 1 sec/< 1 sec
In ExceptionConverter.php line 60:

  An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'admin' for key 'PRIMARY'


In Exception.php line 26:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'admin' for key 'PRIMARY'


In Statement.php line 92:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'admin' for key 'PRIMARY'

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can :heart:

[/details]

Nextcloud version (eg, 20.0.5): 24.0.4
Operating system and version (eg, Ubuntu 20.04): docker contrainer
Apache or nginx version (eg, Apache 2.4.25): nginx, but unknown
PHP version (eg, 7.4): 8.something

The issue you are facing:

Is this the first time you’ve seen this error? (Y/N):

Steps to replicate it:

  1. execute php occ migration (step above)
  2. migration starts, fails after the oc_accounts table)

It seems that there are 2 of every account in the oc_accounts table, one with some “v2-federated” stuff and one without. can i delete one of these?

2

edit: fixed by deleting the dupe entries: here is the formatting between the two, if anyone is curious:

{"displayname":{"value":"admin","scope":"contacts","verified":"0"},"address":{"value":"","scope":"private","verified":"0"},"website":{"value":"","scope":"private","verified":"0"},"email":{"value":null,"scope":"contacts","verified":"0"},"avatar":{"scope":"contacts"},"phone":{"value":"","scope":"private","verified":"0"},"twitter":{"value":"","scope":"private","verified":"0"}}

{"displayname":{"value":"admin","scope":"v2-federated","verified":"0"},"address":{"value":"","scope":"v2-local","verified":"0"},"website":{"value":"","scope":"v2-local","verified":"0"},"email":{"value":null,"scope":"v2-federated","verified":"0"},"avatar":{"scope":"v2-federated"},"phone":{"value":"","scope":"v2-local","verified":"0"},"twitter":{"value":"","scope":"v2-local","verified":"0"},"organisation":{"value":"","scope":"v2-local"},"role":{"value":"","scope":"v2-local"},"headline":{"value":"","scope":"v2-local"},"biography":{"value":"","scope":"v2-local"},"profile_enabled":{"value":"1"}}

not sure what all the “v2” stuff is for.

double update: this doesnt fix it, it seems like there are bunch of dupes. new table has some in oc_mounts. how did these get created?

3

ok somethings definately wrong.

If i run the migration without --clear-schema, iget a fail on “dupliate entry for admin” in the oc_accounts table. If I add clear-schema in, it gets past this, but then sometimes fails on other areas, such as oc_preferences claiming there’s a duplicate key for

Duplicate entry 'admin-activity-configured'

except I can’t find a "admin-activity-configured- key/value/appid/etc in the oc_preferences table. Any ideas? This is really frustrating because with the “clear-schema” option, i have to wait for the oc_filecache to migrate which takes ~40 minutes currently, so 40 minutes every time I try to test something.

edit, fixed the oc_preferences by running

 delete from oc_preferences where ctid IN (select ctid from oc_preferences where userid = 'admin' AND appid = 'activity' AND configkey = 'configured' and configvalue = 'yes' ORDER BY ctid DESC LIMIT 1);

this was tough as a select by userid only returned one match, but if you change to appid, configkey, and config value, you got two matches. it would really help if all of these tables had a unique id field like a row id to make searching easier.