28

Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is used wherever possible, and implies (without ever quite stating it) that the INPLACE algorithm is cheaper than the COPY one is.

So what reason would I ever have to specify ALGORITHM=COPY on an ALTER TABLE statement?

Mark Amery
  • 1,075
  • 2
  • 14
  • 24
  • If you use COPY what happens to the indexes on the table? Do you end up with defragmented indexes due to a fresh table being created and populated from scratch? – Dave Poole Jan 24 '17 at 16:48
  • If COPY does populate from scratch then although it is a slow option the resulting table might perform better due to defragged indexes. – Dave Poole Jan 24 '17 at 16:49
  • @DavePoole Nice theory, but I suspect it's off the mark since OPTIMIZE TABLE (which I believe has defragging indexes as a large part of its purpose) uses ALGORITHM=INPLACE as of MySQL 5.7.4. So I think it's the case that, yes, COPY does defrag indexes, but so does INPLACE (somehow), nullifying it as a potential advantage of COPY. – Mark Amery Jan 24 '17 at 16:53
  • 3
    "InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY"...Limitations of Online DDL – atokpas Jan 25 '17 at 09:55

3 Answers3

24

Yes, there are cases when you may specify COPY, but it would be for other reasons than performance.

It is important to understand that MySQL introduced new feature - Online DLL processing in version 5.6. It did not remove offline processing. So there is a need to differentiate between these 2 modes:

  1. Some operations still work in Offline mode only. See Table 15.10, “Summary of Online Status for DDL Operations” for a list of the DDL operations that can or cannot be performed in-place.

  2. Operations in Online and Offline modes have slightly different behavior, so you can choose "old" one for compatibility reasons.

Some examples (please suggest more):

  1. InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns error.

  2. ADD PRIMARY KEY clause in COPY mode silently converts NULL to default values for that data type (0 for INT, empty string for varchar), whereas IN_PLACE does not do that.

With the ALGORITHM=COPY clause, the operation succeeds despite the presence of NULL values in the primary key columns; the data is silently changed, which could cause problems.

Another reason to prefer COPY:

Operations for which you specify ALGORITHM=COPY or old_alter_table=1, to force the table-copying behavior if needed for precise backward-compatibility in specialized scenarios.

Although MySQL manual doesn't talk about actual scenarios, you can imagine some. E.g. developer relied on table being locked during ALTER INDEX operation so table is read-only or fully locked and there is a process that reads static table during index rebuild.

Stoleg
  • 2,388
  • 14
  • 19
  • 4
    I think people also tend to confuse ALGORITHM=INPLACE with "this is Online DDL and won't lock the database", when in fact, they actually want to use LOCK=NONE. – SineSwiper May 08 '18 at 22:51
3

@Stoleg probably has the best answer, but here is another one. It's an educated guess that the developers left =COPY in as an escape hatch in case there was a serious bug in =INLINE. This would let users continue to use ALTER even if the new feature is broken.

I have seen things like this (in flags, sql_mode, my.cnf settings, etc) over the years. The intent of the new release is clearly to bring out the new, better, feature.

Optimization flags fall into this category, but there is even more reason to hang onto the previous actions -- the Optimizer will always "do it wrong" sometimes; there are simply too many possibilities.

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • 1
    Why would you call it "escape hatch" rather than "backward compatibility"? Though there may not be much difference ;) – Stoleg Jan 30 '17 at 09:00
  • 1
    I would say "backward compatibility" if I needed the same code to run on both versions. But then I would worry about whether the new syntax was recognized by the old version. – Rick James Jan 30 '17 at 15:51
-1

In versions of MySQL that support InnoDB tablespace encryption, when you alter a table to add encryption, the alteration is done using the copy algorithm out of necessity.

nthdesign
  • 11
  • 2