General error 1615 prepared statement needs to be re prepared

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 AppUser
=> <AppUser #000000006dc32a890000000129f667d2> {}
>>> $user = AppUser::find(1);
=> <AppUser #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 AppSource
=> <AppSource #000000006dc32a820000000129f667d2> {}
>>> $source = AppSource::find(1);
IlluminateDatabaseQueryException 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 IlluminateDatabaseEloquentModel;

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


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

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


    /**
     * 
     * @return [type] [description]
     */
    public function issues(){
        return $this->hasMany("AppIssue", "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 = AppUser::find(1);
=> AppUser {#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 = AppContentRepository::find(15);
IlluminateDatabaseQueryException 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 = AppContentRepository::find(15);
IlluminateDatabaseQueryException 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');
IlluminateDatabaseQueryException 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')
IlluminateDatabaseQueryException 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 'IlluminateDatabaseMySqlConnection' 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');
  [SymfonyComponentDebugExceptionFatalErrorException]  
  Call to undefined method PDO::fetchAll()           

Try statement… :

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

Thank you

I have been running into this issue every time I try and sync a medium size JSON object to my database so we can perform some reporting on it. From looking into what can cause it I have come across these links on the matter.

http://blog.corrlabs.com/2013/04/mysql-prepared-statement-needs-to-be-re.html
http://bugs.mysql.com/bug.php?id=42041

Both seem to point me in the direction of table_definition_cache. However this is saying the issue is due to a mysqldump happening on the server at the same time. I can assure you that this is not the case. Further I have slimmed down the query to only insert one object at a time.

public function fire($job, $data) 
{
    foreach (unserialize($data['message']) as $org) 
    {
        // Ignore ID 33421 this will time out.
        // It contains all users in the system.
        if($org->id != 33421) {
            $organization = new Organization();
            $organization->orgsync_id = $org->id;
            $organization->short_name = $org->short_name;
            $organization->long_name = $org->long_name;
            $organization->category = $org->category->name;
            $organization->save();

            $org_groups = $this->getGroupsInOrganization($org->id);
            if (!is_int($org_groups))
            {
                foreach ($org_groups as $group)
                {
                    foreach($group->account_ids as $account_id)
                    {
                        $student = Student::where('orgsync_id', '=', $account_id)->first();
                        if (is_object($student))
                        {
                            $student->organizations()->attach($organization->id, array('is_officer' => ($group->name == 'Officers')));
                        }
                    }
                }
            }
        }
    }

    $job->delete();
}

This is the code that is running when the error is thrown. Which normally comes in the form of.

SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: insert into `organization_student` (`is_officer`, `organization_id`, `student_id`) values (0, 284, 26))

Which is then followed by this error repeated 3 times.

SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: insert into `organizations` (`orgsync_id`, `short_name`, `long_name`, `category`, `updated_at`, `created_at`) values (24291, SA, Society of American, Professional, 2014-09-15 16:26:01, 2014-09-15 16:26:01))

If anyone can point me in the right direction I would be very grateful. I am more curious about what is actually triggering the error then finding the cause of this specific issue. It also seems to be somewhat common in laravel application when using the ORM.

1615 MySQL error making life difficult?

At Bobcares, we are devoted to coming up with solutions for every query, big and small, as a part of our Server Management Service.

Here is a recent example of how our Support Techs helped a customer overcome the dreaded MySQL error due to a MariaDB bug.

How to resolve the 1615 MySQL error

Has a MySQL error been causing trouble recently? This is a general error that seems to be a common problem.

Error code 1615 prepared statement needs to be re-prepared mysql

This error pops up due to a MariaDB bug.

According to our Support Engineers, we can resolve this error by adding the following section of mysqlconf file:

table_definition_cache = 1024

After that, we will restart MariaDB with this command:

systemctl restart mariadb.service

This will resolve the prepared statement needs to be re-prepared error. Furthermore, upping the table cache helps resolve the 1615 error. For instance:

root@smtp:/etc/mysql/mariadb.conf.d# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 40
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04


Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.


Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.


MariaDB [(none)]> show variables like 'table_definition_cache';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 1024  |
+------------------------+-------+
1 row in set (0.001 sec)


MariaDB [(none)]> q
Bye
root@smtp:/etc/mysql/mariadb.conf.d#

Interestingly, error 1615 seems to be an ongoing issue. Increasing the table cache size is one of resolving the error. However, sometimes this solution may also backfire.

In such cases, our Support Engineers recommend using the SQL of view in a subquery rather than relying on the view in the prepared statement option.

[Need a helping hand? We are available 24/7.]

Conclusion

To sum up, we learned how to resolve the common prepared statement needs to be re-prepared MySQL error under the guidance of the skilled Support Engineers at Bobcares.

sysPass Version
3.0.0-rc8 (300.18122601)

Describe the bug
SQLSTATA[HY000]: General error: 1615 Prepared statment Needs to be re-prepared

To Reproduce
Steps to reproduce the behavior:

1. Go to ‘url’
2. Logon »
3. Error on mainpage
4. Error on «Items and Customizations» tab.

All other looks fine…..

Event log
2019-01-03 06:49:22 [EXCEPTION] [N/A] (P) SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared
#0 /var/www/html/syspass/lib/SP/Storage/Database/Database.php(234): PDOStatement->execute()
#1 /var/www/html/syspass/lib/SP/Storage/Database/Database.php(174): SPStorageDatabaseDatabase->prepareQueryData(Object(SPStorageDatabaseQueryData))
#2 /var/www/html/syspass/lib/SP/Storage/Database/Database.php(135): SPStorageDatabaseDatabase->doQuery(Object(SPStorageDatabaseQueryData))
#3 /var/www/html/syspass/lib/SP/Repositories/Account/AccountRepository.php(572): SPStorageDatabaseDatabase->doSelect(Object(SPStorageDatabaseQueryData), true)
#4 /var/www/html/syspass/lib/SP/Services/Account/AccountService.php(697): SPRepositoriesAccountAccountRepository->search(Object(SPDataModelItemSearchData))
#5 /var/www/html/syspass/app/modules/web/Controllers/ItemManagerController.php(221): SPServicesAccountAccountService->search(Object(SPDataModelItemSearchData))
#6 /var/www/html/syspass/app/modules/web/Controllers/ItemManagerController.php(111): SPModulesWebControllersItemManagerController->getAccountsList()
#7 /var/www/html/syspass/app/modules/web/Controllers/ItemManagerController.php(72): SPModulesWebControllersItemManagerController->getGridTabs()
#8 [internal function]: SPModulesWebControllersItemManagerController->indexAction()
#9 /var/www/html/syspass/lib/SP/Bootstrap.php(234): call_user_func_array(Array, Array)
#10 [internal function]: SPBootstrap->SP{closure}(Object(KleinRequest), Object(KleinResponse), Object(KleinServiceProvider), Object(KleinApp), Object(KleinKlein), Object(KleinDataCollectionRouteCollection), Array)
#11 /var/www/html/syspass/vendor/klein/klein/src/Klein/Klein.php(886): call_user_func(Object(Closure), Object(KleinRequest), Object(KleinResponse), Object(KleinServiceProvider), Object(KleinApp), Object(KleinKlein), Object(KleinDataCollectionRouteCollection), Array)
#12 /var/www/html/syspass/vendor/klein/klein/src/Klein/Klein.php(588): KleinKlein->handleRouteCallback(Object(KleinRoute), Object(KleinDataCollectionRouteCollection), Array)
#13 /var/www/html/syspass/lib/SP/Bootstrap.php(456): KleinKlein->dispatch(Object(KleinRequest))
#14 /var/www/html/syspass/lib/Base.php(75): SPBootstrap::run(Object(DIContainer))
#15 /var/www/html/syspass/index.php(28): require(‘/var/www/html/s…’)
#16 {main}

Platform (please complete the following information):
SERVER_VERSION : 10.1.20-MariaDB
CLIENT_VERSION : 10.1.20-MariaDB
SERVER_INFO : Uptime: 65923 Threads: 1 Questions: 249930 Slow queries: 0 Opens: 479 Flush tables: 1 Open tables: 415 Queries per second avg: 3.791
Apache/2.4.6 (CentOS) OpenSSL/xxxxxxx PHP/7.2.12

Erweiterungen: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, standard, apache2handler, bz2, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, gmp, iconv, json, ldap, exif, mysqli, PDO, pdo_mysql, pdo_sqlite, Phar, shmop, SimpleXML, sockets, sqlite3, tokenizer, xml, wddx, xmlreader, xmlwriter, xsl, zip, Zend OPcache
Nicht verfügbare Erweiterungen:
Verwendeter Speicher: 4096 KB