Integrity constraint violation in 8.1.0.0 version server

[luciamaestro]

Actual behaviour

An error is shown in the UI

Steps to reproduce

1. Launch the desktop client app
2. Select to sync a large local folder

Server configuration

{"installed":true,"maintenance":false,"version":"8.1.0.0","versionstring":"8.1 pre alpha","edition":""}

Client configuration

Client version: version 1.8beta2

Operating system:OS X 10.10

Logs

-21-07_0061.JPG" Retrieved inode  6175401 (previous item inode:  6175401 )
03-05 08:35:57:189 0x10376bcc0 OCC::SyncJournalDb::setFileRecord: "INSERT OR REPLACE INTO metadata (phash, pathlen, path, inode, uid, gid, mode, modtime, type, md5, fileid, remotePerm, filesize) VALUES (?1 , ?2, ?3 , ?4 , ?5 , ?6 , ?7,  ?8 , ?9 , ?10, ?11, ?12, ?13);" 6312770779014589282 47 "aa-test-lu/$/Photo-2014-07-16-09-21-07_0061.JPG" 6175401 0 "1417518447" "0" "5d05b517f612cca6322228147111c59d" "00000110ochrzivcagx6" "" 419762
03-05 08:35:57:189 0x10376bcc0 OCC::SyncJournalDb::setUploadInfo: "DELETE FROM uploadinfo WHERE path=?1" "aa-test-lu/$/Photo-2014-07-16-09-21-07_0061.JPG"
03-05 08:35:57:189 0x10376bcc0 OCC::SyncJournalDb::commitInternal: void OCC::SyncJournalDb::commitInternal(const QString &, bool) Transaction commit  "upload file start" and starting new transaction
03-05 08:35:57:189 0x10376bcc0 OCC::SyncEngine::slotJobCompleted: void OCC::SyncEngine::slotJobCompleted(const OCC::SyncFileItem &) "aa-test-lu/$/Photo-2014-07-16-09-21-07_0061.JPG" 4 ""
03-05 08:35:57:192 0x10376bcc0 OCC::SocketApi::sendMessage: SocketApi:  Sending message:  "STATUS:OK:/Users/lmaestro/owncloud-servers/beta2/aa-test-lu/$/Photo-2014-07-16-09-21-07_0061.JPG"
03-05 08:35:57:193 0x10376bcc0 OCC::BandwidthManager::unregisterUploadDevice: void OCC::BandwidthManager::unregisterUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x1037558e0)
03-05 08:35:57:200 0x10376bcc0 OCC::BandwidthManager::registerUploadDevice: void OCC::BandwidthManager::registerUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x10b89a330)
03-05 08:35:57:203 0x10376bcc0 OCC::AbstractNetworkJob::start: !!! OCC::PUTFileJob created for  QUrl( "http://daily.owncloud.com/master/owncloud" )  querying "/aa-test-lu/$/Photo-2014-07-16-09-21-07_0062.JPG"
03-05 08:35:57:228 0x10376bcc0 OCC::BandwidthManager::unregisterUploadDevice: void OCC::BandwidthManager::unregisterUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x10b89a330)
03-05 08:35:57:315 0x10376bcc0 OCC::PropagateUploadFileQNAM::slotPutFinished: void OCC::PropagateUploadFileQNAM::slotPutFinished()  QUrl( "http://daily.owncloud.com/master/owncloud/remote.php/webdav/BBB/example5.file-chunking-474782685-4-2" )  FINISHED WITH STATUS 0 "" QVariant(int, 201) QVariant(QString, "Created")
03-05 08:35:57:315 0x10376bcc0 OCC::SyncJournalDb::setUploadInfo: "INSERT OR REPLACE INTO uploadinfo (path, chunk, transferid, errorcount, size, modtime) VALUES ( ?1 , ?2, ?3 , ?4 ,  ?5, ?6 )" "BBB/example5.file" 3 471636957 0
03-05 08:35:57:315 0x10376bcc0 OCC::SyncJournalDb::commitInternal: void OCC::SyncJournalDb::commitInternal(const QString &, bool) Transaction commit  "Upload info" and starting new transaction
03-05 08:35:57:316 0x10376bcc0 OCC::BandwidthManager::registerUploadDevice: void OCC::BandwidthManager::registerUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x1048a1110)
03-05 08:35:57:324 0x10376bcc0 OCC::AbstractNetworkJob::start: !!! OCC::PUTFileJob created for  QUrl( "http://daily.owncloud.com/master/owncloud" )  querying "/BBB/example5.file-chunking-474782685-4-3"
03-05 08:35:57:330 0x10376bcc0 OCC::BandwidthManager::unregisterUploadDevice: void OCC::BandwidthManager::unregisterUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x1106063a0)
03-05 08:35:58:492 0x10376bcc0 OCC::AbstractNetworkJob::slotFinished: void OCC::AbstractNetworkJob::slotFinished() 401 "Error downloading http://daily.owncloud.com/master/owncloud/remote.php/webdav/BBB/example4.file-chunking-1430717107-4-3 - server replied: Internal Server Error"
03-05 08:35:58:492 0x10376bcc0 OCC::PropagateUploadFileQNAM::slotPutFinished: void OCC::PropagateUploadFileQNAM::slotPutFinished()  QUrl( "http://daily.owncloud.com/master/owncloud/remote.php/webdav/BBB/example4.file-chunking-1430717107-4-3" )  FINISHED WITH STATUS 401 "Error downloading http://daily.owncloud.com/master/owncloud/remote.php/webdav/BBB/example4.file-chunking-1430717107-4-3 - server replied: Internal Server Error" QVariant(int, 500) QVariant(QString, "Internal Server Error")
03-05 08:35:58:493 0x10376bcc0 OCC::PropagateUploadFileQNAM::slotPutFinished: "<?xml version="1.0" encoding="utf-8"?>
<d:error xmlns:d="DAV:" xmlns:s="http://sabredav.org/ns">
  <s:exception>Doctrine\DBAL\Exception\UniqueConstraintViolationException</s:exception>
  <s:message>An exception occurred while executing 'INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "parent", "path_hash", "path", "name", "storage") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["5", "9", 1425540955, 20971520, "2d01bd1e7d5a38a55800821930e7cb8f", 1425540955, 27, "29", "33b876b01b1fea84b27363f7298b5f01", "files\/BBB\/example4.file", "example4.file", "3"]:

SQLSTATE[23000]: Integrity constraint violation: 19 columns storage, path_hash are not unique</s:message>
</d:error>
"
03-05 08:35:58:493 0x10376bcc0 OCC::SyncJournalErrorBlacklistRecord::update: blacklisting  "BBB/example4.file"  for  25 , retry count  1
03-05 08:35:58:493 0x10376bcc0 OCC::SyncJournalDb::updateErrorBlacklistEntry: set blacklist entry for  "BBB/example4.file" 1 "Error downloading http://daily.owncloud.com/master/owncloud/remote.php/webdav/BBB/example4.file-chunking-1430717107-4-3 - server replied: Internal Server Error (An exception occurred while executing 'INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "parent", "path_hash", "path", "name", "storage") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["5", "9", 1425540955, 20971520, "2d01bd1e7d5a38a55800821930e7cb8f", 1425540955, 27, "29", "33b876b01b1fea84b27363f7298b5f01", "files\/BBB\/example4.file", "example4.file", "3"]:

SQLSTATE[23000]: Integrity constraint violation: 19 columns storage, path_hash are not unique)" 1425540958 25 1352370152 ""
03-05 08:35:58:493 0x10376bcc0 OCC::SyncEngine::slotJobCompleted: void OCC::SyncEngine::slotJobCompleted(const OCC::SyncFileItem &) "BBB/example4.file" 2 "Error downloading http://daily.owncloud.com/master/owncloud/remote.php/webdav/BBB/example4.file-chunking-1430717107-4-3 - server replied: Internal Server Error (An exception occurred while executing 'INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "parent", "path_hash", "path", "name", "storage") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["5", "9", 1425540955, 20971520, "2d01bd1e7d5a38a55800821930e7cb8f", 1425540955, 27, "29", "33b876b01b1fea84b27363f7298b5f01", "files\/BBB\/example4.file", "example4.file", "3"]:

SQLSTATE[23000]: Integrity constraint violation: 19 columns storage, path_hash are not unique)"
03-05 08:35:58:500 0x10376bcc0 OCC::SocketApi::sendMessage: SocketApi:  Sending message:  "STATUS:ERROR:/Users/lmaestro/owncloud-servers/beta2/BBB/example4.file"
03-05 08:35:58:501 0x10376bcc0 OCC::BandwidthManager::unregisterUploadDevice: void OCC::BandwidthManager::unregisterUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x11060d880)
03-05 08:35:58:509 0x10376bcc0 OCC::BandwidthManager::registerUploadDevice: void OCC::BandwidthManager::registerUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x10533ba70)
03-05 08:35:58:515 0x10376bcc0 OCC::AbstractNetworkJob::start: !!! OCC::PUTFileJob created for  QUrl( "http://daily.owncloud.com/master/owncloud" )  querying "/aa-test-lu/$/Photo-2014-07-16-09-21-08_0063.JPG"
03-05 08:35:58:610 0x10376bcc0 OCC::BandwidthManager::unregisterUploadDevice: void OCC::BandwidthManager::unregisterUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x10533ba70)
03-05 08:35:58:892 0x10376bcc0 OCC::BandwidthManager::unregisterUploadDevice: void OCC::BandwidthManager::unregisterUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x1048a1110)
03-05 08:35:59:978 0x10376bcc0 OCC::PropagateUploadFileQNAM::slotPutFinished: void OCC::PropagateUploadFileQNAM::slotPutFinished()  QUrl( "http://daily.owncloud.com/master/owncloud/remote.php/webdav/aa-test-lu/$/Photo-2014-07-16-09-21-07_0062.JPG" )  FINISHED WITH STATUS 0 "" QVariant(int, 201) QVariant(QString, "Created")
03-05 08:35:59:979 0x10376bcc0 OCC::SyncJournalFileRecord::SyncJournalFileRecord: OCC::SyncJournalFileRecord::SyncJournalFileRecord(const OCC::SyncFileItem &, const QString &) "/Users/lmaestro/owncloud-servers/beta2/aa-test-lu/$/Photo-2014-07-16-09-21-07_0062.JPG" Retrieved inode  6175402 (previous item inode:  6175402 )
03-05 08:35:59:979 0x10376bcc0 OCC::SyncJournalDb::setFileRecord: "INSERT OR REPLACE INTO metadata (phash, pathlen, path, inode, uid, gid, mode, modtime, type, md5, fileid, remotePerm, filesize) VALUES (?1 , ?2, ?3 , ?4 , ?5 , ?6 , ?7,  ?8 , ?9 , ?10, ?11, ?12, ?13);" 5736743320044993657 47 "aa-test-lu/$/Photo-2014-07-16-09-21-07_0062.JPG" 6175402 0 "1417518450" "0" "c5a6eb270d35a3fed37e6ab1bddc8a87" "00000112ochrzivcagx6" "" 748662
03-05 08:35:59:979 0x10376bcc0 OCC::SyncJournalDb::setUploadInfo: "DELETE FROM uploadinfo WHERE path=?1" "aa-test-lu/$/Photo-2014-07-16-09-21-07_0062.JPG"
03-05 08:35:59:979 0x10376bcc0 OCC::SyncJournalDb::commitInternal: void OCC::SyncJournalDb::commitInternal(const QString &, bool) Transaction commit  "upload file start" and starting new transaction
03-05 08:35:59:979 0x10376bcc0 OCC::SyncEngine::slotJobCompleted: void OCC::SyncEngine::slotJobCompleted(const OCC::SyncFileItem &) "aa-test-lu/$/Photo-2014-07-16-09-21-07_0062.JPG" 4 ""
03-05 08:35:59:981 0x10376bcc0 OCC::SocketApi::sendMessage: SocketApi:  Sending message:  "STATUS:OK:/Users/lmaestro/owncloud-servers/beta2/aa-test-lu/$/Photo-2014-07-16-09-21-07_0062.JPG"
03-05 08:35:59:982 0x10376bcc0 OCC::BandwidthManager::unregisterUploadDevice: void OCC::BandwidthManager::unregisterUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x10b89a330)
03-05 08:35:59:982 0x10376bcc0 OCC::BandwidthManager::registerUploadDevice: void OCC::BandwidthManager::registerUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x110611b00)
03-05 08:35:59:993 0x10376bcc0 OCC::AbstractNetworkJob::start: !!! OCC::PUTFileJob created for  QUrl( "http://daily.owncloud.com/master/owncloud" )  querying "/aa-test-lu/$/Photo-2014-07-16-09-21-08_0064.JPG"
03-05 08:36:00:007 0x10376bcc0 OCC::BandwidthManager::unregisterUploadDevice: void OCC::BandwidthManager::unregisterUploadDevice(OCC::UploadDevice *) OCC::UploadDevice(0x110611b00)
03-05 08:36:00:904 0x10376bcc0 OCC::PropagateUploadFileQNAM::slotPutFinished: void OCC::PropagateUploadFileQNAM::slotPutFinished()  QUrl( "http://daily.owncloud.com/master/owncloud/remote.php/webdav/aa-test-lu/$/Photo-2014-07-16-09-21-08_0063.JPG" )  FINISHED WITH STATUS 0 "" QVariant(int, 201) QVariant(QString, "Created")
03-05 08:36:00:904 0x10376bcc0 OCC::SyncJournalFileRecord::SyncJournalFileRecord: OCC::SyncJournalFileRecord::SyncJournalFileRecord(const OCC::SyncFileItem &, const QString &) "/Users/lmaestro/owncloud-servers/beta2/aa-test-lu/$/Photo-2014-07-16-09-21-08_0063.JPG" Retrieved inode  6175403 (previous item inode:  6175403 )

[PVince81]

@lmaestro Please always include the server logs. Thanks.

[DeepDiver1975]

And server config - basically please use the issue template!

[PVince81]

Please also include the server configuration. Most importantly, what database is used.

[jnfrmarks]

@PVince81

I was able to reproduce this error using the test_uploadFiles.py test, scenario 2, in smashbox running against a stable8 server on CentOs with slqlite db

{"reqId":"d0afdc2483f2771145dd278f4bcb678f","remoteAddr":"x.x.x.x","app":"remote","message":"An exception occurred while executing 'INSERT INTO \"oc_filecache\" (\"mimepart\", \"mimetype\", \"mtime\", \"size\", \"etag\", \"storage_mtime\", \"permissions\", \"parent\", \"path_hash\", \"path\", \"name\", \"storage\") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [\"3\", \"8\", 1425573653, 2000000, \"aa59df0f7eb59c2b5b4253c411d2f82a\", 1425573653, 27, \"21\", \"64953104e03d0aa3bb104658123f96ab\", \"files\\\/localShareDir\\\/TEST_FILE_NEW_USER_SHARE_161.dat\", \"TEST_FILE_NEW_USER_SHARE_161.dat\", \"3\"]:\n\nSQLSTATE[23000]: Integrity constraint violation: 19 columns storage, path_hash are not unique","level":4,"time":"2015-03-05T16:40:54+00:00"}
{"reqId":"cd1a03fb7116e8a33a869a378611edac","remoteAddr":"x.x.x.x","app":"remote","message":"An exception occurred while executing 'INSERT INTO \"oc_filecache\" (\"mimepart\", \"mimetype\", \"mtime\", \"size\", \"etag\", \"storage_mtime\", \"permissions\", \"parent\", \"path_hash\", \"path\", \"name\", \"storage\") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params [\"3\", \"8\", 1425573827, 2000000, \"55c1b58793798542383233a00e863d6e\", 1425573827, 27, \"21\", \"fb9ff0111739e24799ac18806c546a2e\", \"files\\\/localShareDir\\\/TEST_FILE_NEW_USER_SHARE_466.dat\", \"TEST_FILE_NEW_USER_SHARE_466.dat\", \"3\"]:\n\nSQLSTATE[23000]: Integrity constraint violation: 19 columns storage, path_hash are not unique","level":4,"time":"2015-03-05T16:43:51+00:00"}

[jnfrmarks]

See #13934

[DeepDiver1975]

This is the code fragment where this happens: https://github.com/owncloud/core/blob/master/lib/private/files/cache/cache.php#L248

What's happening?

The client uploads two files in parallel into the same folder - which means two requests are being processed in parallel on the server.
Both processes detect changes to the folder, scan its content and at least one of the processes detects the file which just got uploaded by the other process and tries to add it to the cache.

This is the point where the operation fails because the data was meanwhile inserted and results in this 'Integrity constraint violation'.

Short term solution

We could try to use insertIfNotExists() - which might require some tweaks:

• insertIfNotExists() is returning boolean and not the id of the inserted/existing record
• On sqlite the implementation of insertIfNoExists() in not atomic

Mid term solution / discussion

• Resturcture the scanner logic to either ignore detected files ??
• move scanner to an async job with the guarantee that this job is not executed multiple times ??
• Lock folders ??

[dragotin]

Maybe switch the SQL statement to INSERT OR REPLACE INTO filecache will do the trick?

See https://www.sqlite.org/lang_insert.html

[DeepDiver1975]

Maybe switch the SQL statement to INSERT OR REPLACE INTO filecache will do the trick?

Thanks for this input - I was playing with insert ignore ...

[DeepDiver1975]

potential fix #14734

[dragotin]

yes, fix 🍻

[luciamaestro]

I can't reproduce it in the latest version so I close this issue.

[PVince81]

Should we reopen ? Because it was shown that not every such error was fixed by the linked PR.

[sleepysmurf]

Yes I am still having the problem and the linked PR does not explain how to properly patch the issue. Are there some files we need to replace? Or do we need to reinstall the whole thing? :/ sorry for being newbish

[DeepDiver1975]

@sleepysmurf pease wait until tomorrow when 8.0.3 will be released - it contains the fix. THX

[DeepDiver1975]

Should we reopen ? Because it was shown that not every such error was fixed by the linked PR

not really - we have enough open issues to reflect that status 🙊