MySQL Bugs: #34669: Blackhole engine responds for partitioning queries

Bug #34669	Blackhole engine responds for partitioning queries
Submitted:	19 Feb 2008 16:07	Modified:	12 Mar 2008 21:47
Reporter:	Hema Sridharan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
1)I created a database, table with partitioning and inserted few rows in it.
2)As per the manuals the blackhole,csv,federated storage engines does not support partitioning
3) I used blackhole storage engine in my table and the sql query got accepted and the creation of table was successful.

Blackhole storage engine supports partitioning though it is mentioned that it does not. 

How to repeat:
1) create database
2)create table bt1 with paritioning and storage engine type -blackhole.

mysql> create table bt1(ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
NAME CHAR(30),AGE TINYINT UNSIGNED,DOB DATE NOT NULL,SAL BIGINT, BONUS_PERS FLOAT, PRIMARY KEY (DOB),INDEX (ID)) 
PARTITION BY RANGE(YEAR(DOB)) (PARTITION P0 VALUES LESS THAN (1970),PARTITION P1 VALUES LESS THAN (1980),PARTITION P2 VALUES LESS THAN (1990),PARTITION P3 VALUES LESS THAN (2000),PARTITION P4 VALUES LESS THAN MAXVALUE) engine=Blackhole;

Query OK, 0 rows affected (0.03 sec)

Thank you for the report.

Please indicate which tree do you use: I can not repeat described behavior with mysql-6.0 main tree.

Also please provide output of SHOW ENGINES

I used mysql-6.0-backup tree. 

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO  | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO  | NO         |
| Falcon     | YES     | Falcon storage engine                                          | YES          | NO  | YES        |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO  | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
9 rows in set (0.00 sec)

Thank you for the feedback.

I can not repeat described behavior with binaries built today on Linux.

just an idea, doesnt create table T () engine = blackhole "auto covert" to myisam (or default storage engine) if blackhole is not complied in (or explicitly disabled)

Verified like described by using bk tree from last night:

mysql> select version()\G
*************************** 1. row ***************************
version(): 6.0.5-alpha-debug

mysql> create table t(id integer, num integer not null) engine=blackhole partition by range (num) ( partition bla values less than (5), partition blubb values less than (10), partition foo values less than maxvalue);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `num` int(11) NOT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (num) (PARTITION bla VALUES LESS THAN (5) ENGINE = BLACKHOLE, PARTITION blubb VALUES LESS THAN (10) ENGINE = BLACKHOLE, PARTITION foo VALUES LESS THAN MAXVALUE ENGINE = BLACKHOLE) */

I did not find any comment about Blackhole and partitioning in 6.0, but I found this in 5.1:

BLACKHOLE storage engine.   Prior to MySQL 5.1.6, tables using the BLACKHOLE storage engine also could not be partitioned.

From:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-storage-engines.html

Where does it mention that blackhole does not support partitioning?

From e-mail between Mattias Jonsson and Hema Sridharan

Mattias Jonsson wrote:
Thank you for your answer. If it says so in the mysql 5.0 training guide, then the errata should be updated. I cc:ed the docs-team, so they will perhaps fix this! (could you update them on which page etc?)

I will close the bug as 'Not a bug' and include the content of this email as a comment.

Regard
Mattias

Hema Sridharan wrote:
> Hi Mattias,
>
> Thanks for the information. I read this from the mysql 5.0 training guide I
> got and thereby filed the defect. I recently noticed that query of table
> partitions with BLACKHOLE storage engine is supported in mysql-6.0. So in
> such case, this bug could be closed as "Not a bug". Let me know, if you have
> any questions.