search

nextcloud / collectives

Collectives is a Nextcloud App for activist and community projects to organize together.
GNU Affero General Public License v3.0
88 stars 14 forks source link

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

Open arnowelzel opened 1 month ago

arnowelzel commented 1 month ago

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 grafik

Server details:

Client details:

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 commented 1 month ago

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 commented 1 month ago

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.