WL#1011: On the slave, save prepared statements and temporary tables info in a MyISAM table
Affects: Server-7.1 — Status: Un-Assigned — Priority: Medium
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).
Copyright (c) 2000, 2016, Oracle Corporation and/or its affiliates. All rights reserved.