MySQL BLACKHOLE Engine (again)

My previous post about this was run on a MySQL installation on UNIX. I decided to have another go using the MySQL on my home PC, which is installed on Windows XP. First I had a look for the have_blackhole_engine variable but I could not find it:
  

mysql> SHOW VARIABLES LIKE 'have_blackhole_engine';
Empty set (0.00 sec)

mysql>

But I decided to carry on and created a table using the BLACKHOLE engine:

mysql> use test

Database changed

mysql> create table andrew (col1 varchar(10));
Query OK, 0 rows affected (0.41 sec)

mysql> alter table andrew engine = blackhole;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Then I inserted a row into the table and looked for it but it had gone so this time it had worked as expected:

mysql> insert into andrew values ('Fred');
Query OK, 1 row affected (0.00 sec)

mysql> select * from andrew;
Empty set (0.00 sec)

mysql>

MySQL BLACKHOLE Engine

Various engines are available in MySQL to store your table data. I have already looked at MyISAM and InnoDB in a different post. This time I am going to look at the BLACKHOLE engine. You can look at the different engines available using the show engines command. I have had to display its output in a very small font to fit it in but you can try it on your own system if you wish:

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10224

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> show engines;

+------------+---------+----------------------------------------------------------------+

| Engine     | Support | Comment                                                        |  +------------+---------+----------------------------------------------------------------+

| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |      

| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |

| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |

| BerkeleyDB | NO      | Supports transactions and page-level locking                   |

| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |

| EXAMPLE    | NO      | Example storage engine                                         |

| ARCHIVE    | NO      | Archive storage engine                                         |

| CSV        | NO      | CSV storage engine                                             |

| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |

| FEDERATED  | NO      | Federated MySQL storage engine                                 |

| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |

| ISAM       | NO      | Obsolete storage engine                                        |

+------------+---------+----------------------------------------------------------------+

12 rows in set (0.00 sec)

mysql>
  

Now let's try to use the BLACKHOLE engine. According to the output from the show engines command, if you set up a table using this engine, any data you add to it will be discarded:
  

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.02 sec)

 

mysql> alter table andrews_table engine = BLACKHOLE;

Query OK, 0 rows affected, 1 warning (0.17 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into andrews_table

    -> values ('Andrew');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from andrews_table;

+--------+

| col1   |

+--------+

| Andrew |

+--------+

1 row in set (0.00 sec)

mysql>
  

A row was added to the table but it did not disappear so the BLACKHOLE engine did not work as expected. This seems to be due to the have_blackhole_engine variable being set to NO:

 

mysql> SHOW VARIABLES LIKE 'have_blackhole_engine';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| have_blackhole_engine | NO    |

+-----------------------+-------+

1 row in set (0.00 sec)

 

mysql>