WL#1011: On the slave, save prepared statements and temporary tables info in a MyISAM table

Affects: Server-7.1   —   Status: Un-Assigned

Presently a prepared statement like
prepare("insert into t values(?)"); execute(3);
is written to the master's binlog like this:
insert into t values(3);
(a normal query).
This way may make the slave slower than the master (because the master uses
prepared statements and the slave does not).
To fix this we would like to write true prepared statements to the binlog.

We could have a SQL syntax for this (using normal Query_log_events) :
PREPARE AS some_identifier "insert into t values(?)";
...some other queries from other threads...
EXECUTE PREPARED STATEMENT some_identifier;
The advantage of having a SQL syntax for this is that this would work
straightforward when one does mysqlbinlog|mysql.
An alternative is having a new type of event Prepare_query_log_event (and
Prepare_load_log_event ?), but still mysqlbinlog should print this event with a
SQL syntax, because this is the only thing 'mysql' understands.

Finally RESET CONNECTION (which is printing at the end of every thread, that's
another worklog item) would destroy the prepared statement. We also need a SQL
syntax for expliciti destruction by the user, like 
DROP PREPARED STATEMENT some_identifier

So now assume we have done all this.
Then a slave replicates a PREPARE AS, and before it reaches the RESET CONNECTION
or DROP PREPARED STATEMENT, it is shutdown. The in-memory prepared statement is
lost, so at restart the would-be EXECUTE PREPARED STATEMENT will fail ("unknown
prepared statement"). So we need a way to save the prepared statement before
shutdown, and restore it at restart.
After discussion the proposed solution is:
everytime the slave runs a PREPARE AS, it writes to a MyISAM table in the
"mysql" database (called slave_prepared_st for example) : the prepared query,
the thread id, and server id, and some_identifier (4 columns). When it executes
a RESET CONNECTION or DROP PREPARED STATEMENT, it deletes the row which matches
some_identifier.

Note that this is the ideal description: what if the slave
shuts down brutally without having time to write to the MyISAM table?
It is not a big problem as:
- As long as prepared statements are unique, we will be able to notice
  if a prepared statement in the binary log is missing from the table
  and issue an error 'slave needs to be resynced'.
- When we find an event that a master is restarted, we can delete all
  old prepared statements for that master.
The above should at least guarantee that the slave data will always be correct.

We do have one issue when setting up a new slave when there is active
prepared statements on the master.  One solution for this is to force
the master to write the definition for a prepared statements to the
binary log if the binary log has changed since last time the prepared
statement will be used.
This means that one of the procedures to set up a new slave would be
to do one of the following:
- Do flush logs on the master and assume that a slave can start from
  such a position.
- Have a command on the master that reports all active PREPARE
  statements since the start of the current log. (HEY, don't forget temporary
tables).

The same problem stands for temporary tables (BUG#352), so the same solution can
work too. In mysql.slave_tmp_tables, we store the database name, table name,
thread id, server id.

Note that we would have the proper index on the 2 tables for fast search.

Job to be done: new SQL syntax, writing to 2 MyISAM system tables, some master
code to dump prep-statements&temp-tables to the binlog in case of FLUSH LOGS,
and dealing as-good-as-we-can with slave brutal shutdowns (less important).