Hello @shlomi-noach ,

First thanks for opensourcing gh-ost :) It looks very good :)
The only question remaining in my mind was about this particular subject (cut-over phase), thanks for documenting it. But I believe I now have two more:

• What ensures that no other connections writes to tbl table in-between those events:

  • Connection C10: CREATE TABLE tbl_old (id int primary key) COMMENT='magic-be-here'
  • Connection C10: LOCK TABLES tbl WRITE, tbl_old WRITE

  wouldn't it be safer to:

  • Connection C10: LOCK TABLES tbl WRITE;
  • Connection C10: CREATE TABLE tbl_old (id int primary key) COMMENT='magic-be-here'
  • Connection C10: LOCK TABLES tbl_old WRITE;

  I believe this way the replication client knowns once the CREATE_TABLE occurs in the replication stream, no writes to tbl will occur ever. And it's safe to proceed further down (with the rename and so on).

• Other question: You assume:

  A blocked RENAME is always prioritized over a blocked INSERT/UPDATE/DELETE, no matter who came first

  From my understanding of the mysql documentation this is only guaranteed by the lock acquirement algorithm that mysql uses [https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html]:

  LOCK TABLES acquires locks as follows:

  • Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.
  • If a table is to be locked with a read and a write lock, put the write lock request before the read lock request.
  • Lock one table at a time until the session gets all locks.

  I believe it to be defined in https://github.com/percona/percona-server/blob/5.7/sql/mdl.h#L147
  Although I believe the code tends to confirm your hypothesis, the documentation clearly says that user-code shouldn't assume on any ordering. Am I missing something?

@baloo, thank you for your review!

What ensures that no other connections writes to tbl table in-between those events:

There is nothing to ensure that, and there is no need to ensure that; the table creation is merely a step in the direction of beginning the cut-over. There is no problem that tbl still takes writes, these are still being read in the binary log and propagated to the ghost table.

Moreover, if we reverse the order as you suggest, implication is we waste more time during the locked phase.

wouldn't it be safer to:

• Connection C10: LOCK TABLES tbl WRITE;
• Connection C10: CREATE TABLE tbl_old (id int primary key) COMMENT='magic-be-here'
• Connection C10: LOCK TABLES tbl_old WRITE;

This doesn't work like that in MySQL, see: http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html:

... A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. ...

You cannot just grab more locks as you go along, you have to grab them all at once.

I believe it to be defined in https://github.com/percona/percona-server/blob/5.7/sql/mdl.h#L147
Although I believe the code tends to confirm your hypothesis, the documentation clearly says that user-code shouldn't assume on any ordering. Am I missing something?

The relevant code in MySQL is https://github.com/mysql/mysql-server/blob/a533e2c786164af9bd276660b972d93649434297/sql/mdl.cc#L2312. You are correct this relies on internal behavior. This behavior exists for over 15 years or so, and, having discussed (informally, Safe Harbour etc.) with engineers - has no intention of going away.

I'm glad you point it out, I'll make it explicit in the fine print.

This entire scheme came to be because of a limitation in MySQL, where you cannot rename a table you have under lock in the same session. I'm in good mind to influence next MySQL version to support the above, rendering this entire algorithm unnecessary.

Thanks for clarifications :)

It can’t happen because gh-ost tails the binary log, either on master or on the replica, and there may be still events to handle
In the binary logs during the cut-over.

Noteworthy that as of MySQL 8.0.13 there's support for RENAME TABLES under LOCK TABLES, specifically designed for gh-ost, see: https://mysqlserverteam.com/the-mysql-8-0-13-maintenance-release-is-generally-available/

gh-ost is yet to utilize this new functionality.

Hi，has gh-ost already utilize this new functionality?

It is unfortunate that the implementation actually does not meet gh-ost's requirement, and using the new implementation sadly complicates the logic rather than simplifies it. I therefore made no progress meeting the new implementation.

Can I ask why the new implementation can't simplify the logic? I don't understand by myself.o(╥﹏╥)o

@shlomi-noach

If C20 dies just after C10 DROPs the table but before the unlock, same as above.

This entry should be incorrect. In this case, the table name should be cut-over normally, because after the session is broken, the lock is released on C10 and C20 will get the lock and cut-over table directly. Finally gh-ost will fail with error Expected magic comment on _xxx_del, did not find it in retry stage and exit, but the cut-over has actually been completed.

I don't think I have the bandwidth to solve this again. Unless someone else has a definitive solution, consider using -cut-over=two-step