MySQL prepared statement does not tolerate table modification.

Upd: the effect described below appears only in MySQL below 5.1.25 - thanks to pharod .

An interesting effect was discovered by chance that led to a bug in the application:

mysql> create table test(a int,b int);
Query OK, 0 rows affected (0.11 sec)

mysql> prepare ps from "select * from test";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> alter table test drop column b;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> execute ps;
ERROR 1054 (42S22): Unknown column 'testdb.test.b' in 'field list'

It seems that the query is not tied to a specific table schema and can be executed after changing the schema. In fact, the prepared statement is laid on the list of columns that was at the time the statement was created.

In real life, the problem was revealed as follows: the class responsible for communicating with the database caches the prepared statements. Cached statements broke when it was necessary to change the base scheme during execution (do not ask why it was required: not everything in life is done the way we want). Be careful!