Laravel: General error: 1615 Prepared statement needs to be re-prepared

Question

I'm using last version of laravel (5.1) in a homestead virtual machine (vagrant).

I connect my project to a local mariaDB server, in which I have some table and 2 db-view.

Since I made some select only on the db-view tables, I receive back randomly this error:

General error: 1615 Prepared statement needs to be re-prepared

From today, I always get this error when made select only on the db views. If I open my phpMyAdmin and make the same select it return the correct result.

I tried to open php artisan tinker and select one record of the db-view but it return the same error:

// Select one user from user table
>>> $user = new App\User
=> <App\User #000000006dc32a890000000129f667d2> {}
>>> $user = App\User::find(1);
=> <App\User #000000006dc32a9e0000000129f667d2> {
       id: 1,
       name: "Luca",
       email: "luca@email.it",
       customerId: 1,
       created_at: "2015-08-06 04:17:57",
       updated_at: "2015-08-11 12:39:01"
   }
>>> 
// Select one source from Source db-view
>>> $source = new App\Source
=> <App\Source #000000006dc32a820000000129f667d2> {}
>>> $source = App\Source::find(1);
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: select * from `sources` where `sources`.`id` = 1 limit 1)'

How can I fix that? I read about a problem with mysqldump (but not in my case) and to increase value of table_definition_cache but it is not sure that it will work and I can't modify them.

Is this a kind of laravel bug?

How can I figure that out?

---

Edit:

As asked, I add my model source code. Source.php:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Source extends Model
{
    protected $table = 'sources';


    /*
    |--------------------------------------------------------------------------
    | FOREIGN KEYS
    |--------------------------------------------------------------------------
    */

    /**
     * 
     * @return [type] [description]
     */
    public function customersList(){
        return $this->hasMany("App\CustomerSource", "sourceId", "id");
    }


    /**
     * 
     * @return [type] [description]
     */
    public function issues(){
        return $this->hasMany("App\Issue", "sourceId", "id");
    }
}

---

Edit 2:

If I execute the same query in the project with mysqli it works:

$db = new mysqli(getenv('DB_HOST'), getenv('DB_USERNAME'), getenv('DB_PASSWORD'), getenv('DB_DATABASE'));
if($db->connect_errno > 0){
    dd('Unable to connect to database [' . $db->connect_error . ']');
}
$sql = "SELECT * FROM `sources` WHERE `id` = 4";
if(!$result = $db->query($sql)){
    dd('There was an error running the query [' . $db->error . ']');
}

dd($result->fetch_assoc());

---

EDIT 3: Afeter 2 month, I'm still there. Same error and no solution found. I decide to try a little solution in aritsan tinker but no good news. I report what I've tried:

First try to fetch a table model:

>>> $user = \App\User::find(1);
=> App\User {#697
     id: 1,
     name: "Luca",
     email: "luca.d@company.it",
     customerId: 1,
     created_at: "2015-08-06 04:17:57",
     updated_at: "2015-10-27 11:28:14",
   }

Now try to fetch a view table model:

>>> $ir = \App\ContentRepository::find(15);
Illuminate\Database\QueryException with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dbname.content_repositories' doesn't exist (SQL: select * from `content_repositories` where `content_repositories`.`id` = 1 limit 1)'

When contentRepository doesn't have correct table name setup inside the model ContentRepository.php:

>>> $pdo = DB::connection()->getPdo();
=> PDO {#690
     inTransaction: false,
     errorInfo: [
       "00000",
       1146,
       "Table 'dbname.content_repositories' doesn't exist",
     ],
     attributes: [
       "CASE" => NATURAL,
       "ERRMODE" => EXCEPTION,
       "AUTOCOMMIT" => 1,
       "PERSISTENT" => false,
       "DRIVER_NAME" => "mysql",
       "SERVER_INFO" => "Uptime: 2513397  Threads: 12  Questions: 85115742  Slow queries: 6893568  Opens: 1596  Flush tables: 1  Open tables: 936  Queries per second avg: 33.864",
       "ORACLE_NULLS" => NATURAL,
       "CLIENT_VERSION" => "mysqlnd 5.0.11-dev - 20120503 - $Id: id_here $",
       "SERVER_VERSION" => "5.5.5-10.0.17-MariaDB-1~wheezy-wsrep-log",
       "STATEMENT_CLASS" => [
         "PDOStatement",
       ],
       "EMULATE_PREPARES" => 0,
       "CONNECTION_STATUS" => "localiphere via TCP/IP",
       "DEFAULT_FETCH_MODE" => BOTH,
     ],
   }
>>> 

CHANGE TABLE VALUE INSIDE model ContentRepository.php:

>>> $ir = \App\ContentRepository::find(15);
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: select * from `contentRepository` where `contentRepository`.`id` = 15 limit 1)'

When it is correct, pay attention to "errorInfo" that is missing:

>>> $pdo = DB::connection()->getPdo();
=> PDO {#690
     inTransaction: false,
     attributes: [
       "CASE" => NATURAL,
       "ERRMODE" => EXCEPTION,
       "AUTOCOMMIT" => 1,
       "PERSISTENT" => false,
       "DRIVER_NAME" => "mysql",
       "SERVER_INFO" => "Uptime: 2589441  Threads: 13  Questions: 89348013  Slow queries: 7258017  Opens: 1604  Flush tables: 1  Open tables: 943  Queries per second avg: 34.504",
       "ORACLE_NULLS" => NATURAL,
       "CLIENT_VERSION" => "mysqlnd 5.0.11-dev - 20120503 - $Id: id_here $",
       "SERVER_VERSION" => "5.5.5-10.0.17-MariaDB-1~wheezy-wsrep-log",
       "STATEMENT_CLASS" => [
         "PDOStatement",
       ],
       "EMULATE_PREPARES" => 0,
       "CONNECTION_STATUS" => "localIPhere via TCP/IP",
       "DEFAULT_FETCH_MODE" => BOTH,
     ],
   }

Show db's tables:

>>> $tables = DB::select('SHOW TABLES');
=> [
     {#702
       +"Tables_in_dbname": "table_name_there",
     },
     {#683
       +"Tables_in_dbname": "table_name_there",
     },
     {#699
       +"Tables_in_dbname": "table_name_there",
     },
     {#701
       +"Tables_in_dbname": "table_name_there-20150917-1159",
     },
     {#704
       +"Tables_in_dbname": "contentRepository", */ VIEW TABLE IS THERE!!!! /*
     },
     {#707
       +"Tables_in_dbname": "table_name_there",
     },
     {#684
       +"Tables_in_dbname": "table_name_there",
     },
   ]

Try with normal select:

>>> $results = DB::select('select * from dbname.contentRepository limit 1');
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: select * from dbname.contentRepository limit 1)'

Try unprepared query:

>>> DB::unprepared('select * from dbname.contentRepository limit 1')
=> false

Try second time unprepared query:

>>> DB::unprepared('select * from dbname.contentRepository limit 1')
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. (SQL: select * from dbname.contentRepository limit 1)'

Try PDOStatement::fetchAll():

>>> DB::fetchAll('select * from dbname.contentRepository limit 1'); 
PHP warning:  call_user_func_array() expects parameter 1 to be a valid callback, class 'Illuminate\Database\MySqlConnection' does not have a method 'fetchAll' in /Users/luca/company/Laravel/dbname/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php on line 296

Try second PDOStatement::fetchAll():

>>> $pdo::fetchAll('select * from dbname.contentRepository limit 1');
  [Symfony\Component\Debug\Exception\FatalErrorException]  
  Call to undefined method PDO::fetchAll()           

Try statement... :

>>> $pdos = DB::statement('select * from dbname.contentRepository limit 1')
Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: select * from dbname.contentRepository limit 1)'

Thank you

Answer 1

It seems to work adding

'options'   => [
                \PDO::ATTR_EMULATE_PREPARES => true
            ]

Inside projectName/config/database.php file in DB configuration. It will be like this:

'mysql' => [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST', 'localhost'),
    'database'  => env('DB_DATABASE', 'forge'),
    'username'  => env('DB_USERNAME', 'forge'),
    'password'  => env('DB_PASSWORD', ''),
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
    'strict'    => false,
    'options'   => [
        \PDO::ATTR_EMULATE_PREPARES => true
    ]
],

Laravel 5.1. Hope it will help!

Edit: I'm currently on Laravel 8 and this solution is still working.

Answer 2

As per the comments in the accepted answer, running

SET GLOBAL table_definition_cache = 1024

in the MariaDB solved the problem.

https://mariadb.com/kb/en/library/server-system-variables/#table_definition_cache

Answer 3

Seems like it's a MySQL Bug that has been documented.

Edit:

Is your model using 'id' as the primary key? I like to set the primary key explicitly in the model even if it is.

protected $primaryKey = 'id'; // If different than id, definitely need to set the column here

You can also try commenting out the hasMany() functions and trying again. Sometimes Laravel can do weird things on eagerLoad, especially if there are A LOT of records that it is trying to map to.