Bug #19641 | Merge table does not work properly with primary keys | ||
---|---|---|---|
Submitted: | 9 May 2006 16:11 | Modified: | 29 Jun 2006 19:51 |
Reporter: | Predrag Ostojic | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Merge storage engine | Severity: | S2 (Serious) |
Version: | 5.0.18/4.1BK/5.0BK/5.1BK | OS: | Linux (Linux 9.2) |
Assigned to: | Antony Curtis | CPU Architecture: | Any |
[9 May 2006 16:11]
Predrag Ostojic
[9 May 2006 16:26]
MySQL Verification Team
Thank you for the bug report. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.1.10-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table t1 (i int, a varchar(16), primary key(i) ); Query OK, 0 rows affected (0.00 sec) mysql> create table t2 (i int, a varchar(16), primary key(i) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1,"one"); Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values(1,"two"); Query OK, 1 row affected (0.00 sec) mysql> create table tm (i int, a varchar(16), primary key(i) ) engine = merge -> union = (t1,t2); Query OK, 0 rows affected (0.00 sec) mysql> select * from tm; +---+------+ | i | a | +---+------+ | 1 | one | | 1 | two | +---+------+ 2 rows in set (0.01 sec) mysql> select * from tm where i = 1; +---+------+ | i | a | +---+------+ | 1 | one | +---+------+ 1 row in set (0.00 sec) mysql> select * from tm where i <= 1; +---+------+ | i | a | +---+------+ | 1 | one | | 1 | two | +---+------+ 2 rows in set (0.01 sec)
[29 Jun 2006 19:51]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/. See http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html "A MERGE table cannot maintain uniqueness constraints over the entire table. When you perform an INSERT, the data goes into the first or last MyISAM table (depending on the value of the INSERT_METHOD option). MySQL ensures that unique key values remain unique within that MyISAM table, but not across all the tables in the collection." and an example at http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html "Note that the a column is indexed as a PRIMARY KEY in the underlying MyISAM tables, but not in the MERGE table. There it is indexed but not as a PRIMARY KEY because a MERGE table cannot enforce uniqueness over the set of underlying tables."