Error while sending STMT_PREPARE packet. PID=2

Question

I have no idea why I'm experiencing this bug.

I have the following script:

foreach($brands as $brand){ // about 600items for this loop
    ....
    ....
    DB::table('mailing_list')->insert(array(
                        'email'     => $email,
                        'source'    => $source,
                        'source_url'=> $brand->external_url,
                        'facebook_url'  => $facebook_url,
                        'instagram_id'  => $instagram_username,
                        'instagram_url' => $instagram_url,
                        'instagram_followers'   => $instagram_followers
                    ));
}

which breaks always at the same item

Error while sending STMT_PREPARE packet. PID=2 (SQL: insert into `mailing_list` (`email`, `source`, `source_url`, `facebook_url`, `instagram_id`, `instagram_url`, `instagram_followers`) values ([email protected], source, www.url.com, https://www.facebook.com/url, some_username, http://instagram.com/url, 501))

Before I was getting 29 queries executed, now 34.

I would like to understand this error better: it might be the single entry breaking it, but even if the data I posted are gibberish the actual ones look fine to me.

WHAT I'VE TRIED:

set_time_limit(3600);
DB::connection()->disableQueryLog();
DB::reconnect(Config::get('database.default')); // this one after each foreach loop

Answer 1

The real reason and solution of the problem can be found in my blog post:

Laravel 4 and "Error while sending STMT_PREPARE packet"

It's impossible to describe the whole answer here but, long story short, there was a bug in Laravel 4's \Illuminate\Database\MySqlConnection class. Specifically, in its causedByLostConnection method which looked like this:

/**
 * Determine if the given exception was caused by a lost connection.
 *
 * @param  \Illuminate\Database\QueryException
 * @return bool
 */
protected function causedByLostConnection(QueryException $e)
{
    return str_contains($e->getPrevious()->getMessage(), 'server has gone away');
}

Unfortunately, "server has gone away" is not the only message indicating that a connection with MySQL was lost.

The same method in Laravel 5 checks for 6 additional messages and this solves the problem:

/**
* Determine if the given exception was caused by a lost connection.
*
* @param  \Exception  $e
* @return bool
*/
protected function causedByLostConnection(Exception $e)
{
    $message = $e->getMessage();

    return Str::contains($message, [
        'server has gone away',
        'no connection to the server',
        'Lost connection',
        'is dead or not enabled',
        'Error while sending',
        'decryption failed or bad record mac',
        'SSL connection has been closed unexpectedly',
    ]);
}

Answer 2

I had this problem in a script that did the following operations in this order:

1. Connect to local database
2. Connect to a remote API via cURL
3. Update local database

It seems to be that step 2 was sometimes taking too long to complete, which ended up with a timed out / broken connection to the database.

To fix, I simply changed the order of operations, so I connect to the database after the long-running operation, just before I do the work on the database:

1. Connect to a remote API via cURL
2. Connect to local database
3. Update local database

Answer 3

I've solved it by reducing the items passed to the foreach loop.

$all_brands = Brand::all();
$padding    = 0;
$batch      = 100;

while($all_brands->count() > $padding){
    $brands = Brand::orderBy('id', 'asc')->skip($padding)->take($batch)->get();
    foreach($brands as $brand){
        ....
        ....
        DB::table('mailing_list')->insert(array(
                        'email'     => $email,
                        'source'    => $source,
                        'source_url'=> $brand->external_url,
                        'facebook_url'  => $facebook_url,
                        'instagram_id'  => $instagram_username,
                        'instagram_url' => $instagram_url,
                        'instagram_followers'   => $instagram_followers
                    ));
    }
    $padding = $padding + $batch;
}