The BLACKHOLE storage engine accepts data but does not store it and always returns an empty result.


The BLACKHOLE Storage Engine was enabled by default until MariaDB 10.0. From MariaDB 10.1, the plugin needs to be specifically enabled, for example:

INSTALL SONAME 'ha_blackhole';


If the binary log is enabled, all SQL statements will be logged as usual, and replicated to any slave servers. However, since rows are not stored, it is important to use statement-based rather than the row or mixed format, as UPDATE and DELETE statements are neither logged nor replicated. See Binary Log Formats.

A BLACKHOLE table consists of a single .frm table format file, but no associated data or index files.

This storage engine can be useful, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master. The master can run a BLACKHOLE storage engine, with the data replicated to the slave for processing.

The INSERT statement is still valid, even though it does not store any data, and will still activate BEFORE triggers. UPDATE and DELETE triggers are not activated, however, nor does the FOR EACH ROW clause apply.

Foreign keys are not supported. If you convert an InnoDB table to BLACKHOLE, the foreign keys will disappear; when you convert the table back to InnoDB, you will have to recreate them.

Trying to convert a table which contains virtual columns to BLACKHOLE produces an error.


Because a BLACKHOLE table does not store data, it will not maintain the AUTO_INCREMENT value. If you are replicating to a table that can handle AUTO_INCREMENTs, and are not explicitly setting the primary key auto-increment value in the INSERT query, or using the SET INSERT_ID statement, inserts will fail on the slave due to duplicate keys.


Comments loading...