[clug] MySQL prepared statements issue

[David]

Tim Murphy wrote:
> Hi guys and gals,
>  
> I've come across a strange MySQL occurrence when doing prepared statements.
> I'm using MySQL 5.0.45.
>  
> I run the usual syntax:
> 
> PREPARE prepared_stmt FROM "SELECT first_name FROM table WHERE id = ?";
>  
> SET @a1 = "123";
>  
> EXECUTE prepared_stmt USING @a1;
>  
> DEALLOCATE PREPARE prepared_stmt;
> 
> half of the time I get the right result, a quarter of the time I get the
> error
> 
> Unknown prepared statement handler (prepared_stmt) given to DEALLOCATE
> PREPARE
> 
> and the other quarter of the time I get the error
> 
> Unknown prepared statement handler (prepared_stmt) given to EXECUTE

I haven't played much with MySQL prepared statements but it smells like
a fairly standard synchronisation issue.

The MySQL manual is fairly specific that prepared statements are local
to the session, which should fix most of them.

Do you have multiple clients?

Are you using the mysql command line tool or are you going through
another layer?  If the layer uses the one database connection for all
it's clients then they will all share the same scope.

If I'm right you should be able to work around the problem by not
deallocating the prepared statement.  MySQL will automatically
deallocate it when the session is closed.


David