Is it safe to use ALGORITHM=INPLACE for MySql?

Question

ALTER TABLE in MySql allows ALGORITHM=INPLACE for avoiding table copy during altering. But for default they use copy instead of inplace. Why? Does default value used for backward compatibility or there are some limitations or failure with inplace value?

P.S. MySql is Percona-Server-server-56.x86_64 5.6.29

Antipus

are you aware that percona has a custom innodb? Also settings, regarding HOW to do sync, What with (bin_log,etc) and Whether to lock tables/row. – Concenter

Did you ever find a reason for why copy is the default? My guess would be mostly for safety reasons, but as you suggested, backwards compatibility could be an issue as well. According to dba.stackexchange.com/a/162411/243271 , there is a corner case in which the two algorithms give different results, but it sounds like for the vast majority of cases, the two give the same results, and you will most likely prefer INPLACE over COPY. But yeah, "Why?" is a good question, and no definitive answer has emerged here yet... – Audrieaudris

Answer 1

Yes. You can use it in many cases. See this list of online DDL operations.

If the INPLACE algorithm cannot be used, MySQL will tell you so and then you can revert to using DEFAULT (ie: copy) or use pt-online-schema-change

Residuary