race condition in insertOrUpdate queries (completely reworked in 9.0 - try to cu…rricumvent them in stable8.1/2 code too) - the reason for this is that those queries are technically two queries (there is a subquery) and if a parallel insert happens after the subquery is executed this could happen
error message:
```
{"Message":"An exception occurred while executing 'INSERT INTO "oc_preferences" ("configvalue","userid","appid","configkey") SELECT ?,?,?,? FROM "oc_preferences" WHERE "configkey" = ? AND "userid" = ? AND "appid" = ? HAVING COUNT(*) = 0' with params [1455358499.591, "foo@bar.baz", "files_sharing", "last_propagate", "last_propagate", "foo@bar.baz", "files_sharing"]:
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL: Key (userid, appid, configkey)=(foo@bar.baz, files_sharing, last_propagate) already exists.","Code":0,"Trace":"
#0 3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(116): Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))
#1 3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(996): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOPgSql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'INSERT INTO "oc...', Array)
#2 lib/private/db/connection.php(151): Doctrine\DBAL\Connection->executeUpdate('INSERT INTO "oc...', Array, Array)
#3 lib/private/db/adapter.php(93): OC\DB\Connection->executeUpdate('INSERT INTO `*P...', Array)
#4 lib/private/db/connection.php(190): OC\DB\Adapter->insertIfNotExist('*PREFIX*prefere...', Array, Array)
#5 lib/private/allconfig.php(217): OC\DB\Connection->insertIfNotExist('*PREFIX*prefere...', Array, Array)
#6 apps/files_sharing/lib/propagation/recipientpropagator.php(83): OC\AllConfig->setUserValue('foo@bar.baz', 'files_sharing', 'last_propagate', 1455358499.591)
#7 apps/files_sharing/lib/mountprovider.php(64): OCA\Files_Sharing\Propagation\RecipientPropagator->propagateDirtyMountPoints(Array)
#8 lib/private/files/config/mountprovidercollection.php(61): OCA\Files_Sharing\MountProvider->getMountsForUser(Object(OC\User\User), Object(OC\Files\Storage\StorageFactory))
#9 [internal function]: OC\Files\Config\MountProviderCollection->OC\Files\Config\{closure}(Object(OCA\Files_Sharing\MountProvider))
#10 lib/private/files/config/mountprovidercollection.php(62): array_map(Object(Closure), Array)
#11 lib/private/files/filesystem.php(425): OC\Files\Config\MountProviderCollection->getMountsForUser(Object(OC\User\User))
#12 apps/files_sharing/lib/cache.php(67): OC\Files\Filesystem::initMountPoints('foo@bar.baz')
#13 apps/files_sharing/lib/cache.php(104): OC\Files\Cache\Shared_Cache->getSourceCache('')
#14 lib/private/files/view.php(1215): OC\Files\Cache\Shared_Cache->get('')
#15 apps/files/appinfo/remote.php(59): OC\Files\View->getFileInfo('')
#16 [internal function]: {closure}(Object(Sabre\HTTP\Request), Object(Sabre\HTTP\Response))
#17 3rdparty/sabre/event/lib/EventEmitterTrait.php(105): call_user_func_array(Object(Closure), Array)
#18 3rdparty/sabre/dav/lib/DAV/Server.php(456): Sabre\Event\EventEmitter->emit('beforeMethod', Array)
#19 3rdparty/sabre/dav/lib/DAV/Server.php(254): Sabre\DAV\Server->invokeMethod(Object(Sabre\HTTP\Request), Object(Sabre\HTTP\Response))
#20 apps/files/appinfo/remote.php(83): Sabre\DAV\Server->exec()
#21 remote.php(132): require_once('/var/www/ownclo...')
#22 {main}","File":"3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php","Line":63}
```
and:
```
error while running hook (OCA\Files_Versions\Hooks::write_hook): An exception occurred while executing 'INSERT INTO "oc_filecache" ("mimepart","mimetype","mtime","size","etag","storage_mtime","permissions","parent","path_hash","path","name","storage") SELECT ?,?,?,?,?,?,?,?,?,?,?,? FROM "oc_filecache" WHERE "storage" = ? AND "path_hash" = ? HAVING COUNT(*) = 0' with params [1, 2, 1455369346, -1, "56bf2c8286739", 1455369346, 31, 92362664, "6b25e3ed6d9bb265731382b35b1d54ff", "files_versions\/data\/scripts\/dtc", "dtc", 17184, 17184, "6b25e3ed6d9bb265731382b35b1d54ff"]:
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "fs_storage_path_hash"
DETAIL: Key (storage, path_hash)=(17184, 6b25e3ed6d9bb265731382b35b1d54ff) already exists.
```
Since we implemented a non standard insertIfNotExists [we cannot fix this by db specific SQL extensions](https://github.com/owncloud/core/pull/22443#issuecomment-185090142). While the above use cases are fixed for OC 9 because the code for the propagation has changed other code paths using the current insertIfNotExists may run into this race condition.
Depending on the not exists parameter this race condition might throw the uniqe index violation error. Adding the primary key to the conditions is not always possible, would change the semantics of the query and we could still run into the race condition. It might be possible to use DB specific SQL extensions to ignore the error. Or we just catch the Exception and log it as a warning?
cc @MorrisJobke, @DeepDiver1975, @nickvergessen