MySQL Bugs: #40994: Regression: can't create unique index on NOT NULL column if engine is falcon

Bug #40994	Regression: can't create unique index on NOT NULL column if engine is falcon
Submitted:	24 Nov 2008 20:56	Modified:	13 Dec 2008 9:57
Reporter:	Alexey Stroganov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0.8-release	OS:	Any
Assigned to:	Christopher Powers	CPU Architecture:	Any
Tags:	F_ONLINE ALTER

Description:
Found this issue while analyzed mysql-bench results - statement for creation of unique index in 6.0.8 do nothing(see below). In 6.0.7 all works ok.

mysql> create table a (a int not null) engine=falcon;
Query OK, 0 rows affected (0.00 sec)

mysql> create unique index u on a (a); show create table a;
Query OK, 0 rows affected (0.00 sec)

+-------+----------------------------------------------------------------------------------+
| Table | Create Table                                                                     |
+-------+----------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `a` int(11) NOT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

How to repeat:
See description for instructions

All works ok if column can be null:

create table b (a int) engine=falcon;
Query OK, 0 rows affected (0.01 sec)

mysql> create unique index u on b (a); show create table b;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+-------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------+
| b     | CREATE TABLE `b` (
  `a` int(11) DEFAULT NULL,
  UNIQUE KEY `u` (`a`)
) ENGINE=Falcon DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Trying to reproduce this on current 6.0.9 bzr branch mysql-6.0-falcon-team, debug build on linux... Creating a UNIQUE index on a NOT NULL column actually crashes the server:

-- Nullable column works
mysql> CREATE TABLE t1 (a INT) ENGINE = Falcon;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE UNIQUE INDEX i1 on t1 (a);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a INT NOT NULL) ENGINE = Falcon;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE UNIQUE INDEX i1 on t2 (a);
ERROR 2013 (HY000): Lost connection to MySQL server during query

Anyone else see this?

Note that 
CREATE OFFLINE UNIQUE INDEX i1 on t2 (a);
works, so this seems to be related to the ONLINE ALTER ADD INDEX functionality.

Stacktrace from error log is:

Version: '6.0.9-alpha-debug-log'  socket: '/tmp/39hgcZqSYf/master.sock'  port: 9306  Source distribution
mysqld: protocol.cc:467: void net_end_statement(THD*): Assertion `0' failed.
/export/home/tmp/je159969/mysql/bzr-shared/jsandbox/sql/mysqld(my_print_stacktrace+0x26)[0x873b411]
/export/home/tmp/je159969/mysql/bzr-shared/jsandbox/sql/mysqld(handle_segfault+0x2cd)[0x82a9bc5]
[0x980420]
/lib/libc.so.6(abort+0x101)[0xa75621]
/lib/libc.so.6(__assert_fail+0xfb)[0xa6d15b]
/export/home/tmp/je159969/mysql/bzr-shared/jsandbox/sql/mysqld(_Z17net_end_statementP3THD+0x1d2)[0x829c74e]
/export/home/tmp/je159969/mysql/bzr-shared/jsandbox/sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x16f1)[0x82c424f]
/export/home/tmp/je159969/mysql/bzr-shared/jsandbox/sql/mysqld(_Z10do_commandP3THD+0x23b)[0x82c46c3]
/export/home/tmp/je159969/mysql/bzr-shared/jsandbox/sql/mysqld(handle_one_connection+0x11d)[0x82b2af1]
/lib/libpthread.so.0[0xbc445b]
/lib/libc.so.6(clone+0x5e)[0xb1bc4e]
081125 13:32:33 - mysqld got signal 6 ;

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/60338

2922 Christopher Powers	2008-12-01
      Bug#40994, "Regression: can't create unique index on NOT NULL columns if engine is falcon"
      
      The MySQL server interprets unique indexes on NOT NULL columns as a primary key.
      Falcon online add/drop index does not yet support primary keys.

Pushed into 6.0.9-alpha  (revid:cpowers@mysql.com-20081201221834-24eysrs0mv4dpgdo) (version source revid:hky@sun.com-20081205164847-vbocpvlyf6fbxkkj) (pib:5)

A note has been added to the 6.0.9 changelog: 

Unique indexes on FALCON tables can not be created when the the column is NOT NULL.