Searching in Collectives causes error "SQLSTATE[HY000]: General error: 5 database is locked"

[arnowelzel]

Describe the bug
When searching for content in Collectives the search never shows any result, because the backend produces the following error:

[collectives] Error: Collectives App Error: SQLSTATE[HY000]: General error: 5 database is locked
	GET /apps/collectives/_api/2/_pages/search?searchString=test
	from <IP address> by <user> at 8 Jul 2024, 16:08:57

To Reproduce
Steps to reproduce the behavior:

1. Open a collective.
2. Click in the search field.
3. Enter a tearm to search for and press [Tab].
4. The search result only shows titles but no content matches.

Expected behavior
Search result shows document with matches as well and there is no error in the server log.

Screenshots

Server details:

• Collectives app version: 2.12.0
• Nextcloud version: 29.0.3
• PHP Version: 8.3
• Database: MariaDB 10.11

Client details:

• OS: Windows
• Browser: Firefox, Chrome, Edge, Vivaldi
• Browser version: latest versions as of June 8, 2024
• Device: desktop

Logs

Nextcloud log (data/nextcloud.log)

{"reqId":"YK8iuD1DPqDZ5aYjCCEI","level":3,"time":"2024-07-08T14:20:47+00:00","remoteAddr":"*** sensitive parameters replaced ***","user":"*** sensitive parameters replaced ***","app":"collectives","method":"GET","url":"/apps/collectives/_api/2/_pages/search?searchString=test","message":"Collectives App Error: SQLSTATE[HY000]: General error: 5 database is locked","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:127.0) Gecko/20100101 Firefox/127.0","version":"29.0.3.4","exception":{"Exception":"PDOException","Message":"SQLSTATE[HY000]: General error: 5 database is locked","Code":"HY000","Trace":[{"file":"/var/www/nextcloud/apps/collectives/vendor/teamtnt/tntsearch/src/Engines/SqliteEngine.php","line":144,"function":"execute","class":"PDOStatement","type":"->"},{"file":"/var/www/nextcloud/apps/collectives/vendor/teamtnt/tntsearch/src/Engines/SqliteEngine.php","line":136,"function":"updateInfoTable","class":"TeamTNT\\TNTSearch\\Engines\\SqliteEngine","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/nextcloud/apps/collectives/vendor/teamtnt/tntsearch/src/Engines/EngineTrait.php","line":170,"function":"setStemmer","class":"TeamTNT\\TNTSearch\\Engines\\SqliteEngine","type":"->"},{"file":"/var/www/nextcloud/apps/collectives/vendor/teamtnt/tntsearch/src/Indexer/TNTIndexer.php","line":101,"function":"setLanguage","class":"TeamTNT\\TNTSearch\\Engines\\SqliteEngine","type":"->"},{"file":"/var/www/nextcloud/apps/collectives/lib/Search/FileSearch/FileSearcher.php","line":96,"function":"setLanguage","class":"TeamTNT\\TNTSearch\\Indexer\\TNTIndexer","type":"->"},{"file":"/var/www/nextcloud/apps/collectives/lib/Search/FileSearch/FileSearcher.php","line":81,"function":"selectIndex","class":"OCA\\Collectives\\Search\\FileSearch\\FileSearcher","type":"->"},{"file":"/var/www/nextcloud/apps/collectives/lib/Service/SearchService.php","line":69,"function":"selectIndexFile","class":"OCA\\Collectives\\Search\\FileSearch\\FileSearcher","type":"->"},{"file":"/var/www/nextcloud/apps/collectives/lib/Controller/PageController.php","line":95,"function":"searchCollective","class":"OCA\\Collectives\\Service\\SearchService","type":"->"},{"file":"/var/www/nextcloud/apps/collectives/lib/Controller/ErrorHelper.php","line":23,"function":"OCA\\Collectives\\Controller\\{closure}","class":"OCA\\Collectives\\Controller\\PageController","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/nextcloud/apps/collectives/lib/Controller/PageController.php","line":92,"function":"handleErrorResponse","class":"OCA\\Collectives\\Controller\\PageController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":232,"function":"contentSearch","class":"OCA\\Collectives\\Controller\\PageController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":138,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":184,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":338,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1050,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":49,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/apps/collectives/vendor/teamtnt/tntsearch/src/Engines/SqliteEngine.php","Line":144,"message":"Collectives App Error: SQLSTATE[HY000]: General error: 5 database is locked","exception":{"errorInfo":["HY000",5,"database is locked"]},"CustomMessage":"Collectives App Error: SQLSTATE[HY000]: General error: 5 database is locked"}}

Browser log

(not relevant)

[arnowelzel]

The problem occurs inside the "tntsearch" component - the affected method is \TeamTNT\TNTSearch\Engines\SqliteEngine::updateInfoTable:

    public function updateInfoTable($key, $value)
    {
        $this->updateInfoTableStmt = $this->index->prepare("UPDATE info SET value = :value WHERE key = :key");
        $this->updateInfoTableStmt->bindValue(':key', $key);
        $this->updateInfoTableStmt->bindValue(':value', $value);
        $this->updateInfoTableStmt->execute();
    }

When I comment this out for testing purposes, the search works fine, however this is of course not a viable solution. But maybe the way how tntsearch is used affects this as well.

    public function updateInfoTable($key, $value)
    {
        /*
        $this->updateInfoTableStmt = $this->index->prepare("UPDATE info SET value = :value WHERE key = :key");
        $this->updateInfoTableStmt->bindValue(':key', $key);
        $this->updateInfoTableStmt->bindValue(':value', $value);
        $this->updateInfoTableStmt->execute();
        */
    }

Overall I understand the motivation here to have full text search in Collectives - but using a solution based on SQLite may not be the best choice overall since it is all file based. There is a reason why Nextcloud recommends using a database server.

[arnowelzel]

I moved data folder to another filesystem which is mounted as a block device and not via NFS. Here the search works fine again. However I wonder, if a am the first with this issue.