Temp tables can break replication

[GoogleCodeExporter]

Is it possible for common_schema to avoid temp tables, and use uniquely named 
real tables instead? When starting a slave from a backup, I got the following 
error:

2013-05-18 20:34:34 3454 [ERROR] Slave SQL: Error 'Table 
'common_schema._qs_variables' doesn't exist' on query. Default database: 
'common_schema'. Query: 'UPDATE _qs_variables SET value_snapshot = NULL WHERE 
declaration_depth =  NAME_CONST('depth',4)', Error_code: 1146
2013-05-18 20:34:34 3454 [Warning] Slave: Table 'common_schema._qs_variables' 
doesn't exist Error_code: 1146

Original issue reported on code.google.com by baron.schwartz on 18 May 2013 at 8:36

[GoogleCodeExporter]

As the title suggests this is a MySQL limitation that stopped+shutdown slaves 
(or slaves under snapshot) with open temporary tables turn to be inconsistent.

So question is how far shall I go to go around this MySQL bug/problem.

I'm using MyISAM temporary tables for the main reasons that they are fast to 
create/drop and are non persistent (if anything crashes, I don't need to 
maintain a "tables I should be dropping later on" list).

If I'm going to use persistent tables, I won't dare use MyISAM. As result, I 
will be using InnoDB, which will turn all DDL to be very slow; so this is a 
tight corner.

If you know of a good solution please let me know.

Original comment by shlomi.n...@gmail.com on 19 May 2013 at 6:40

[GoogleCodeExporter]

I could use CONNECTION_ID() for naming the tables, and do a cleanup for all 
tables with IDs not in PROCESSLIST. Someone will have to pay for this cleanup; 
namely, other script executions. Cleanup is required in case of crash or 
premature termination of script. Typically this would not happen, but one can 
never know. 
Still, in some operations such as split() I create supporting tables while the 
script executes. I would hate to create these as normal tables; I've already 
seen the impact of (temporary) innodb tables creation on scripting -- it's 
disastrous.

Original comment by shlomi.n...@gmail.com on 19 May 2013 at 10:06

[GoogleCodeExporter]

I'm not sure there is a perfect workaround for this. I wonder if things
like split() that will potentially amortize the cost of creating the temp
table over time can stand the cost of creating it with innodb.

Original comment by baron.schwartz on 20 May 2013 at 11:53

[GoogleCodeExporter]

I can see how this is a serious issue for long running scripts (never releasing 
the temporary tables, thereby not allowing for safe slave shutdown). Need to 
think this over.

Original comment by shlomi.n...@gmail.com on 21 May 2013 at 4:35

[GoogleCodeExporter]

Will attempt using the following tables:
  _sql_tokens
  _qs_variables
  _script_report_data
  _split_column_names_table

as global (standard) InnoDB tables. 

Will add a "session_id" (== CONNECTION_ID()) column to each

Where possible (certainly in _sql_tokens), will currently rename tables and add 
view under same original name which only filters appropriate rows.

Upon script termination / split termination, will cleanup tables
Upon script startap / split startup, will cleanup tables for same 
CONNECTION_ID()

May need to support additoinal "queryscript_cleanup()" routine to handle 
leftover from aborted scripts

Original comment by shlomi.n...@gmail.com on 26 May 2013 at 10:04

[GoogleCodeExporter]

Original comment by shlomi.n...@gmail.com on 2 Jun 2013 at 2:44

• Changed state: Started

[GoogleCodeExporter]

Fixed in revision 468 (not yet released)

Original comment by shlomi.n...@gmail.com on 3 Jun 2013 at 5:47

[GoogleCodeExporter]

Original comment by shlomi.n...@gmail.com on 3 Jun 2013 at 5:47

• Changed state: Verified

[GoogleCodeExporter]

Attached temporary build file

Original comment by shlomi.n...@gmail.com on 3 Jun 2013 at 5:58

Attachments:

• common_schema.sql

[GoogleCodeExporter]

Thanks! Looks like there is still a (very small, reasonable) chance of this 
problem happening but this should be much improved.

Original comment by baron.schwartz on 3 Jun 2013 at 5:12

[GoogleCodeExporter]

Temporary tables are now only created per split() operation, and for the 
duration of table analysis; so 
CREATE-CREATE-CREATE-CREATE-SELECT-DROP-DROP-DROP-DROP. Tables are dropped 
before actually iterating the split() statement, so should be very short period.

Original comment by shlomi.n...@gmail.com on 3 Jun 2013 at 7:31