数据库查询生成器有时会返回数组，而不是将对象作为排队作业运行 [英] Database query builder sometimes returns array instead of object running as a queued job

问题描述

TL; DR

我将有时似乎失败的作业排入了队列，因为常规数据库查询(使用Laravel的标准查询生成器)不能可靠地返回config/database.php中的访存模式所定义的PHP对象.似乎有时会返回一个对象或数组，以某种方式获取模式会改变(甚至改变回来).

I have queued jobs that sometimes seem to fail, because a regular database query (using Laravel's standard query builder) does not reliably return PHP objects as defined by the fetch mode in my config/database.php. It seemed to sometimes return an object or an array so somehow the fetch mode changes (and even changes back).

详细问题

我正在使用Laravel的查询生成器查询外部数据库.将获取模式设置为返回config/database.php中的对象:

I'm querying an external database using Laravel's query builder. The fetch mode is set to return objects in config/database.php:

'fetch' => PDO::FETCH_OBJ,

...基本上可以在我的应用程序的许多地方使用

...which is basically working in many places of my application.

有时，当将其作为排队的作业执行时，查询的结果可能是数组而不是对象，这可能发生.这些作业运行正确的代码，我无法重现有时会发生这种情况的原因.

Sometimes, while executing it as a queued job, it can happen that the result of a query is an array instead of an object. The jobs run the correct code and I cannot reproduce why this sometimes is happening.

有人对导致这种结果的方式有想法吗?

例如，使用常规的get()方法或chunk都会发生这种情况.重新启动队列运行器有助于消除错误，但最终它会回来！

It happens both using the regular get() method, or chunk for example. Restarting the queue runner helps to get rid of the error but it eventually will come back!

这是我的代码的样子:

$assetData = DB::connection('connection_name')->table('system')
  ->whereIn('system_id', $this->system_ids)->orderBy('system_id');

$emlAssetData->chunk(5000, function ($assetDataChunk) {
  foreach ($assetDataChunk AS $assetData) {
      if (!is_object($assetData)) {
          \Log::warning(__CLASS__.': '.json_encode($assetData));
      }
  }

  $assetData->field; // Sometimes fails because result is an array, instead of an object
}

我正在使用:

• PHP 7.0
• MySQL v5.7.16 v5.1.49
• Laravel 5.5

• PHP 7.0
• MySQL v5.7.16 v5.1.49
• Laravel 5.5

为此，我的解决方法是将其添加到我查询外部数据库的任何地方.

My workarround to this is to add this to any places where I query a external database like this.

if (is_array($assetData)) {
    \Log::warning(__CLASS__." Converting array to object: ".json_encode($assetData));
    $assetData = (object)$assetData;
}

在这种情况下调试非常困难，因为它只在队列中运行:(

Debugging is pretty hard to do under this conditions, because it only happens running in the queue :(

更新:2017-12-11:有关所使用的SQL/代码的更多详细信息

要总结我在这里所做的更特殊的事情，可能与我的问题有关:

To summarize the more special things I do here, that might have to do with my issue:

• 我不是查询本地主机上运行的默认"连接，而是外部数据库(在内部网络中)
• 我不是在使用Eloquent，而是Laravel的常规查询生成器
• 要逐步查看结果，我使用了一个自定义的自定义函数，该函数为每一行调用一个回调函数

背景:这会将旧版数据库MySQL v5.1.49的各个部分导入到我们项目的数据库中.为了简化操作，您可以指定某种列映射(从旧字段映射到新字段/表名称)

Background: This imports various parts of a legacy database MySQL v5.1.49 into our project's database. To make that easier, you specify some sort of column mapping (from old to new field/table names) like this

$columnMapping = collect([
    'system.system_id'          => 'system_id',
    'staud_colours.colour_name' => 'system_name',
]);

接下来，您执行自定义查询，并使用帮助程序功能将旧字段映射到新字段:

Next, you execute your custom query and map the old fields to new fields using a helper function:

$items = \DB::connection('slave')->table('system')
    ->join('staud_colours', 'staud_colours.colour_id', '=', 'system.system_fremd_id')
    ->where('system.system_klasse', 'colours')->where('system.system_status', 1);

$this->prepareQueryToBeInsertedToDB($items, $columnMapping, function ($insertData) {
    static::create($insertData);
});

和帮助函数，在其中可以看到我添加的所有if，因为有时我会收到一个数组而不是对象:

And the helper function where you see all the ifs I have added because I sometimes receive an array instead of objects:

protected function prepareEmlQueryToBeInsertedToDB(
    Builder $items,
    Collection $columnMapping,
    Closure $callback,
    $orderBy = 'system.system_id'
) {
    // Step through each element of the mapping
    $items->orderBy($orderBy)->select($columnMapping->keys()->toArray())
        ->chunk(5000, function ($items) use ($columnMapping, $callback, $items) {
            foreach ($items AS $item) {

                $values = $columnMapping->mapWithKeys(function ($item, $key) use ($item) {
                    $key = Str::lower($key);

                    if (Str::contains($key, ' as ')) {
                        $column = array_reverse(explode(' as ', $key))[0];
                    } else {
                        $column = substr(strrchr($key, "."), 1);
                    }

                    if (!$item) {
                        \Log::error("Received damaged item from slave db: ".json_encode($item));
                    }

                    if (is_array($item)) {
                        $item = (object)$item;
                    }

                    if (!property_exists((object)$item, $column)) {
                        \Log::error("{$column} does not exist on item from slave db: ".json_encode($item));
                    }

                    $value = $item->$column;

                    return [$item => $value];
                });

                if (!$values || $values->isEmpty()) {
                    info('No values: '.json_encode($values));
                }

                // Now call the callback method for each item, passing an well prepared array in format:
                // column_name => value
                // so that it can be easily be used with something like static::create()
                $callback($values->toArray());
            }
        });
}

推荐答案

从5.4版开始，Laravel 不再通过 config/database.php 支持PDO提取模式的配置.默认情况下，框架将获取模式设置为PDO::FETCH_OBJ，尽管我们可以通过监听StatementPrepared事件来覆盖此设置:

Since version 5.4, Laravel no longer supports configuration of the PDO fetch mode through config/database.php. By default, the framework sets the fetch mode to PDO::FETCH_OBJ, though we can override this setting by listening for the StatementPrepared event:

Event::listen(StatementPrepared::class, function ($event) {
    $event->statement->setFetchMode(PDO::FETCH_ASSOC);
});

似乎某个排队的作业订阅了此事件并更改了获取模式.如果我们使用queue:work Artisan控制台命令启动队列工作器，则侦听器会继续执行所有后续作业 ，因为此命令会为 all 引导应用程序一次. em>工作者处理的工作.这可以解释为什么重新启动工作程序可以暂时解决此问题.

It seems possible that a certain queued job subscribes to this event and changes the fetch mode. If we start a queue worker using the queue:work Artisan console command, the listener lingers for any subsequent jobs because this command boots the application once for all the jobs that that the worker processes. This would explain why restarting the worker fixes the issue temporarily.

因此，更改获取模式的作业必须在完成或失败后将其重新设置.每当更改作业的任何全局应用程序状态时，我们都需要格外小心.

For this reason, jobs that change the fetch mode must set it back after completion or failure. We need to exercise the same care whenever we change any global application state from a job.

这篇关于数据库查询生成器有时会返回数组，而不是将对象作为排队作业运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！