smashbox fails due to duplicated entries in the filecache table

[jvillafanez]

This is happening from time to time in several versions of ownCloud.

Checked with 8.1.3, 8.2.0 and 9.0 (this last one after upgrading from 8.0.6)

{\"Message\":\"HTTP\\\/1.1 500 An exception occurred while executing 'INSERT INTO `filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`parent`,`path_hash`,`path`,`name`,`storage`) SELECT ?,?,?,?,?,?,?,?,?,?,?,? FROM `filecache` WHERE `storage` = ? AND `path_hash` = ? HAVING COUNT(*) = 0' with params [\\\"1\\\", \\\"2\\\", 1456130211, -1, \\\"56cac8a32fd2b\\\", 1456130211, 31, 534, \\\"9b2997f0595af58997b37b050b6966a7\\\", \\\"files_encryption\\\\\\\/keys\\\\\\\/files\\\", \\\"files\\\", \\\"17\\\", \\\"17\\\", \\\"9b2997f0595af58997b37b050b6966a7\\\"]:\\n\\nSQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '17-9b2997f0595af58997b37b050b6966a7' for key 'fs_storage_path_hash'\",\"Exception\":\"Sabre\\\\DAV\\\\Exception\",\"Code\":0,\"Trace\":\"#0 \\\/opt\\\/owncloud\\\/apps\\\/dav\\\/lib\\\/connector\\\/sabre\\\/file.php(469): OCA\\\\DAV\\\\Connector\\\\Sabre\\\\File->convertToSabreException(Object(Doctrine\\\\DBAL\\\\Exception\\\\UniqueConstraintViolationException))\\n#1 \\\/opt\\\/owncloud\\\/apps\\\/dav\\\/lib\\\/connector\\\/sabre\\\/file.php(104): OCA\\\\DAV\\\\Connector\\\\Sabre\\\\File->createFileChunked(Resource id #39)\\n#2 \\\/opt\\\/owncloud\\\/apps\\\/dav\\\/lib\\\/connector\\\/sabre\\\/directory.php(134): OCA\\\\DAV\\\\Connector\\\\Sabre\\\\File->put(Resource id #39)\\n#3 \\\/opt\\\/owncloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(1036): OCA\\\\DAV\\\\Connector\\\\Sabre\\\\Directory->createFile('TEST_FILE_DELET...', Resource id #39)\\n#4 \\\/opt\\\/owncloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/CorePlugin.php(525): Sabre\\\\DAV\\\\Server->createFile('TEST_FILE_DELET...', Resource id #39, NULL)\\n#5 [internal function]: Sabre\\\\DAV\\\\CorePlugin->httpPut(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#6 \\\/opt\\\/owncloud\\\/3rdparty\\\/sabre\\\/event\\\/lib\\\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\\n#7 \\\/opt\\\/owncloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(459): Sabre\\\\Event\\\\EventEmitter->emit('method:PUT', Array)\\n#8 \\\/opt\\\/owncloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(248): Sabre\\\\DAV\\\\Server->invokeMethod(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#9 \\\/opt\\\/owncloud\\\/apps\\\/dav\\\/appinfo\\\/v1\\\/webdav.php(54): Sabre\\\\DAV\\\\Server->exec()\\n#10 \\\/opt\\\/owncloud\\\/remote.php(137): require_once('\\\/opt\\\/owncloud\\\/a...')\\n#11 {main}\",\"File\":\"\\\/opt\\\/owncloud\\\/apps\\\/dav\\\/lib\\\/connector\\\/sabre\\\/file.php\",\"Line\":535}","level":4,"time":"2016-02-22T08:36:51+00:00"}

@nickvergessen I don't know how we want to handle this but I haven't seen a full successful run in all the environments yet.

[nickvergessen]

There are quite some issues in core: https://github.com/owncloud/core/issues?utf8=%E2%9C%93&q=is%3Aissue+is%3Aopen+constraint+violation

I however can not reproduce this, so not sure what this is about. Seems like your DB does not read commited entries accross transactions

[jvillafanez]

Tests are run against a default installation of mysql. Other than resetting the mysql password, I don't see any other change.

[nickvergessen]

Can you check the transaction isolation level? https://github.com/owncloud/core/issues/20555#issuecomment-157331571

[jvillafanez]

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

Something to fix in the docker image...

[jvillafanez]

I've changed the mysql startup command to add the --transaction-isolation=READ-COMMITTED . I've checked it's set in the DB correctly; currently running tests

[jvillafanez]

It's still happening.

[nickvergessen]

Still happening with current master? We now have a check on the admin page regarding the transaction isolation level when it is not correct. So in case you still get the error, please check the admin page if the warning is displayed.

[jvillafanez]

Is READ-COMMITTED the expected isolation level?

We'll need to wait until the tests for 9.1 (the current master) are ready. We'll keep an eye on this.

CC: @SergioBertolinSG

[nickvergessen]

Yes, read-committed https://doc.owncloud.org/server/8.2/admin_manual/configuration_database/linux_database_configuration.html#mysql-mariadb-with-binary-logging-enabled