The database query designer sometimes returns an array instead of an object running as a job in a queue

Question

TL DR

I have queues with jobs that sometimes seem unsuccessful because a regular database query (using the standard Laravel query designer) does not reliably return PHP objects defined in fetch mode in my config/database.php . It seemed that sometimes an object or an array was returned, so somehow the sampling mode changes (and even changes back).

Detailed question

I am querying an external database using the Laravel query constructor. The sampling mode is set to return objects to config/database.php :

 'fetch' => PDO::FETCH_OBJ, 

... which basically works in many places of my application.

Sometimes, executing it as a job in a queue, it may happen that the query result is an array instead of an object. Tasks set the correct code, and I cannot reproduce why this happens sometimes.

Does anyone have an idea what could lead to this result?

This happens both using the usual get() method and chunk , for example. Restarting the command line helps to get rid of the error, but in the end it will return!

This is what 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 use:

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

My desktop is to add this to any places where I query an external database like this.

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

Debugging is quite difficult to do in such conditions, because it is performed only in the queue :(

Update: 2017-12-11: more detailed information on the used SQL / code

To summarize the more special things I'm doing here, this may be relevant to my problem:

• I do not request the default connection running on localhost, but an external database (on the internal network).
• I do not use Eloquent, but the usual Laravel query builder
• To accomplish the result, I use a self-written custom function that calls the callback function for each row

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

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

Then you execute your own query and map the old fields to the 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 a helper function, where you see all the if I added, because sometimes I get 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()); } }); } 

Answer 1

Starting with version 5.4, Laravel no longer supports setting the PDO fetch mode via config / database.php . By default, the structure sets the fetch mode to PDO::FETCH_OBJ , although we can override this parameter by listening to the StatementPrepared event:

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

It seems possible that a particular job in the queue subscribes to this event and changes the sampling mode. If we start a queue employee using the queue:work Artisan console command, the listener is delayed for any subsequent tasks, because this command loads the application once for all tasks performed by the worker. This explains why rebooting the worker fixes the problem temporarily.

For this reason, jobs that change the sampling mode should set it after completion or failure. We need to exercise the same caution when we change any state of a global application from a job.

Answer 2

First you need to make it work. Add if / else_if, which checks if the result is an object or an array, and retrieves the data accordingly. If you can abstract this with the BaseDB class, which will be used by all queries, even better.

In the second step, add some entries to the code to find which queue job returns the array and causes the problem. As @cyrossignol mentioned, there might be some kind of event listener that the script runs. Look at this. Also keep in mind that the problem may be related to MySQL. Perhaps some condition is called in db by your request, and some exception code is triggered and returns an array instead of an object.

The main thing is to fix the code at the moment, and little by little to accurately determine the actual problem. You may not find it now, but over time you will have enough information to find the root of the problem.