Laravel:一般错误:1615 准备好的语句需要重新准备 [英] Laravel: General error: 1615 Prepared statement needs to be re-prepared
问题描述
我在 homestead 虚拟机 (vagrant) 中使用最新版本的 laravel (5.1).
我将我的项目连接到本地 mariaDB 服务器，其中有一些表和 2 个 db-view.
因为我只在 db-view 表上做了一些选择，所以我随机收到这个错误:
<块引用>一般错误:1615 Prepared statement需要重新准备
从今天开始，仅在 db 视图上进行选择时，我总是会收到此错误.如果我打开我的 phpMyAdmin 并进行相同的选择，它会返回正确的结果.
我尝试打开 php artisan tinker 并选择 db-view 的一条记录，但它返回相同的错误:
//从用户表中选择一个用户>>>$user = 新应用用户=><应用用户#000000006dc32a890000000129f667d2>{}>>>$user = AppUser::find(1);=><应用用户#000000006dc32a9e0000000129f667d2>{编号:1，name: "卢卡",电子邮件:luca@email.it"，客户 ID:1，created_at: "2015-08-06 04:17:57",更新时间:2015-08-11 12:39:01"}>>>//从 Source db-view 中选择一个源>>>$source = 新 AppSource=><AppSource #000000006dc32a820000000129f667d2>{}>>>$source = AppSource::find(1);IlluminateDatabaseQueryException 带有消息SQLSTATE[HY000]:一般错误:1615 准备好的语句需要重新准备(SQL:select * from `sources` where `sources`.`id` = 1 limit 1)'我该如何解决?我读到了 mysqldump 的一个问题(但不是我的情况)并增加了 table_definition_cache 的值，但不确定它是否会起作用，我无法修改它们.
这是一种 Laravel 错误吗?
我怎样才能弄清楚?
<小时>按照要求，我添加了我的模型源代码.源代码:
hasMany("AppCustomerSource", "sourceId", "id");}/**** @return [类型] [说明]*/公共功能问题(){return $this->hasMany("AppIssue", "sourceId", "id");}}<小时>
编辑 2:
如果我在项目中使用 mysqli 执行相同的查询，它会起作用:
$db = new mysqli(getenv('DB_HOST'), getenv('DB_USERNAME'), getenv('DB_PASSWORD'), getenv('DB_DATABASE'));if($db->connect_errno>0){dd('无法连接到数据库 [' . $db->connect_error . ']');}$sql = "SELECT * FROM `sources` WHERE `id` = 4";if(!$result = $db->query($sql)){dd('运行查询时出错 [' . $db->error . ']');}dd($result->fetch_assoc());<小时>
编辑 3:2个月后，我还在那里.同样的错误，没有找到解决方案.我决定在 aritsan tinker 中尝试一些解决方案，但没有好消息.我报告了我的尝试:
首先尝试获取一个表模型:
<预><代码>>>>$user = AppUser::find(1);=>应用用户 {#697编号:1，name: "卢卡",电子邮件:luca.d@company.it"，客户 ID:1，created_at: "2015-08-06 04:17:57",更新时间:2015-10-27 11:28:14"，}现在尝试获取一个视图表模型:
<预><代码>>>>$ir = AppContentRepository::find(15);IlluminateDatabaseQueryException 消息为SQLSTATE[42S02]:未找到基表或视图:1146 表 'dbname.content_repositories' 不存在(SQL:从 `content_repositories` 中选择 * 其中 `content_repositories`.`id` = 1限制 1)'当 contentRepository 在模型 ContentRepository.php 中没有正确的表名设置时:
<预><代码>>>>$pdo = DB::connection()->getPdo();=>PDO {#690交易中:假，错误信息:["00000",1146,"表 'dbname.content_repositories' 不存在",],属性: [案例" =>天然，错误模式" =>例外，自动提交" =>1、持久"=>错误的，DRIVER_NAME" =>"mysql","SERVER_INFO" =>正常运行时间:2513397 线程:12 问题:85115742 慢查询:6893568 打开:1596 刷新表:1 打开表:936 查询每秒平均:33.864"，"ORACLE_NULLS" =>天然，"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" =>["PDO 声明",],"EMULATE_PREPARES" =>0,"CONNECTION_STATUS" =>"通过 TCP/IP 的本地化","DEFAULT_FETCH_MODE" =>两个都，],}>>>在模型 ContentRepository.php 中更改表值:
<预><代码>>>>$ir = AppContentRepository::find(15);IlluminateDatabaseQueryException 带有消息SQLSTATE[HY000]:一般错误:1615 准备好的语句需要重新准备(SQL:从 `contentRepository` 中选择 *，其中 `contentRepository`.`id` = 15 limit 1)'正确时，注意缺少的errorInfo":
<预><代码>>>>$pdo = DB::connection()->getPdo();=>PDO {#690交易中:假，属性: [案例" =>天然，错误模式" =>例外，自动提交" =>1、持久"=>错误的，DRIVER_NAME" =>"mysql","SERVER_INFO" =>正常运行时间:2589441 线程:13 问题:89348013 慢查询:7258017 打开:1604 刷新表:1 打开表:943 查询每秒平均:34.504"，"ORACLE_NULLS" =>天然，"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" =>["PDO 声明",],"EMULATE_PREPARES" =>0,"CONNECTION_STATUS" =>"通过 TCP/IP 的本地IPhere","DEFAULT_FETCH_MODE" =>两个都，],}显示数据库的表:
<预><代码>>>>$tables = DB::select('显示表格');=>[{#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",},]尝试正常选择:
<预><代码>>>>$results = DB::select('select * from dbname.contentRepository limit 1');IlluminateDatabaseQueryException 带有消息SQLSTATE[HY000]:一般错误:1615 准备好的语句需要重新准备(SQL:select * from dbname.contentRepository limit 1)"尝试未准备好的查询:
<预><代码>>>>DB::unprepared('select * from dbname.contentRepository limit 1')=>错误的尝试第二次未准备好的查询:
<预><代码>>>>DB::unprepared('select * from dbname.contentRepository limit 1')IlluminateDatabaseQueryException 带有消息SQLSTATE[HY000]:一般错误:2014 无法执行查询，而其他未缓冲的查询处于活动状态.考虑使用 PDOStatement::fetchAll().或者，如果您的代码只针对 mysql 运行，您可以通过设置 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 属性来启用查询缓冲.(SQL: select * from dbname.contentRepository limit 1)'试试 PDOStatement::fetchAll():
<预><代码>>>>DB::fetchAll('select * from dbname.contentRepository limit 1');PHP 警告:call_user_func_array() 期望参数 1 是有效的回调，类 'IlluminateDatabaseMySqlConnection' 在/Users/luca/company/Laravel/dbname/vendor/laravel/framework/src 中没有方法 'fetchAll'/Illuminate/Database/DatabaseManager.php 第 296 行尝试第二个 PDOStatement::fetchAll():
<预><代码>>>>$pdo::fetchAll('select * from dbname.contentRepository limit 1');[SymfonyComponentDebugExceptionFatalErrorException]调用未定义的方法 PDO::fetchAll()尝试语句...:
<预><代码>>>>$pdos = DB::statement('select * from dbname.contentRepository limit 1')IlluminateDatabaseQueryException 带有消息SQLSTATE[HY000]:一般错误:1615 准备好的语句需要重新准备(SQL:select * from dbname.contentRepository limit 1)"谢谢
似乎可以添加
'options' =>[PDO::ATTR_EMULATE_PREPARES =>真的]在数据库配置中的projectName/config/database.php 文件中.会是这样:
'mysql' =>['司机' =>'mysql','主机' =>环境('DB_HOST'，'本地主机')，'数据库' =>env('DB_DATABASE', '伪造'),'用户名' =>env('DB_USERNAME', '伪造'),'密码' =>env('DB_PASSWORD', ''),'字符集' =>'utf8','整理' =>'utf8_unicode_ci','前缀' =>'','严格' =>错误的，'选项' =>[PDO::ATTR_EMULATE_PREPARES =>真的]],Laravel 5.1.希望能帮到你！
我目前使用的是 Laravel 8，这个解决方案仍然有效.
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
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.
这篇关于Laravel:一般错误:1615 准备好的语句需要重新准备的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
