Laravel: Error while sending STMT_PREPARE packet

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# /etc/mysql/mysql.conf.d/mysqld.cnf または my.cnf

[mysqld]
# 接続タイムアウトを延長（秒）
wait_timeout = 28800
interactive_timeout = 28800

# パケットサイズを増加
max_allowed_packet = 256M

# 接続数を増加
max_connections = 500

1
2

# 設定を反映
sudo systemctl restart mysql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

// config/database.php
'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
    'options' => [
        // 接続エラー時に再接続を試みる
        PDO::ATTR_PERSISTENT => false,  // 永続的接続を無効化
        PDO::ATTR_TIMEOUT => 60,        // タイムアウト設定
    ],
],

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

// app/Providers/AppServiceProvider.php
use Illuminate\Queue\Events\JobProcessed;
use Illuminate\Queue\Events\JobProcessing;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Queue;

public function boot(): void
{
    // ジョブ処理前に接続をリフレッシュ
    Queue::before(function (JobProcessing $event) {
        DB::reconnect();
    });

    // または、ジョブ処理後に接続を切断
    Queue::after(function (JobProcessed $event) {
        DB::disconnect();
    });
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

<?php

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;

class LongRunningJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public function handle(): void
    {
        // 長時間処理の前に再接続
        DB::reconnect();

        // 処理
        foreach ($this->items as $item) {
            $this->processItem($item);

            // 定期的に再接続（大量処理の場合）
            if ($this->shouldReconnect()) {
                DB::reconnect();
            }
        }
    }

    private function shouldReconnect(): bool
    {
        static $counter = 0;
        return ++$counter % 1000 === 0;
    }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# タイムアウトを設定してワーカーを起動
php artisan queue:work --timeout=600 --sleep=3 --tries=3

# Supervisorの設定例
# /etc/supervisor/conf.d/laravel-worker.conf
[program:laravel-worker]
process_name=%(program_name)s_%(process_num)02d
command=php /var/www/html/artisan queue:work --sleep=3 --tries=3 --timeout=600
autostart=true
autorestart=true
numprocs=4
redirect_stderr=true
stdout_logfile=/var/www/html/storage/logs/worker.log

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

// 接続が有効か確認するヘルパー
function ensureDbConnection(): void
{
    try {
        DB::connection()->getPdo();
    } catch (\Exception $e) {
        DB::reconnect();
    }
}

// 使用例
public function handle(): void
{
    ensureDbConnection();
    // 処理
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

// config/horizon.php
'environments' => [
    'production' => [
        'supervisor-1' => [
            'connection' => 'redis',
            'queue' => ['default'],
            'balance' => 'auto',
            'processes' => 10,
            'tries' => 3,
            'timeout' => 600,  // タイムアウト設定
        ],
    ],
],

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

use Illuminate\Support\Facades\DB;

function executeWithRetry(callable $callback, int $maxRetries = 3)
{
    $attempts = 0;
    $lastException = null;

    while ($attempts < $maxRetries) {
        try {
            return $callback();
        } catch (\PDOException $e) {
            $lastException = $e;
            $attempts++;

            if (str_contains($e->getMessage(), 'server has gone away') ||
                str_contains($e->getMessage(), 'STMT_PREPARE')) {
                DB::reconnect();
                sleep(1);
                continue;
            }

            throw $e;
        }
    }

    throw $lastException;
}

// 使用例
$result = executeWithRetry(function () {
    return DB::table('users')->get();
});

1
2
3
4
5
6
7

// app/Console/Kernel.php
protected function schedule(Schedule $schedule): void
{
    $schedule->call(function () {
        DB::reconnect();
    })->everyFiveMinutes();
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

// 接続状態を確認
Route::get('/db-status', function () {
    try {
        $pdo = DB::connection()->getPdo();
        $status = DB::select('SHOW STATUS LIKE "Threads_connected"');
        $variables = DB::select('SHOW VARIABLES LIKE "wait_timeout"');

        return [
            'connected' => true,
            'threads' => $status,
            'timeout' => $variables,
        ];
    } catch (\Exception $e) {
        return ['error' => $e->getMessage()];
    }
});