laravel-scout-tntsearch-driver
laravel-scout-tntsearch-driver copied to clipboard
teamtnt
PDOException: SQLSTATE[HY000]: General error: 5 database is locked
Why am I experiencing such an error in the log file? Available in the index storage file
I have an index file.I used this command.I get this error when I search later.Could it be because of the query at the same time? @team5ru
[2017-01-16 13:10:18] local.ERROR: PDOException: SQLSTATE[HY000]: General error: 5 database is locked in /var/www/laravel/vendor/teamtnt/tntsearch/src/TNTSearch.php:404 Stack trace: #0 /var/www/laravel/vendor/teamtnt/tntsearch/src/TNTSearch.php(404): PDO->query('SELECT * FROM i...') #1 /var/www/laravel/vendor/teamtnt/tntsearch/src/TNTSearch.php(114): TeamTNT\TNTSearch\TNTSearch->setStemmer() #2 /var/www/laravel/vendor/teamtnt/laravel-scout-tntsearch-driver/src/Engines/TNTSearchEngine.php(37): TeamTNT\TNTSearch\TNTSearch->selectIndex('news.index')
Same issue here, I use a single queue to handle indexing so concurrency on insert/update would be weird. What could be the issue here?
PDOException: SQLSTATE[HY000]: General error: 5 database is locked in /home/forge/**/vendor/teamtnt/tntsearch/src/Indexer/TNTIndexer.php:87
Stack trace:
#0 /home/forge/**/vendor/teamtnt/tntsearch/src/Indexer/TNTIndexer.php(87): PDO->exec('INSERT INTO inf...')
#1 /home/forge/**/vendor/teamtnt/tntsearch/src/TNTSearch.php(371): TeamTNT\TNTSearch\Indexer\TNTIndexer->setStemmer(Object(TeamTNT\TNTSearch\Stemmer\PorterStemmer))
#2 /home/forge/**/vendor/teamtnt/laravel-scout-tntsearch-driver/src/Engines/TNTSearchEngine.php(38): TeamTNT\TNTSearch\TNTSearch->getIndex()
#3 /home/forge/**/vendor/laravel/scout/src/Jobs/MakeSearchable.php(43): TeamTNT\Scout\Engines\TNTSearchEngine->update(Object(Illuminate\Database\Eloquent\Collection))
#4 [internal function]: Laravel\Scout\Jobs\MakeSearchable->handle()
#5 /home/forge/**/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(29): call_user_func_array(Array, Array)
#6 /home/forge/**/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(87): Illuminate\Container\BoundMethod::Illuminate\Container\{closure}()
.
.
.
Hello,
Same issue here as well.
PDOException: SQLSTATE[HY000]: General error: 5 database is locked in /var/www/koshershopping.com/vendor/teamtnt/tntsearch/src/Indexer/TNTIndexer.php:306 Stack trace: #0 /var/www/koshershopping.com/vendor/teamtnt/tntsearch/src/Indexer/TNTIndexer.php(306): PDOStatement->execute()
How to proceed in this scenario?
@richardblondet removing the tntsearch db files and rebuilding the db solves the issue most of the time, seems likes it gets corrupted by concurrency now and then..
I am experiencing the same thing. For me it is using multiple queue workers through supervisor and using sqlite as the index db. Only one worker gets to work on the database at a time. I think setting PRAGMA journal_mode=wal; will solve the problem. But I have not tested.
Same error here. For me it happens when I use TNTIndexer and try to upload multiple files at the same time.
For anyone still perplexed by this, it's because you're creating too many models too quickly and it's indexing just as fast. Simple solution is to bring the app down with php artisan down and ensure that scout has the setting 'queue' => env('SCOUT_QUEUE', true),. Worked for me!
I have queue enabled and the same thing happens to be. Is there was a way to disable indexing on the model and only have it work when running the index manually?
Any news on this? I think this can be solved with a sleep wait time for updating the sqlite index. This issue makes this driver unusable
For anyone wondering i just find the issue in my case:
I have php artisan horizon and php artisan queue:work running
Seems that both handle the queue so its causing that sometimes two proceses try to access to the same database
As a good tip you can use the command lsof path/to/db/file.index to know which process are tryin to access to the database at the same time it will return the different process id, the run ps aux | grep change_by_your_process_id to know the process name
Had the same problem: If the supervisor is used (https://laravel.com/docs/5.6/queues#supervisor-configuration) and config parameter numprocs is set to more than 1 (8 by laravel docs).
When indexing a lot of models (not by artisan bulk import command but for example by ENTITY::select()->searchable();) scout chunks the models (see config/scout.php):
'chunk' => [ 'searchable' => 500, 'unsearchable' => 500, ],
and creates multiple jobs, each 500 models. Now the 8 running queue workers start to pickup these job in parallel or at least they do not wait for the first job to finish and want to access the sqlite db at the same time.
Solution: Define an extra queue (i am using my own searchable trait and job, but config scout.queue.queue should do the job as well) and configured a second "program" in the supervisor to run only this queue with numprocs=1
Now there should be 8 queue workers/listeners for the default queue and 1 for the search index queue.
let me add an example for usage of laravel/horizon:
//config/scout.php
'queue' => [
'queue' => 'search'
],
//config/horizon.php
'environments' => [
'production' => [
'supervisor-1' => [
'connection' => 'redis',
'queue' => ['default'],
'balance' => 'simple',
'processes' => 10,
'tries' => 3,
],
'supervisor-2' => [
'connection' => 'redis',
'queue' => ['search'],
'balance' => 'simple',
'processes' => 1,
'tries' => 3,
],
],
But there is one problem: you cannot disable queuing via env. https://github.com/laravel/scout/issues/289
Experiencing the same issue here. Happens when I loop through a large amount of JSON data and try to do a big relationship sync with them.
Ok... I may have resolved this issue on my side, that was only on prod server by the way. I'm not sure why but it definitely have to do with queue processes yes. I switched config queue to a database driver. Obviously created the jobs and failed table through artisan and "voila"... so far so good :)
For those coming here for solutions... in my case I was custom building the toSearchableArray() method without an 'id' property but the indexer expects 'id' column to be included in the result.
My specific issue was that the .index files in storage/ didn't have the proper permissions. Was able to fix it with a simple chown command.
For anyone still getting this error and using queued jobs with more than 1 worker, you can now prevent the search indexing jobs from overlapping so they don't all try to access the sqlite database at the same time (which is the root of the error).
Add these 2 jobs to your app:
<?php
declare(strict_types=1);
namespace App\Jobs;
use Illuminate\Bus\Queueable;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\Middleware\WithoutOverlapping;
use Illuminate\Queue\SerializesModels;
use Laravel\Scout\Jobs\MakeSearchable as MakeSearchableBase;
class MakeSearchable extends MakeSearchableBase
{
use Dispatchable;
use InteractsWithQueue;
use Queueable;
use SerializesModels;
/**
* @return array<int, object>
*/
public function middleware(): array
{
return [
(new WithoutOverlapping('scout.index'))
->shared()
->releaseAfter(10)
->expireAfter(30),
];
}
}
<?php
declare(strict_types=1);
namespace App\Jobs;
use Illuminate\Bus\Queueable;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\Middleware\WithoutOverlapping;
use Illuminate\Queue\SerializesModels;
use Laravel\Scout\Jobs\RemoveFromSearch as RemoveFromSearchAliasBase;
class RemoveFromSearch extends RemoveFromSearchAliasBase
{
use Dispatchable;
use InteractsWithQueue;
use Queueable;
use SerializesModels;
/**
* @return array<int, object>
*/
public function middleware(): array
{
return [
(new WithoutOverlapping('scout.index'))
->shared()
->releaseAfter(10)
->expireAfter(30),
];
}
}
Then in a service provider's register method, override the jobs that do the work with yours:
\Laravel\Scout\Scout::makeSearchableUsing(MakeSearchable::class);
\Laravel\Scout\Scout::removeFromSearchUsing(RemoveFromSearch::class);
Relevant issue: https://github.com/teamtnt/laravel-scout-tntsearch-driver/issues/151
