occ db:convert-type mysql -> UniqueConstraintViolationException #4914

hostingnuggets · 2017-05-17T08:49:36Z

Steps to reproduce

run: occ db:convert-type --all-apps --clear-schema mysql db username hostname database

Expected behaviour

Migrate Nextcloud PostgreSQL database to MySQL without any issues.

Actual behaviour

oc_filecache
chunked query, 15 chunks
  5700/14898 [==========>-----------------]  38%
                                                                                                                                                                                   
  [Doctrine\DBAL\Exception\UniqueConstraintViolationException]                                                                                                                     
  An exception occurred while executing 'INSERT INTO `oc_filecache` (`fileid`, `storage`, `path`, `path_hash`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storag  
  e_mtime`, `encrypted`, `unencrypted_size`, `etag`, `permissions`, `checksum`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [463919, 1, "files\/000_Folder\/Directory\/file.odt", "4a1f98565842fa63d3398b42198dd7bd", 463857, "file.odt", 7, 5, 73841, 147109182  
  3, 1471091823, 0, 0, "8756a2b3c9cebbb0aec8d1bd8d296030", 27, ""]:                                                                                                                
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '463919' for key 'PRIMARY'                                                                            
 
 [Doctrine\DBAL\Driver\PDOException]                                                               
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '463919' for key 'PRIMARY'  
               
  [PDOException]                                                                                    
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '463919' for key 'PRIMARY'

Server configuration

Operating system: Debian 7

Web server: nginx 1.8.1

Database: 9.1.24

PHP version: 5.6.30

Nextcloud version: 11.0.2

Updated from an older Nextcloud/ownCloud or fresh install: yes, updated

Where did you install Nextcloud from: from the shell

Signing status:

Signing status

No errors have been found.

List of activated apps:

App list

Enabled:
  - activity: 2.4.1
  - admin_audit: 1.1.0
  - bookmarks: 0.10.0
  - calendar: 1.5.2
  - comments: 1.1.0
  - contacts: 1.5.3
  - dav: 1.1.1
  - federatedfilesharing: 1.1.1
  - federation: 1.1.1
  - files: 1.6.1
  - files_pdfviewer: 1.0.1
  - files_sharing: 1.1.1
  - files_texteditor: 2.2
  - files_trashbin: 1.1.0
  - files_versions: 1.4.0
  - files_videoplayer: 1.0.0
  - firstrunwizard: 2.0
  - gallery: 16.0.0
  - logreader: 2.0.0
  - lookup_server_connector: 1.0.0
  - nextcloud_announcements: 1.0
  - notifications: 1.0.1
  - password_policy: 1.1.0
  - provisioning_api: 1.1.0
  - serverinfo: 1.1.1
  - sharebymail: 1.0.1
  - survey_client: 0.1.5
  - systemtags: 1.1.3
  - tasks: 0.9.5
  - templateeditor: 0.2
  - theming: 1.1.1
  - twofactor_backupcodes: 1.0.0
  - updatenotification: 1.1.1
  - user_ldap: 1.1.2
  - workflowengine: 1.1.1
Disabled:
  - encryption
  - external
  - files_accesscontrol
  - files_automatedtagging
  - files_external
  - files_retention
  - richdocuments
  - user_external
  - user_saml

Nextcloud configuration:

Config report

{
    "system": {
        "instanceid": "ocrioxxj1qez",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.domain.com"
        ],
        "datadirectory": "\/srv\/nextcloud\/data",
        "overwrite.cli.url": "https:\/\/cloud.domain.com",
        "dbtype": "pgsql",
        "version": "11.0.2.7",
        "dbname": "nc",
        "dbhost": "localhost",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "forcessl": true,
        "forceSSLforSubdomains": true,
        "mail_from_address": "cloud",
        "mail_smtpmode": "php",
        "mail_domain": "domain.comt",
        "ldapIgnoreNamingRules": false,
        "maintenance": false,
        "theme": "mytheme",
        "loglevel": 2,
        "logtimezone": "Europe\/Berlin",
        "trashbin_retention_obligation": "auto,30",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "updater.release.channel": "stable",
        "ldapProviderFactory": "\\OCA\\User_LDAP\\LDAPProviderFactory",
        "appstore.experimental.enabled": true,
        "activity_expire_days": 180
    }
}

Are you using external storage, if yes which one: local

Are you using encryption: no

Are you using an external user-backend, if yes which one: LDAP

LDAP configuration (delete this part if not used)

LDAP config

+-------------------------------+---------------------------------------------------------------------------------------------------+
| Configuration                 | s01                                                                                               |
+-------------------------------+---------------------------------------------------------------------------------------------------+
| hasMemberOfFilterSupport      | 0                                                                                                 |
| hasPagedResultSupport         |                                                                                                   |
| homeFolderNamingRule          |                                                                                                   |
| lastJpegPhotoLookup           | 0                                                                                                 |
| ldapAgentName                 | cn=proxyagent,ou=ldapusers,dc=domain,dc=com                                                      |
| ldapAgentPassword             | ***                                                                                               |
| ldapAttributesForGroupSearch  |                                                                                                   |
| ldapAttributesForUserSearch   |                                                                                                   |
| ldapBackupHost                | ldap2.domain.com                                                                           |
| ldapBackupPort                | 389                                                                                               |
| ldapBase                      | ou=people,dc=domain,dc=com                                                                       |
| ldapBaseGroups                | ou=people,dc=domain,dc=com                                                                       |
| ldapBaseUsers                 | ou=people,dc=domain,dc=com                                                                      |
| ldapCacheTTL                  | 600                                                                                               |
| ldapConfigurationActive       | 1                                                                                                 |
| ldapDynamicGroupMemberURL     |                                                                                                   |
| ldapEmailAttribute            | mail                                                                                              |
| ldapExperiencedAdmin          | 0                                                                                                 |
| ldapExpertUUIDGroupAttr       |                                                                                                   |
| ldapExpertUUIDUserAttr        |                                                                                                   |
| ldapExpertUsernameAttr        | uid                                                                                               |
| ldapGroupDisplayName          | cn                                                                                                |
| ldapGroupFilter               |                                                                                                   |
| ldapGroupFilterGroups         |                                                                                                   |
| ldapGroupFilterMode           | 1                                                                                                 |
| ldapGroupFilterObjectclass    |                                                                                                   |
| ldapGroupMemberAssocAttr      | uniqueMember                                                                                      |
| ldapHost                      | ldap1.domain.com                                                                           |
| ldapIgnoreNamingRules         |                                                                                                   |
| ldapLoginFilter               | (&(|(objectclass=inetOrgPerson))(uid=%uid)(memberOf=cn=cloud,ou=groups,dc=domain,dc=com)) |
| ldapLoginFilterAttributes     |                                                                                                   |
| ldapLoginFilterEmail          | 0                                                                                                 |
| ldapLoginFilterMode           | 1                                                                                                 |
| ldapLoginFilterUsername       | 1                                                                                                 |
| ldapNestedGroups              | 0                                                                                                 |
| ldapOverrideMainServer        | 0                                                                                                 |
| ldapPagingSize                | 500                                                                                               |
| ldapPort                      | 389                                                                                               |
| ldapQuotaAttribute            |                                                                                                   |
| ldapQuotaDefault              |                                                                                                   |
| ldapTLS                       | 1                                                                                                 |
| ldapUserDisplayName           | cn                                                                                                |
| ldapUserDisplayName2          |                                                                                                   |
| ldapUserFilter                | (|(objectclass=inetOrgPerson))                                                                    |
| ldapUserFilterGroups          |                                                                                                   |
| ldapUserFilterMode            | 0                                                                                                 |
| ldapUserFilterObjectclass     | inetOrgPerson                                                                                     |
| ldapUuidGroupAttribute        | auto                                                                                              |
| ldapUuidUserAttribute         | auto                                                                                              |
| turnOffCertCheck              | 0                                                                                                 |
| turnOnPasswordChange          | 0                                                                                                 |
| useMemberOfToDetectMembership | 1                                                                                                 |
+-------------------------------+---------------------------------------------------------------------------------------------------+

Client configuration

Browser: Firefox 45.0.9

Operating system: Debian 8

Logs

Web server error log

nothing relevant

Nextcloud log (data/nextcloud.log)

Nextcloud log

nothing relevant

Browser log

n/a

The text was updated successfully, but these errors were encountered:

MorrisJobke · 2017-05-17T14:47:04Z

cc @nickvergessen @icewind1991 🙈

nickvergessen · 2017-05-18T09:02:12Z

This happens on the migration or afterwards?

hostingnuggets · 2017-05-18T14:58:41Z

@nickvergessen this happens during the occ db:convert-type command, as soon as this error gets thrown it aborts the whole process and bails out with the error messages I have submitted. I had the same problem first with the oc_activity table but I could empty this table so I bypassed this issue but afaik it is not possible to empty the oc_filecache table, else I will have another type of issue (no files)...

cbrompton · 2017-06-03T15:57:41Z

I can confirm I've had the same issue going from sqlite to mysql
here's my error output:

  An exception occurred while executing 'INSERT INTO `oc_filecache` (`fileid`, `storage`, `path`, `path_hash`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `  
  mtime`, `storage_mtime`, `encrypted`, `unencrypted_size`, `etag`, `permissions`, `checksum`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params  
   ["75340", "9", "appdata_ocnocud4wwks\/js\/core\/merged-share-backend.js.deps", "afe08a1c719049620553406d7946ef07", "33467", "merged-share-backend.js.deps", "7"  
  , "5", "682", "1495772154", "1495772154", "0", "0", "8ccf85b6f2a469a8bf212e73d0c7a5d3", "27", ""]:                                                                
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '9-afe08a1c719049620553406d7946ef07' for key 'fs_storage_path_hash'                         
                                                                                                                                                                    

                                                                                                                                             
  [Doctrine\DBAL\Driver\PDOException]                                                                                                        
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '9-afe08a1c719049620553406d7946ef07' for key 'fs_storage_path_hash' 
                                                                                                                                             

                                                                                                                                             
  [PDOException]                                                                                                                             
  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '9-afe08a1c719049620553406d7946ef07' for key 'fs_storage_path_hash' ```

nickvergessen · 2017-06-06T11:08:57Z

@cbrompton What does the query return on your old database:

SELECT * FROM `oc_filecache` WHERE `storage` = 9 AND `path_hash` = 'afe08a1c719049620553406d7946ef07'

@hostingnuggets What does the query return on your old database:

SELECT * FROM `oc_filecache` WHERE `fileid` = 463919

hostingnuggets · 2017-06-06T12:10:11Z

@nickvergessen below is the ouput of your query. I have modified the directory/filename for privacy reasons.

-[ RECORD 1 ]----+---------------------------------------------------------------------------
fileid           | 463919
storage          | 1
path             | files/Directory/file.odt
path_hash        | 4a1f98565842fa63d3398b42198dd7bd
parent           | 463857
name             | file.odt
mimetype         | 7
mimepart         | 5
size             | 73841
mtime            | 1471091823
storage_mtime    | 1471091823
encrypted        | 0
unencrypted_size | 0
etag             | 8756a2b3c9cebbb0aec8d1bd8d296030
permissions      | 27
checksum         |

hostingnuggets · 2017-07-06T20:51:18Z

@nickvergessen was my output above helpful or do you need any more information?

nickvergessen · 2017-07-07T08:17:24Z

yeah a bit, but it is very odd.
The path_hash does not belong to the path. So something went wrong there somehow.
But I couldn't figure out what it is yet.

nickvergessen · 2017-07-07T08:26:56Z

Okay the wrong hash aside. There is no order command on the import queries.
Since we chunk those here, I guess the order changed between chunk 14 and 15 and therefor we are importing the same item twice. 😕

The only think I can see is to hardcode order-by options for known tables. 😞

nickvergessen · 2017-07-07T08:32:03Z

@MorrisJobke you have any other idea?

MorrisJobke · 2017-07-07T10:09:33Z

@MorrisJobke you have any other idea?

No :/

hostingnuggets · 2017-07-07T15:10:44Z

From a user perspective is there any workaround on how I can migrate from PostgreSQL to MySQL without having to use the occ command?

MorrisJobke · 2017-07-10T15:33:03Z

From a user perspective is there any workaround on how I can migrate from PostgreSQL to MySQL without having to use the occ command?

No. There is no other way than using our command line tool.

hostingnuggets · 2017-07-10T20:45:13Z

@MorrisJobke I guess I will simply have to wait that you guys find the worm in this migration command.

hostingnuggets · 2017-07-25T18:11:28Z

Well done guys! So this should fix my migration issue if I understand correctly? and is it possible that this feature will be available with Nextcloud 12.0.x or do I need to wait for Nextcloud 13?

nickvergessen · 2017-07-25T18:18:24Z

13 sadly, because the required changes are too big.

hostingnuggets · 2017-07-25T19:35:23Z

No problem I will have to show a bit more patience in order to migrate away from PostgreSQL.

nickvergessen · 2017-07-26T10:56:28Z

You can try to hardcode it for the filecache, but then you will get a similar issue on the next table... So I would suggest to wait, thanks 😸

MorrisJobke added the bug label May 17, 2017

nickvergessen mentioned this issue Jul 19, 2017

Install nextcloud via migrations instead of the db_structure.xml #5772

Merged

LukasReschke closed this as completed in #5772 Jul 25, 2017

MorrisJobke added this to the Nextcloud 13 milestone Jul 26, 2017

nextcloud-bot mentioned this issue Jul 9, 2018

db:convert-mysql-charset and MariaDB 10.3 #10163

Closed

nextcloud-bot mentioned this issue Jul 29, 2018

MYSQL #10440

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

occ db:convert-type mysql -> UniqueConstraintViolationException #4914

occ db:convert-type mysql -> UniqueConstraintViolationException #4914

hostingnuggets commented May 17, 2017

MorrisJobke commented May 17, 2017

nickvergessen commented May 18, 2017

hostingnuggets commented May 18, 2017

cbrompton commented Jun 3, 2017 •

edited

Loading

nickvergessen commented Jun 6, 2017

hostingnuggets commented Jun 6, 2017

hostingnuggets commented Jul 6, 2017

nickvergessen commented Jul 7, 2017

nickvergessen commented Jul 7, 2017

nickvergessen commented Jul 7, 2017

MorrisJobke commented Jul 7, 2017

hostingnuggets commented Jul 7, 2017

MorrisJobke commented Jul 10, 2017

hostingnuggets commented Jul 10, 2017

hostingnuggets commented Jul 25, 2017

nickvergessen commented Jul 25, 2017

hostingnuggets commented Jul 25, 2017

nickvergessen commented Jul 26, 2017

occ db:convert-type mysql -> UniqueConstraintViolationException #4914

occ db:convert-type mysql -> UniqueConstraintViolationException #4914

Comments

hostingnuggets commented May 17, 2017

Steps to reproduce

Expected behaviour

Actual behaviour

Server configuration

LDAP configuration (delete this part if not used)

Client configuration

Logs

Web server error log

Nextcloud log (data/nextcloud.log)

Browser log

MorrisJobke commented May 17, 2017

nickvergessen commented May 18, 2017

hostingnuggets commented May 18, 2017

cbrompton commented Jun 3, 2017 • edited Loading

nickvergessen commented Jun 6, 2017

hostingnuggets commented Jun 6, 2017

hostingnuggets commented Jul 6, 2017

nickvergessen commented Jul 7, 2017

nickvergessen commented Jul 7, 2017

nickvergessen commented Jul 7, 2017

MorrisJobke commented Jul 7, 2017

hostingnuggets commented Jul 7, 2017

MorrisJobke commented Jul 10, 2017

hostingnuggets commented Jul 10, 2017

hostingnuggets commented Jul 25, 2017

nickvergessen commented Jul 25, 2017

hostingnuggets commented Jul 25, 2017

nickvergessen commented Jul 26, 2017

cbrompton commented Jun 3, 2017 •

edited

Loading