Database query builder sometimes returns array ins

TL;DR

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

Detailed question

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.

Does anybody have an idea for what could lead to this kind of result?

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!

Here is how my code looks like:

$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
}

I'm using:

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 :(

Update: 2017-12-11: More details about the SQL/Code being used

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

I'm not querying the "default" connection running on localhost but an external database (in internal network)
I'm not using Eloquent, but the regular query builder of Laravel
To step through the result, I'm using a self-written custom function that calls a callback function for each row

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

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());
            }
        });
}

回答1:

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

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.

回答2:

First you have to make it work. Add an if/else_if that checks if the result is object or array and fetches the data accordingly. If you can abstract this with a "BaseDB" class that all the queries will use, even better.

For the second phase put some logging to the code to find which queue job is returning an array and is causing the problem. As @cyrossignol mentioned, there might be some Event Listener that is triggered by a script. Look into it. Also keep in mind that the problem might be with MySQL. Maybe some condition is triggered in the db by your query and some exception code runs and returns an array instead of an object.

The main point is to fix the code for now and little-by-little pinpoint the actual problem. You may not find it now but in time you will have enough information to find the root of the problem.