Viewed   133 times

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

 Answers

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!

Sunday, August 7, 2022
 
5

After save, $data->id should be the last id inserted.

$data->save();
$data->id;

Can be used like this.

return Response::json(array('success' => true, 'last_insert_id' => $data->id), 200);

For updated laravel version try this

return response()->json(array('success' => true, 'last_insert_id' => $data->id), 200);
Wednesday, December 21, 2022
4

The problem is that setConnection() works on an instance of a class, but the create() method is a static method on the class itself. In your repository, $this->product is an instance of the Product class. Using setConnection() on this instance before doing queries will work fine, but you'll need to do a little more manual work if you want to use separate connections on the static methods (such as create()).

All the create() method does is instantiate a new instance with the given attributes and then call save(). So, instead of calling create() on the Product model, you'll just need to do this manually:

class ProductRepository {
    public function create(array $attributes, $connection = null) {
        $product = $this->product->newInstance($attributes);
        $product->setConnection($connection ?: $this->product->getConnectionName());
        $product->save();
        return $product;
    }
}

You could also override the static create() method on the Product model to accept a connection, as well.

class Product extends Model {
    public static function create(array $attributes, $connection = null) {
        $model = new static($attributes);
        $model->setConnection($connection ?: $this->connection);
        $model->save();
        return $model;
    }
}

class ProductRepository {
    public function create(array $attributes, $connection = null) {
        $connection = $connection ?: $this->product->getConnectionName()
        return $this->product->create($attributes, $connection);
    }
}
Thursday, September 29, 2022
 
5

Through some checking, even though mysqli is installed on my host, apparently the mysqlnd driver is not present. Therefore, get_result() can not be used(php manual defines get_result as mysqlnd dependent), and instead extra coding would need to be done to handle my result the way I would like.

Therefore, I decided to try and learn how PDO works, and within minutes, voila!!!

Replaced the above code with this:

function checkUsernameEmailAvailability($username, $email) {

echo 'pdo about to be created';

$dsn = 'mysql:dbname='.C_BASE.';host='.C_HOST;
$user = C_USER;
$password = C_PASS;

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$params = array(':username' => $username, ':email' => $email);

try{
$sth = $dbh->prepare('SELECT username,email FROM tb_users WHERE username = :username OR email = :email ');
} catch(PDOException $e) {
    echo 'Prepare failed: ' . $e->getMessage();
}

try{
$sth->execute($params);
} catch(PDOException $e) {
    echo 'Execute failed: ' . $e->getMessage();
}
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
}

As much error checking as I could think of, and no problems at all first crack at it!

Final Code

function checkUsernameEmailAvailability($username, $email) {
    $dsn = 'mysql:dbname='.C_BASE.';host='.C_HOST;
    $user = C_USER;
    $password = C_PASS;
    new PDO($dsn, $user, $password);

    $params = array(':username' => $username, ':email' => $email);

    $sth = $dbh->prepare('SELECT username,email FROM tb_users WHERE username = :username OR email = :email ');
    $sth->execute($params);
    $result = $sth->fetch(PDO::FETCH_ASSOC);

    print_r($result);
}
Tuesday, August 16, 2022
3

From the Java Tutorials on operators:

The && and || operators perform Conditional-AND and Conditional-OR operations on two boolean expressions. These operators exhibit "short-circuiting" behavior, which means that the second operand is evaluated only if needed.

Friday, November 11, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :