一般错误:1615 Prepared statement需要重新准备

2022-01-15 00:00:00 mariadb mysql laravel

每次我尝试将中等大小的 JSON 对象同步到我的数据库时都会遇到这个问题，以便我们可以对其执行一些报告.通过调查可能导致它的原因，我发现了有关此事的这些链接.

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.htmlhttp://bugs.mysql.com/bug.php?id=42041

两者似乎都将我指向 table_definition_cache 的方向.然而，这是说问题是由于服务器上同时发生了 mysqldump.我可以向你保证，情况并非如此.此外，我还精简了查询，一次只插入一个对象.

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))

然后此错误重复 3 次.

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))

如果有人能指出我正确的方向，我将不胜感激.我对实际触发错误的原因更加好奇，然后找到了这个特定问题的原因.在使用 ORM 时，它在 laravel 应用程序中似乎也很常见.

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.

推荐答案

虽然 mysqldump 是常见的原因，但它并不是唯一的原因.

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

在我的情况下，在任何数据库上运行 artisan:migrate 也会为同一服务器上的不同数据库触发此错误.

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提到将在 mysqldump 中调用的表锁定/刷新，因此值得检查您是否同时发生任何迁移、锁定或刷新.

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
        ]

相关文章