| Bug #24797 | BlackHole engine doesn't support update, delete and drop table | ||
|---|---|---|---|
| Submitted: | 4 Dec 2006 14:29 | Modified: | 8 Feb 2007 16:52 |
| Reporter: | Mikhail Petrov | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.22, 4.1.23-nightly-20061205-log | OS: | |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | blackhole, Contribution, syntax support | ||
[4 Dec 2006 16:01]
MySQL Verification Team
Thank you for the bug report. Please read: http://dev.mysql.com/doc/refman/4.1/en/blackhole-storage-engine.html 14.9. The BLACKHOLE Storage Engine The BLACKHOLE storage engine was added in MySQL 4.1.11. This engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result: .....
[4 Dec 2006 16:41]
Mikhail Petrov
I've already read that part on man, and I saw such phrase: For example, suppose that your application requires slave-side filtering rules, but transferring all binary log data to the slave first results in too much traffic. In such a case, it is possible to set up on the master host a “dummy” slave process whose default storage engine is BLACKHOLE. But we _can't_ use BLACKHOLE for such purpose without support of UPDATEs and DELETEs. In most cases binary logs on master server consists of not only INSERTs, so I insist on it's a bug nevertheless...
[4 Dec 2006 17:04]
MySQL Verification Team
Thank you for the feedback. Sorry but is clear: "that accepts data but throws it away and does not store it" so you can't delete or update..the very sample shows even not returns a select: mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test; Empty set (0.00 sec) so that storage engine wasn't designed for that.
[4 Dec 2006 17:39]
Mikhail Petrov
Sorry, but 5.0 have support for such commands, so, I think this engine designed for such things. I've consulted with couple of MySQL developers, and they also think so.
[4 Dec 2006 19:39]
MySQL Verification Team
Thank you for the feedback. Are you able to perform the below commnds how I did with current source server? mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> update test set i = 5 where i =2; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> delete from test where i =1; Query OK, 0 rows affected (0.00 sec) mysql> show create table test; +-------+-------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `i` int(11) default NULL, `c` char(10) default NULL ) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 4.1.23 | +-----------+ 1 row in set (0.00 sec) mysql> Thanks in advance.
[5 Dec 2006 11:01]
Mikhail Petrov
Hi, Miguel!
I've got a very strange situation.
This is what I get on
4.1.23-nightly-20061205-log
mysql> create database test;
Query OK, 1 row affected (0.03 sec)
mysql> use test
Database changed
mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update test set i = 5 where i =2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> delete from test where i =1;
Query OK, 0 rows affected (0.05 sec)
mysql> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`i` int(11) default NULL,
`c` char(10) default NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=cp1251 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
mysql> select version();
+-----------------------------+
| version() |
+-----------------------------+
| 4.1.23-nightly-20061205-log |
+-----------------------------+
1 row in set (0.01 sec)
But:
mysql> CREATE TABLE `User` (
-> `oid` int(11) NOT NULL default '0',
-> `balance` double(15,5) NOT NULL default '0.00000',
-> `balance_update_time` datetime NOT NULL default '0000-00-00 00:00:00',
-> `last_clicktime` datetime NOT NULL default '0000-00-00 00:00:00',
-> `rbalance` double(15,5) NOT NULL default '0.00000',
-> `user_load` enum('Always','Normal') NOT NULL default 'Normal',
-> `user_action` set('Distinct') NOT NULL default '',
-> `status` enum('Active','Blocked','Deleted') NOT NULL default 'Active',
-> `system_status` enum('Active','Blocked','Deleted') NOT NULL default 'Active',
-> `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> `created` timestamp NOT NULL default '0000-00-00 00:00:00',
-> PRIMARY KEY (`oid`),
-> KEY `rbalance` (`rbalance`),
-> KEY `created` (`created`),
-> KEY `status` (`status`,`system_status`)
-> ) ENGINE=BLACKHOLE DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.10 sec)
mysql> update User set last_clicktime='20061205133712' where oid=32310732;
ERROR 1031 (HY000): Table storage engine for 'User' doesn't have this option
mysql> alter table User drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update User set last_clicktime='20061205133712' where oid=32310732;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql>
And with 4.1.22 with my patch:
mysql> use test
Database changed
mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.23 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.20 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update test set i = 5 where i =2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> delete from test where i =1;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`i` int(11) default NULL,
`c` char(10) default NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=cp1251 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 4.1.22-log |
+------------+
1 row in set (0.10 sec)
mysql> CREATE TABLE `User` (
-> `oid` int(11) NOT NULL default '0',
-> `balance` double(15,5) NOT NULL default '0.00000',
-> `balance_update_time` datetime NOT NULL default '0000-00-00 00:00:00',
-> `last_clicktime` datetime NOT NULL default '0000-00-00 00:00:00',
-> `rbalance` double(15,5) NOT NULL default '0.00000',
-> `user_load` enum('Always','Normal') NOT NULL default 'Normal',
-> `user_action` set('Distinct') NOT NULL default '',
-> `status` enum('Active','Blocked','Deleted') NOT NULL default 'Active',
-> `system_status` enum('Active','Blocked','Deleted') NOT NULL default 'Active',
-> `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> `created` timestamp NOT NULL default '0000-00-00 00:00:00',
-> PRIMARY KEY (`oid`),
-> KEY `rbalance` (`rbalance`),
-> KEY `created` (`created`),
-> KEY `status` (`status`,`system_status`)
-> ) ENGINE=BLACKHOLE DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.06 sec)
mysql> update User set last_clicktime='20061205133712' where oid=32310732;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from User where oid=32310732;
Query OK, 1 row affected (0.00 sec)
mysql>
It seems to be that blackhole in nightly build doesn's support updates and deletes by indexes... But patched 4.1.22 supports all these operations...
[5 Dec 2006 13:20]
Sergei Golubchik
Something's fishy is going on here. There shouldn't be any need for update_row/delete_row in ha_blackhole, because if the table has no rows, there's nothing to delete/update and delete/update methods should never be called.
[5 Dec 2006 13:32]
Mikhail Petrov
Sergei, I completely understand purposes of blackhole, and I think that current version of that engine don't let me use that for filtering, for example. As I wrote in previous comment, blackhole can't work with updates and deletes using primary keys. Yes, we have workaround - create table without primary keys will help, but in such case we can hit errors with altering tables.
[5 Dec 2006 14:41]
Sergei Golubchik
I wan't speaking about the purpose. I was saying that update_row/delete_row of ha_blackhole should never be called, so it doesn't matter what they return. If they are called - as in your case - it's a bug. But the bug is that they're called, not that they're not implemented.
[5 Dec 2006 14:45]
Mikhail Petrov
Oh, thanks, I've understood :) By the way, I've deleted OS - I think it affects all operating systems...
[8 Feb 2007 16:51]
Sergei Golubchik
A duplicate of BUG#19717
[11 Feb 2007 6:09]
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
Description: I've tried to use blackhole storage engine as filter for binary logs. I've compiled mysql from sources (mysql-4.1.22.tar.gz) with such options: ./configure --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --disable-shared --with-charset=cp1251 --with-collation=cp1251_general_ci --with-vio --with-openssl --with-blackhole-storage-engine --prefix=/usr --without-innodb And on updates and deletes on slave I've got these errors in error-log: 061204 15:39:03 [ERROR] Slave: Error 'Table storage engine for 'table_name' doesn't have this option' on query. Default database: ''. Query: 'update db_name.table_name set rbalance=rbalance+0.0794 where oid='41768681'', Error_code: 1031 061204 16:28:16 [ERROR] Slave: Error 'Table storage engine for 'table_name' doesn't have this option' on query. Default database: ''. Query: 'DELETE from db_name.table_name WHERE oid = '42121645'', Error_code: 1031 I tried to patch MySQL, and with a patch listed below all queries work fine. How to repeat: mysql> create table temp.foo (`bar` int(11)) engine=blackhole; Query OK, 0 rows affected (0.02 sec) mysql> update temp.foo set bar=bar+1 where bar=41768681; Table storage engine for temp.foo doesn't have this option Same behaviour I've got on deletes. Suggested fix: --- sql/ha_blackhole.cc 2006-11-03 02:16:34.000000000 +0000 +++ sql/ha_blackhole.cc 2006-12-04 13:36:44.493713731 +0000 @@ -123,6 +123,23 @@ DBUG_RETURN(0); } +int ha_blackhole::update_row(const byte * old_data, byte * new_data) +{ + DBUG_ENTER("ha_blackhole::update_row"); + DBUG_RETURN(0); +} + +int ha_blackhole::delete_row(const byte * buf) +{ + DBUG_ENTER("ha_blackhole::delete_row"); + DBUG_RETURN(0); +} + +int ha_blackhole::delete_table(const char * name) +{ + DBUG_ENTER("ha_blackhole::delete_table"); + DBUG_RETURN(0); +} uint ha_blackhole::lock_count(void) const { --- sql/ha_blackhole.h 2006-11-03 02:17:26.000000000 +0000 +++ sql/ha_blackhole.h 2006-12-04 13:36:45.951533661 +0000 @@ -80,6 +80,9 @@ void position(const byte *record); int info(uint flag); int external_lock(THD *thd, int lock_type); + int update_row(const byte * old_data, byte * new_data); + int delete_row(const byte * buf); + int delete_table(const char * name); uint lock_count(void) const; int create(const char *name, TABLE *table_arg, HA_CREATE_INFO *create_info);