Level 26
Thats a MySQL issue rather than framework specific, I think it's due to too many large queries.
Edit: a quick google points to upping the table_definition_cache
Aug 6, 2015
23
Level 1
General error: 1615 Prepared statement needs to be re-prepared (selecting mysql view)
Hi guys,
Selecting MySQL tables are good, but MySQL views, error comes. See the following error:
SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: SELECT * FROM v_user_models)
My view: CREATE VIEW v_user_models AS select m.id AS model_id, u.id AS user_id, u.firstname AS firstname, u.lastname AS lastname, from ((users u join models m on((u.id = m.user_id)))
In my localhost, it is working but when I uploaded it on the live site, it doesn't work. Please help.. Thanks a lot!
Level 1
hi guys, I think that change of "table_definition_cache" parameter is not resolve this problem ... I did try to create simply separate PHP page with (standard/usual) code for select data from db-view and - data is retrieved correctly! (on my webhosting). But if I use retrieving the same data from the same db-view through code in Laravel app (on the same webhosting and the same MySQL setting) it is raised that error (1615). Further I don't run a lot of queries, only this one (simply select to db-view which have to retrieve 1 row from 20).
Pls see to my similar question/problem on http://laravel.io/forum/08-07-2015-difference-between-select-on-db-table-and-db-view-on-webhosting-error-1615
1 like
Level 1
I already tried increasing the table_definition_cache but nothing happened. I tried doing the native PHP code and it's now working! Cool! Thank you guys for helping me!
1 like
Level 1
Hi maverickheroes, how to fix it? i have a similar problem.
$data= DB::table('view_depth_nodes')->get();
error:
{"error":{"type":"Illuminate\Database\QueryException","message":"SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared (SQL: select * from view_depth_nodes)","file":"/home/fonzpho1/public_html/vendor/laravel/framework/src/Illuminate/Database/Connection.php","line":555}}
please help me.
1 like
Level 1
Hi guys!
Enable the ATTR_EMULATE_PREPARES of PDO (disabled by default at Laravel) for resolve the problem. Just insert the option config:
'options' => [
\PDO::ATTR_EMULATE_PREPARES => true
]
Example:
'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
]
],
[]'s
23 likes
Level 1
@ErickCarvalho thanks so much it worked
Level 9
@ErickCarvalho I didn't actually try this so I can't say for sure. The docs do say if the driver can't prepare the statement, it'll fallback to emulating them:
"It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query."
Level 1
@ErickCarvalho solution works perfectly for me. Thanks.
Level 6
@ErickCarvalho thank you very much this works like a charm
Level 1
@ErickCarvalho thanks a lot
Level 5
@ErickCarvalho this seems to have worked for me as well!
Level 1
@ErickCarvalho thanks guy, it was really helpful o/
Level 6
Yes, adding the options worked for me as well.
Level 5
@ErickCarvalho thank you so much. It solves all the problem i have regarding this error. Can you helpt to elaborate why this work?
Level 2
Perfect @ErickCarvalho. It works :D
Level 1
@ErickCarvalho Thanks for the answer, and sorry to bring this back again. The option
'options' => [
\PDO::ATTR_EMULATE_PREPARES => true
]
will resolve the issue, However, beside the security issues it generates, it also effect all select queries that you perform on the DB in the way that it force/convert every returned type from the DB to be string instead of the real type (integer/ float)
How does it effect you?
if you have a validation layer above the result of the query, you can't perform something like (
if (is_int($model->id))
) which will return 0 always, because it's string not an integer anymore.
5 likes
Level 1
@ErickCarvalho thank you for your answer!
Level 1
Why switch emulation to ‘false’? The main reason for this is that having the database engine do the prepare instead of PDO is that the query and the actual data are sent separately, which increases security. This means when the parameters are passed to the query, attempts to inject SQL into them are blocked, since MySQL prepared statements are limited to a single query. That means that a true prepared statement would fail when passed a second query in a parameter.
The main argument against using the database engine for the prepare vs PDO is the two trips to the server – one for the prepare, and another for the parameters to get passed – but I think the added security is worth it. Also, at least in the case of MySQL, query caching has not been an issue since version 5.1.
https://michaelseiler.net/2016/07/04/dont-emulate-prepared-statements-pdo-mysql/
Level 1
Thanks a lot man!
Level 1
@erickcarvalho thanks very much, saving lives.
Level 1
it worked for me. thanks
Level 3
Adding \PDO::ATTR_EMULATE_PREPARES => true option to config seems like the most popular answer. But it impacts returned types of database fields as @hanihh mentioned. To avoid additional issues with type casting better to increase Mysql table open cache and table definition cache:
SET GLOBAL table_definition_cache = 4096;
SET GLOBAL table_open_cache = 4096;
1 like
Level 4
If you - like me - just ran into this with
DB::statement("....")
try to change it to
DB::unprepared("....")
Please or to participate in this conversation.