Further working towards an atomic table swap

[shlomi-noach]

Basically what we're looking for is a wait_for_condition function, that blocks until some condition actively turns true. The GET_LOCK is a reverse implementation, because it blocks on an active operation (another process holding the lock), as opposed of unblocking by an active operation.

Some other things I was looking it is MASTER_POS_WAIT which is difficult to work with, especially with multiple concurrent migrations. Also at LOAD_FILE() which placing a read-lock on the file itself (requires mount options on the file system).

There are no further blocking functions in mysql, to the best of my knowledge.

However, it is easy for us to write one. I'm able to produce a UDF function that blocks on a condition, such that said condition is not connection/session dependent and such that it would take an active activation of that condition (someone to say "yes, release!").

We do not need to fork MySQL for that; just to have that function as shared library, compatible with the versions we have deployed

• That's an issue: we would typically need to have this function compiled per mysql version we're using. Not a big deal, and we have puppet to watch over our back anyhow. Some minor versions would not require recompiling but we would go on the safe side.

An example to a UDF which I wrote in the past is https://github.com/outbrain/audit_login ; it worked & works pretty well, with the hassle of recompiling per version.

This really solves our problems. It's not pure 100% MySQL, but we can open source this plugin, and support a choice of swap algorithms:

• bumpy (the one FB uses today, table ceases to exist for a short duration)
• Locky, pure MySQL (the GET_LOCK() implementation). Death of connections will cause premature rename
• UDF. No death risk. We need to develop this. We need to make sure we don't crash our servers (I haven't crashed anything with the audit_login)

[jonahberquist]

I think this sounds like the best option so far, especially combined with supporting multiple swap algorithms. 👍

[shlomi-noach]

Thank you for spending the time reviewing this. I'm closing this issue as #65 came up, which I believe to solve the cut-over even in face of connection failure, and via pure MySQL commands.