MySQL Forums
Forum List  »  Views

MySql “view”, “prepared statement” and “Prepared statement needs to be re-prepared”
Posted by: R K
Date: April 07, 2015 02:36AM

Hi..

I've a problem with MySql, specially with View and Prepared Statement, here is the details :
I created a new schema/database, then in PHPMyAdmin I executed (only) this queries :

create table mytable (
id varchar(50) not null,
name varchar(50) null default '',
primary key (id));

create view myview as
select id,name from mytable;

insert into mytable values ('1','aaa');
insert into mytable values ('2','bbb');
insert into mytable values ('3','ccc');

and then, if I run these queries :

select * from mytable;
select * from myview;
prepare cmd from 'select id,name from mytable where id=?';
set @param1 = '2';
execute cmd using @param1;
deallocate prepare cmd;

the queries give the correct result (3 rows,3 rows,1 row).

but, the problem exists if I run this query:

prepare cmd from 'select id,name from myview where id=?';
set @param1 = '2';
execute cmd using @param1;
deallocate prepare cmd;

ERROR: #1615 - Prepared statement needs to be re-prepared

I've done some research and found that the increment of configurations below "may" solve the problem :

increase table_open_cache_instances value
increase table_open_cache value
increase table_definition_cache value

As far as I know, the queries above are the common and standard MySql queries, so I think there is no problem with the syntax.

I'm on a "shared webhosting" and using MySql version is 5.6.22

But the things that make me confused is, it only contain 1 schema/database, with 1 table with 3 short records and 1 view, and I executed a common and standard MySql select query, does the increment of values above really needed? is there anyone with the same problem had increase the values and really solve the problem? or, perhaps do you have any other solution which you think may or will works to solve this problem?

It does not happen once or twice in a day (which assumed caused by some backup or related), but in all day (24 hours).

Below is some values in the environment status :

show status;
Com_stmt_prepare 0
Com_stmt_reprepare 0
Com_stmt_close 0
Com_stmt_reset 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_dealloc_sql 0

show global status;
Com_stmt_prepare 21538
Com_stmt_reprepare 222
Com_stmt_close 21194
Com_stmt_reset 11836
Com_stmt_fetch 0
Com_stmt_execute 21529
Com_dealloc_sql 236

To MySql DbAdmin experts, please give some opinions about the values above.

ps: If you find the exactly same post in an another forum, it is the same, and I 'will' post the final solution in both forum if I found the solution (so it can usefull to anyone who visit any of the forums).

Thank you.

Options: ReplyQuote


Subject
Views
Written By
Posted
MySql “view”, “prepared statement” and “Prepared statement needs to be re-prepared”
12877
R K
April 07, 2015 02:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.