General error: 1615 Prepared statement needs to be re-prepared

Question

I have been running into this issue every time I try and sync a medium size JSON object to my database so we can perform some reporting on it. From looking into what can cause it I have come across these links on the matter.

http://blog.corrlabs.com/2013/04/mysql-prepared-statement-needs-to-be-re.html http://bugs.mysql.com/bug.php?id=42041

Both seem to point me in the direction of table_definition_cache. However this is saying the issue is due to a mysqldump happening on the server at the same time. I can assure you that this is not the case. Further I have slimmed down the query to only insert one object at a time.

public function fire($job, $data) 
{
    foreach (unserialize($data['message']) as $org) 
    {
        // Ignore ID 33421 this will time out.
        // It contains all users in the system.
        if($org->id != 33421) {
            $organization = new Organization();
            $organization->orgsync_id = $org->id;
            $organization->short_name = $org->short_name;
            $organization->long_name = $org->long_name;
            $organization->category = $org->category->name;
            $organization->save();

            $org_groups = $this->getGroupsInOrganization($org->id);
            if (!is_int($org_groups))
            {
                foreach ($org_groups as $group)
                {
                    foreach($group->account_ids as $account_id)
                    {
                        $student = Student::where('orgsync_id', '=', $account_id)->first();
                        if (is_object($student))
                        {
                            $student->organizations()->attach($organization->id, array('is_officer' => ($group->name == 'Officers')));
                        }
                    }
                }
            }
        }
    }

    $job->delete();
}

This is the code that is running when the error is thrown. Which normally comes in the form of.

SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: insert into `organization_student` (`is_officer`, `organization_id`, `student_id`) values (0, 284, 26))

Which is then followed by this error repeated 3 times.

SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: insert into `organizations` (`orgsync_id`, `short_name`, `long_name`, `category`, `updated_at`, `created_at`) values (24291, SA, Society of American, Professional, 2014-09-15 16:26:01, 2014-09-15 16:26:01))

If anyone can point me in the right direction I would be very grateful. I am more curious about what is actually triggering the error then finding the cause of this specific issue. It also seems to be somewhat common in laravel application when using the ORM.

Answer 1

While mysqldump is the commonly reported cause for this it is not the only one.

In my case running artisan:migrate on any database will also trigger this error for different databases on the same server.

http://bugs.mysql.com/bug.php?id=42041 Mentions table locks/flush which would be called in a mysqldump so worth checking if you have any migrations, locks or flushes happening simultaneously.

Failing that try switching the prepares to emulated.

'options'   => [
            \PDO::ATTR_EMULATE_PREPARES => true
        ]

Answer 2

This error occurs when mysqldump is in progress. It doesn't matter which DB dump is in progress. Wait for the dump to finish and this error will vanish.

The issue is with the table definition being dumped which cause this error.

Yeah I tried changing these mysql settings, but it still occurs sometime (mostly when running heavy mysql backups/dumps at night)..

table_open_cache 128=>16384

table_definition_cache 1024=>16384