16

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.

michael.schuett
  • 4,248
  • 4
  • 28
  • 39
  • I've got the same [error](http://stackoverflow.com/questions/31957441/laravel-general-error-1615-prepared-statement-needs-to-be-re-prepared/31957572) tying to select data from my mariaDB. I noticed that it thow the exeption only when working with sql view. The only different thing is that the error is showing only 2 times for me. Have you discovered somethings new? Thank you very much! – Tenaciousd93 Aug 13 '15 at 06:24

3 Answers3

8

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
        ]
Mark Walker
  • 1,199
  • 11
  • 20
4

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

pencilvania
  • 276
  • 1
  • 4
  • 18
  • 2
    That is what I had found when looking around but we only run mysqldump at 2AM and this error would occur anytime throughout the day when the process would run. – michael.schuett Nov 11 '14 at 14:48
  • I encountered this when restoring a large database dump as well. Restarting mysqld resolved the issue. – garrettmills Feb 06 '23 at 15:12
1

I had a similar problem. In my case the problem seemed to be caused by using a view that itself used other views, the net effect might have been that it took several mS to process. It was particularly annoying because sometimes the error occurred and sometimes it did not. I programmed my way around it by creating temporary tables within the stored procedure rather than relying on the views. The server running the database reported using MariaDb ver. 10.2.35

David
  • 51
  • 3