When calling DB::select why do I get a "The connection was reset" message?

Question

In my Laravel 5.5 application, calls to DB::select which run a select query on a Postgresql database fail without showing any error in the Apache or Laravel error logs and trigger a "The connection was reset" message. This code sample runs as expected because the function get_users_with_roles exists.

public function missing_function(Request $request) {
        try{
           $all = DB::select('SELECT * from get_users_with_roles()', []);
        }catch(Illuminate\Database\QueryException $qe){
            return json_encode($qe->getMessage());
        }
        return json_encode($all);
}

However, if I replace that SQL string with a function that doesn't exist:

public function missing_function(Request $request) {
        try{
           $all = DB::select('SELECT * from test()', []);
        }catch(Illuminate\Database\QueryException $qe){
            return json_encode($qe->getMessage());
        }
        return json_encode($all);
}

The connection is reset and I can't see any errors in the logs. If I run this erroneous query in a native Postgresql environment:

SELECT * from test();

I get a clear error message:

    ERROR:  function test() does not exist
LINE 1: select * from test()
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

It is particularly strange because this problem is not consistent. The try block sometimes catches the QueryException and displays the Postgresql error message as excepted.

I have tried adding

php_flag xcache.cacher Off 
php_flag xcache.size 0 
php_flag xcache.stat Off

to the .htaccess file but to no avail.

I need the ability to use the DB::select method because I rely heavily on Postgresql user-defined SQL and plpgsql functions in the application. I have a function which constructs the relevant SQL and passes it the DB::select method programmatically, so I need to be able to catch exceptions thrown when there is an error in the SQL, such as when the function is missing.

UPDATE

This problem seems to be with the way DB::select handles any SQL error. I've just tried this out with a function which exists but which throws an SQL error. Again, instead of allowing me to catch this in PHP with a try/catch block, it just resets the connection and doesn't log an error in either the Laravel log or the Apache log.

This question doesn't shed any light. The accepted answer there refers to the expected behaviour. In my environment, the QueryException isn't thrown or caught.

Brighton

What does $all contain when the exception is not thrown? – Biopsy

Have you tried catching \Throwable (instead of QueryException) to see if maybe another exception is being thrown, and then later swallowed by another handler in your application? – Biopsy

$all contains whatever the select statement returns. In this case, it returns a table as an array of objects, but I have other examples where I'm just returning a scalar value. However, I have the same problem of not being able to catch the sql error if it doesn't return. – Brighton

I tried catching \Throwable but there was no change. – Brighton

So you're saying, this code $all = DB::select('SELECT * from test()', []); produces an $all that contains an array of objects, even though test purportedly does not exist? – Biopsy

No. $all = DB::select('SELECT * from get_users_with_roles()', []); succeeds as expected. When I change the SQL to one that triggers an error in Postgresql, the Laravel try/catch block doesn't catch the exception. The function test() doesn't exist so it triggers an error. – Brighton

Let us continue this discussion in chat. – Biopsy

Answer 1

The tricky part of this has been the browser's stubborn refusal to reveal any form of error message. When that happens, I like to go to the command line and try it, thus eliminating the web server as a variable.

From chat, we learned that the command line showed the error as expected, but did not gracefully do so: the error was output, and the script was halted. That's a hard crash, one not attributable to the web server.

With the introduction of \Throwable, the scenarios where PHP dies hard are becoming fewer and farther between. So, in an effort to catch PHP's dying breath, we implemented a register_shutdown_function that pulled error_get_last in an effort to figure out what, if anything, was said just before blowing up.

This revealed, briefly, the error message in the browser (this time using a different browser). However, this was not repeatable. The insight at this point was caching: composer dump-autoload fixed the problem!

I suspect what happened is this:

• Eloquent threw an exception
• PHP was bubbling that up through Laravel's exception handling classes
• At some point, PHP attempted to load a class that wasn't in the autoloader
• PHP crashed hard (this is one of those cases where PHP 7.0 bails)

By running composer dump-autoload, all the "missing" classes were brought into the autoloader's purview and, when tried again, the correct code sequence happened.

Biopsy

Please check my latest question at #54130937 The problem is back and is even more intractable. As I type, I am upgrading to PHP 7.3 and Laravel 5.7 in the vain hope of throwing exceptions when expected! – Brighton

Answer 2

i think its an Sql query error

 `SELECT * from test()` 

Since () bracket indicates the function so try to use like

 `SELECT * from test` in your query 

Best way in laravel

Create a model with php artisan make:model Test

Then use in controller like

     `use App\Test;'

and then to fetch records Test::all(); it will bring all records from database like your requirement SELECT * from Test

Graner

Jay, I know it’s an SQL error. I deliberately triggered it for test purposes. As I explained in my question, I rely on Postgres functions so I need to use DB::select with raw SQL. Laravel’s Eloquent model works with tables, not functions. Please read the answer I awarded the bonus to. I’m forced to downvote you because you misunderstood the question. – Brighton