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
Triage: Triaged: D2 (Serious)

[1 Jul 2009 18:26] Dmitry Lenev
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
[1 Jul 2009 18:44] Sveta Smirnova
Thank you for the report.

Verified as described.
[7 Jul 2009 11:43] John Embretsen
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)
[15 Jul 2009 2:19] Christopher Powers
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.
[15 Jul 2009 3:32] Christopher Powers
Related: Bug#46188 "Server raises fatal exception if engine returns error during ONLINE ALTER"
[17 Jul 2009 0:29] 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/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
[5 Aug 2009 2:35] 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/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