Pt-online-schema-change’s triggers fail with “DELETE command denied” on insert

pt-online-schema-change takes advantage of the consistency nature of relational databases at table level. Let's see what happens when we write to the original table, one of three cases:

1. The chunk has already been copied. No problem, the trigger will overwrite the values of the accessory table within the transaction

2. The chunk has not yet been copied. The trigger will probably fail, but the original modification will be done thanks to the IGNORE option on the INSERT/UPDATE/DELETE of the trigger. No problem, when the pt-osc process reaches to it, it will copy the newer values.

3. Your question. The chunk is being copied. There are 2 subcases, depending on which process starts first.

   a) If the modification of the original table starts first, either the whole table (in the case of MyISAM) or the set of rows (in the case of InnoDB), will be locked for other modifications, so pt-osc will have to wait for the write to finish. You should avoid very time-consuming write operations while this tool is working, or relying on it to pause if the load is too high, but in the unfortunate case that you are doing in concurrency another large update, the session variable innodb_lock_wait_timout (configured by the parameter lock-wait-timeout, by default 1 second) for pt-osc should kick in and revert/abort the copy of that chunk. In that case, the tool will retry that chunk again --retries times (default, 3), or the whole process will abort, in theory gracefully. In most cases, once the lock disappears (because the concurrent write finishes), you will be essentially on case 2.

   b) If pt-osc starts first, it will essentially lock again the whole table (in MyISAM) or those specific rows (in InnoDB) for writing (a READ LOCK), preventing concurrent writes. This will only be hold for, ideally, up to 0.5 seconds, as the chunk size has been calculated so that the chunk copy should not take more than that. Once it has finished, you are essentially on case 1.

So, yes, there is not 100% lock free tool, but no such a tool can exist if you want to guarantee consistency. Even the rename table process takes microseconds (after all, a/several file(s) must be renamed on disk), and for those microseconds, the table will be locked. The idea is to have a minimum amount of locking. For MyISAM, every time you write a row (except for concurrent inserts) the whole table is locked, and for InnoDB, at least that particular row (sometimes more). You cannot change that unless you sacrifice the atomicy and durability of changes. However, you can take into acount some tricks to minimize its problems:

• Use InnoDB instead of MyISAM for extra write concurrency
• Avoid slow/large writing transactions on that table during the copy process
• Lower the value of chunk-time parameter. In an extreme case, you could set chunk-size to one, copying and locking one row at a time, but that will be extremely slow. The chunk-time will be an heuristic worse-case scenario of the maximum locking time when you happen to write the exact rows that you are copying at that time.
• MySQL 5.6 allows for concurrent writes while certain DDLs are running. It is less flexible, but it should work faster as it has access to the underlying logic. In some cases, this may work better, but it will suffer from similar problems when doing too many concurrent writes (as those will be buffered).

Reading the code of the tool is not that hard, I invite you to read it for more information.

The problem was that every now and then an unrelated query put a lot of stress on the server and was causing the threads running to nearly max out. It was doing this regularly before the schema change was first run.