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
Tags: bfsm_2006_12_21

[11 May 2006 9:54] Damir Franusic
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.
[11 May 2006 10:00] Damir Franusic
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)
[11 May 2006 13:32] Miguel Solorzano
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)
[16 May 2006 2:06] Brian Aker
Whomever works on this should contact me, since I have an idea on how to fix this.
[16 May 2006 7:02] Damir Franusic
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)
[13 Dec 2006 13:37] Victoria Reznichenko
The same happens with UPDATE query when column(s) from WHERE clause is a primary/unique key .
[16 Dec 2006 10:02] Victoria Reznichenko
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
[21 Dec 2006 12:17] Giuseppe Maxia
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
[8 Feb 2007 16:52] Sergei Golubchik
BUG#24797 is a duplicate of this bug
[10 Feb 2007 13:09] gehaijiang gehaijiang
这个问题如何解决
[11 Feb 2007 6:03] gehaijiang gehaijiang
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
[14 Feb 2007 17:38] Bugs System
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.
[14 Feb 2007 17:38] Bugs System
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.
[14 Feb 2007 17:41] Bugs System
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.
[28 Feb 2007 17:41] Sergey Glukhov
Fixed in 5.0.38, 5.1.17-beta
[6 Mar 2007 19:23] Paul Dubois
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.