Description:
The blackhole storage engine is used when you want to ignore data in a table. There may be a wide variety of reasons for this but often on a slave you may not be interested in the content on the upstream master so changing a table to be a blackhole table means that there's no load due to replication as the inserts/updates/deletes are accepted but no data is actually stored. (as intended).
However if you make a slave have a blackhole table when the upstream master uses InnoDB this may break if certain new features such as the use of ALTER TABLE ... ALGORITHM=INPLACE is used.
The blackhole engine does not support this syntax and therefore replication breaks.
Making the blackhole engine support the syntax (it doesn't need to store any data so there's no implementation) means that you can then have such a table under a master where the master's engine is InnoDB and replication won't break.
How to repeat:
see above.
The issue seen on such a slave is as follows:
Last_SQL_Error: Error 'ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.' on query. Default database: 'db'. Query: 'ALTER TABLE mytable LOCK=NONE, ALGORITHM=INPLACE, MODIFY `col1` enum( 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l') NOT NULL DEFAULT 'a''
Seen on: MySQL 5.6.15
Note on this server there was a /etc/motd which said:
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*
* db.mytable and db.mytable2
* are blackholed to reduce disk space usage
*
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
This type of usage for adhoc situations is quite common
Suggested fix:
Support the same syntax that InnoDB makes. (you want us to use InnoDB?) and this enables slaves to replicate correctly in SBR mode even if the downstream table is a blackhole table.
Description: The blackhole storage engine is used when you want to ignore data in a table. There may be a wide variety of reasons for this but often on a slave you may not be interested in the content on the upstream master so changing a table to be a blackhole table means that there's no load due to replication as the inserts/updates/deletes are accepted but no data is actually stored. (as intended). However if you make a slave have a blackhole table when the upstream master uses InnoDB this may break if certain new features such as the use of ALTER TABLE ... ALGORITHM=INPLACE is used. The blackhole engine does not support this syntax and therefore replication breaks. Making the blackhole engine support the syntax (it doesn't need to store any data so there's no implementation) means that you can then have such a table under a master where the master's engine is InnoDB and replication won't break. How to repeat: see above. The issue seen on such a slave is as follows: Last_SQL_Error: Error 'ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.' on query. Default database: 'db'. Query: 'ALTER TABLE mytable LOCK=NONE, ALGORITHM=INPLACE, MODIFY `col1` enum( 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l') NOT NULL DEFAULT 'a'' Seen on: MySQL 5.6.15 Note on this server there was a /etc/motd which said: * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * db.mytable and db.mytable2 * are blackholed to reduce disk space usage * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This type of usage for adhoc situations is quite common Suggested fix: Support the same syntax that InnoDB makes. (you want us to use InnoDB?) and this enables slaves to replicate correctly in SBR mode even if the downstream table is a blackhole table.