collectives icon indicating copy to clipboard operation
collectives copied to clipboard
verified publisher icon nextcloud

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

Open arnowelzel opened this issue 1 year ago • 2 comments

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:

  • 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 avatar Jul 08 '24 14:07 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 avatar Jul 08 '24 15:07 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.

arnowelzel avatar Jul 08 '24 18:07 arnowelzel