MySQL Bugs: #45885: Primary key added to Falcon table allows non-unique values.

Bug #45885	Primary key added to Falcon table allows non-unique values.
Submitted:	1 Jul 2009 18:26	Modified:	26 May 2010 17:46
Reporter:	Dmitry Lenev	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.12-bzr	OS:	Any
Assigned to:	Christopher Powers	CPU Architecture:	Any
Tags:	F_ONLINE ALTER

Description:
I am using the latest snapshot from mysql-6.0-falcon-team tree,

I am trying to add primary key on a column in Falcon which has some duplicate values. ALTER TABLE succeeds without any error being emitted (particularly I have expected ER_DUP_ENTRY). As result I have a table with a primary key with some duplicate values. Indeed for both InnoDB and MyISAM the expected error is emitted at ALTER TABLE's time.

This bug was discovered while working on new foreign key implementation.

How to repeat:
set storage_engine= Falcon;
create table t1 (a int);
insert into t1 values (1), (1), (1);
# The below statements succeed although it should not!
alter table t1 add primary key (a);
# As result with have table with primary key
show create table t1;
# Table  Create Table
# t1     CREATE TABLE `t1` (
#   `a` int(11) NOT NULL DEFAULT '0',
#   PRIMARY KEY (`a`)
# ) ENGINE=Falcon DEFAULT CHARSET=latin1
#
# ... with duplicate values!
select * from t1;
# a
# 1
# 1
# 1

Thank you for the report.

Verified as described.

This issue is apparently related to the ONLINE ALTER feature. This is the default unless "OFFLINE" is specified:

mysql> CREATE TABLE t1 (a int, b int) Engine = Falcon;
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO t1 VALUES (1, 11), (1, 11), (2, 22);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+---+------+
| a | b    |
+---+------+
| 1 |   11 |
| 1 |   11 |
| 2 |   22 |
+---+------+
3 rows in set (0.00 sec)

mysql> ALTER OFFLINE TABLE t1 ADD PRIMARY KEY (a);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> ALTER TABLE t1 ADD PRIMARY KEY (a);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Also note that the index (primary key) misses or ignores the duplicate value in the following example query:

mysql> SELECT * FROM t1 WHERE a = 1;
+---+------+
| a | b    |
+---+------+
| 1 |   11 |
+---+------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t1 WHERE a > 0;
+---+------+
| a | b    |
+---+------+
| 1 |   11 |
| 1 |   11 |
| 2 |   22 |
+---+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE b = 11;
+---+------+
| a | b    |
+---+------+
| 1 |   11 |
| 1 |   11 |
+---+------+
2 rows in set (0.00 sec)

There are two issues here:

1) Falcon does not report a duplicate key error when building a new primary or unique index, and

2) The server cannot tolerate errors during online alter.

Falcon creates primary or unique indexes in alter phase 2. Pre-existing duplicate keys are discovered when the index is populated. If Falcon encounters a duplicate key and returns an error in alter phase 2, the server raises a fatal exception.

Therefore, online add primary/unique indexes is disabled until the server can except a duplicate key error without crashing.

Related: Bug#46188 "Server raises fatal exception if engine returns error during ONLINE ALTER"

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

2756 Christopher Powers	2009-07-16
      Bug#45885 "Primary key added to Falcon table allosw non-unique values"
      
      Temporarily disabled online alter add primary key/unique index until
      Bug#46118 "Server raises fatal exception if engine returns error during ONLINE ALTER"
      
      Added unique value check and exception handling to online add index.
      modified:
        mysql-test/suite/falcon/r/falcon_bug_45775.result
        mysql-test/suite/falcon/r/falcon_online_index.result
        mysql-test/suite/falcon/t/disabled.def
        mysql-test/suite/falcon/t/falcon_online_index.test
        storage/falcon/Statement.cpp
        storage/falcon/Table.cpp
        storage/falcon/Table.h
        storage/falcon/ha_falcon.cpp

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

2763 Christopher Powers	2009-08-04
      Bug#45885 "Primary key added to Falcon table allow non-unique values"
            
      Disabled online alter add primary key/unique index until Bug#46118
      "Server raises fatal exception if engine returns error during ONLINE ALTER"
      is fixed.
      modified:
        mysql-test/suite/falcon/r/falcon_bug_45775.result
        mysql-test/suite/falcon/r/falcon_online_index.result
        mysql-test/suite/falcon/t/disabled.def
        mysql-test/suite/falcon/t/falcon_online_index.test
        storage/falcon/Statement.cpp
        storage/falcon/Table.cpp
        storage/falcon/Table.h
        storage/falcon/ha_falcon.cpp