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: "[email protected]",        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: "[email protected]",      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