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

[11 May 2006 11: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 12: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 15: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 4:06] Brian Aker 
Whomever works on this should contact me, since I have an idea on how to fix this.
[16 May 2006 9: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 14:37] Victoria Reznichenko 
The same happens with UPDATE query when column(s) from WHERE clause is a primary/unique
key .
[16 Dec 2006 11: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 13: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 17:52] Sergei Golubchik 
BUG#24797 is a duplicate of this bug
[10 Feb 2007 14:09] gehaijiang gehaijiang 
这个问题如何解决
[11 Feb 2007 7: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 18: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 18: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 18: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 18:41] Sergey Gluhov 
Fixed in 5.0.38, 5.1.17-beta
[6 Mar 2007 20: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.