MySQL Bugs: #19717: DELETE Query Error on BLACKHOLE when using WHERE on column with UNIQUE INDEX

Bug #19717	DELETE Query Error on BLACKHOLE when using WHERE on column with UNIQUE INDEX
Submitted:	11 May 2006 9:54	Modified:	6 Mar 2007 19:23
Reporter:	Damir Franusic	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.21/5.0BK/5.1BK	OS:	Linux (Linux FC4)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	bfsm_2006_12_21

Description:
We are using blackhole storage engine on one table which is replicated to one slave server. Since the data is not needed on master server, blackhole storage engine was ideal because statements are written to binary log and data is lost on master server. Table structure on slave server is identical to one on the master server except for the ENGINE part which is MyISAM on slave and BLACKHOLE on master server. 

To simplify everything, we have created one simple BLACKHOLE table 'test' with 2 fields.
Both our tables(original and this simple one) have one standard PRIMARY KEY AI NOT NULL field.  Only recently we have discovored that simple query like this :
-------------------------------------------------------
DELETE FROM test WHERE c1=10;
is causing the following error:
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option
-----------------------------------------------------
This only happens if column c1 is UNIQUE INDEX/PRIMARY KEY.

How to repeat:
mysql> CREATE TABLE test(c1 int, c2 int) ENGINE=BLACKHOLE;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `c1` int(11) default NULL,
  `c2` int(11) default NULL,
  KEY `c1` (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin2 COLLATE=latin2_croatian_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> DELETE FROM test WHERE c1=10;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE test ADD INDEX(c1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `c1` int(11) default NULL,
  `c2` int(11) default NULL,
  KEY `c1` (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin2 COLLATE=latin2_croatian_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM test WHERE c1=10;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE test DROP INDEX c1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test ADD UNIQUE INDEX(c1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `c1` int(11) default NULL,
  `c2` int(11) default NULL,
  UNIQUE KEY `c1` (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin2 COLLATE=latin2_croatian_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM test WHERE c1=10;
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option

mysql> ALTER TABLE test DROP INDEX c1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `c1` int(11) NOT NULL default '0',
  `c2` int(11) default NULL,
  PRIMARY KEY  (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin2 COLLATE=latin2_croatian_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM test WHERE c1=10;
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option

Suggested fix:
Do not use UNIQUE INDEXE or PRIMARY KEY when creating table on BLACKHOLE storage engine.

The first SHOW CREATE TABLE output under "How to repeat:" is wrong(sorry for this). It should be like this:
mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                        |+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+| test  | CREATE TABLE `test` (
  `c1` int(11) default NULL,
  `c2` int(11) default NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin2 COLLATE=latin2_croatian_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Thank you for the bug report.

mysql> DELETE FROM test WHERE c1=10;
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option
mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.10-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM test WHERE c1=10;
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.22-debug | 
+--------------+
1 row in set (0.01 sec)

Whomever works on this should contact me, since I have an idea on how to fix this.

ANALYZE TABLE and OPTIMIZE TABLE also fail with the same error message and statements are not written to binary log.

mysql> ANALYZE TABLE test;
+-----------+---------+----------+----------------------------------------------------------+
| Table     | Op      | Msg_type | Msg_text                                                 |
+-----------+---------+----------+----------------------------------------------------------+
| test.test | analyze | note     | The storage engine for the table doesn't support analyze |
+-----------+---------+----------+----------------------------------------------------------+

mysql> OPTIMIZE TABLE test;
+-----------+----------+----------+-----------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                  |
+-----------+----------+----------+-----------------------------------------------------------+
| test.test | optimize | note     | The storage engine for the table doesn't support optimize |
+-----------+----------+----------+-----------------------------------------------------------+
1 row in set (0.01 sec)

The same happens with UPDATE query when column(s) from WHERE clause is a primary/unique key .

In some cases this error occurs only first time though any SELECT 'resets' this behaviour:

mysql> create table test1(
    -> id1 int not null,
    -> id2 int not null,
    -> dt datetime,
    -> primary key(id1, id2),
    -> index(dt))engine=blackhole;
Query OK, 0 rows affected (0.02 sec)

mysql> update test1 set dt='2004-01-01 00:01:01' where id1=1 and id2=2;
ERROR 1031 (HY000): Table storage engine for 'test1' doesn't have this option
mysql> update test1 set dt='2004-01-01 00:01:01' where id1=1 and id2=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update test1 set dt='2004-01-01 00:01:01' where id1=1 and id2=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

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

mysql> update test1 set dt='2004-01-01 00:01:01' where id1=1 and id2=2;
ERROR 1031 (HY000): Table storage engine for 'test1' doesn't have this option
mysql> update test1 set dt='2004-01-01 00:01:01' where id1=1 and id2=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Here is a simpler test case.

# --- CUT 
--disable_warnings
drop table if exists t1;
--enable_warnings

create table t1 (
    id int not null,
    c1 char(10) not null,
    unique key (id)
)engine=blackhole;

insert into t1 values (1,'abc'), (2,'bcd'), (3,'cde');

update t1 set id = 100 where id = 1;

delete from t1 where id = 1;
truncate t1;

drop table t1;

# --- CUT

Notice that bug #18309 is related to this one

BUG#24797 is a duplicate of this bug

这个问题如何解决

Please resolve this problem! 

There is a bug on blackhole engine.

See:
UPDATE Query Error on BLACKHOLE when using WHERE on column with UNIQUE OR PRIMARY INDEX

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19891

ChangeSet@1.2414, 2007-02-14 18:17:43+02:00, gkodinov@macbook.gmz +3 -0
  Bug#19717: 
   The blackhole engine is returning
   an OK flag in index_read(), whereas
   it must return HA_ERR_END_OF_FILE
   instead, as there are by definition
   no rows in a table of that engine.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19892

ChangeSet@1.2414, 2007-02-14 18:21:42+02:00, gkodinov@macbook.gmz +3 -0
  Bug#19717: 
   The blackhole engine is returning
   an OK flag in index_read(), whereas
   it must return HA_ERR_END_OF_FILE
   instead, as there are by definition
   no rows in a table of that engine.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19895

ChangeSet@1.2414, 2007-02-14 18:35:59+02:00, gkodinov@macbook.gmz +3 -0
  Bug#19717: The blackhole engine is returning an OK flag in 
  index_read(), whereas it must return HA_ERR_END_OF_FILE
  instead (as there are by definition no rows in a table of 
  that engine.

Fixed in 5.0.38, 5.1.17-beta

Noted in 5.0.38, 5.1.17 changelogs.

For index reads, the BLACKHOLE engine did not return end-of-file
(which it must because BLACKHOLE tables contain no rows), causing
some queries to crash.