SQLite image file is malformed

[Maclay74]

We use TNT search for a quite high-loaded service and run into an problem with SQLite.

Seems like something corrupts index file, but it's not certain, what exactly.
I guess it can be because of concurrent access to the index.

Did anybody have the similar issue and know how to solve it?
For now we have to monitor our system and re-index every time when index break.

Thanks.

[nticaric]

What kind of error does it throw when the index is broken?

[Maclay74]

request.CRITICAL: Uncaught PHP Exception PDOException: "SQLSTATE[HY000]: General error: 11 database disk image is malformed" at /var/www/***/***.***.com/***/vendor/teamtnt/tntsearch/src/TNTSearch.php line 399 {"exception":"[object] (PDOException(code: HY000): SQLSTATE[HY000]: General error: 11 database disk image is malformed at /var/www/***/***/***/vendor/teamtnt/tntsearch/src/TNTSearch.php:399)"} {"url":"/en/***/playstation/search?query=Language%20Support%20P","ip":"***","http_method":"GET","server":"***.***.com","referrer":"https://***.***.com/en/***/playstation","file":"/var/www/***/***.***.com/***/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/EventListener/ExceptionListener.php","line":101,"class":"Symfony\\Component\\HttpKernel\\EventListener\\ExceptionListener","function":"logException"}

[Maclay74]

Hello, @nticaric, any clues about that?

[stokic]

I can't remember that we had problems like this reported before. How are you updating the sqlite index, async via some queue system or ...? Also when you say "high loaded" service, could you share what this means exactly ie. how many visitors etc...? Quick google search suggests there might be various reasons this can happen :/

btw I'm curious which website/service are you using it on if you could tell us please? We would like to gather a list of "heavy" users and do some research about future improvements. You can email us: info [at] tntstudio [dot] hr if you don't want to say publicly.

[Maclay74]

Hello @stokic, thanks for reply.

Unfortunately, at this moment I can't share details about our load and company. I'd like to, but it wasn't approved.

Although, in order to update index, we use methods to delete and persist:

$this->tnt->selectIndex($this->indexName);
$index = $this->tnt->getIndex();
$index->delete($issue->getId());

And then, persist:

$this->tnt->selectIndex($this->indexName);
$index = $this->tnt->getIndex();

/** @var EntityManager $em */
$em = $this->container->get('doctrine')->getEntityManager();

$connection = $em->getConnection();
$sql = $this->generateQuery($issue->getId());
$query = $connection->prepare($sql);
$query->execute();

$index->setPrimaryKey('id_0');

foreach ($query->fetchAll() as $row) {
     $index->insert($row);
}

So, update does both commands synchronously.

[Maclay74]

@stokic any news on this field? Maybe suggestions of usage?