| Bug #15133 | unique index with nullable value not accepted in federated table | ||
|---|---|---|---|
| Submitted: | 22 Nov 2005 13:32 | Modified: | 17 Aug 2006 11:23 |
| Reporter: | Giuseppe Maxia | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Federated storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.16/5.0.17 BK | OS: | Linux (Linux) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[4 Apr 2006 18:23]
Timothy Smith
It is not only a UNIQUE index - any index with nullable values is rejected: CREATE TABLE `t2` ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '', `value` varchar(64) default NULL, KEY `value` (`value`) engine=federated CONNECTION='mysql://miguel:a@192.168.0.33/test/test'; NULL, KEY `value` (`value`)) engine=federated CONNECTION='mysql://miguel:a@192.168.0.33/test/t2'; ERROR 1121 (42000): Column 'value' is used with UNIQUE or INDEX but is not defined as NOT NULL Regards, Timothy
[11 Jul 2006 16:32]
Patrick Galbraith
wake-up call - I'm working on this now, this week!
[12 Jul 2006 5:55]
Patrick Galbraith
bk mail of commit message not working, but I do have a patch.
[13 Jul 2006 14:00]
Ingo Strüwing
Cool. Again. For a review one must see the patch. So please get your email problems fixed, run "bk fix -c", and "bk commit" again.
[13 Jul 2006 22:09]
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/9146
[13 Jul 2006 22:09]
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/9147
[13 Jul 2006 22:09]
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/9148
[18 Jul 2006 14:53]
Patrick Galbraith
pushed.
[18 Jul 2006 15:15]
Calvin Sun
please add the three-part version numbers.
[18 Jul 2006 15:46]
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/9281
[25 Jul 2006 22:36]
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/9557
[27 Jul 2006 8:51]
Calvin Sun
pushed to 5.0-engines tree.
[16 Aug 2006 15:22]
Patrick Galbraith
merged from mysql-5.0-engines to main 5.0 tree, version 5.0.25
[17 Aug 2006 11:23]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Documented bugfix in 5.0.25.
Description: A UNIQUE KEY allows one or more of its components to be nullable. However, setting such a UNIQUE key in a federated table results in a error. Example: mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `i` int(11) NOT NULL, `j` int(11) NOT NULL, `c` varchar(30) default NULL, PRIMARY KEY (`i`,`j`), UNIQUE KEY `i` (`i`,`c`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> alter table test engine=innodb; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `i` int(11) NOT NULL, `j` int(11) NOT NULL, `c` varchar(30) default NULL, PRIMARY KEY (`i`,`j`), UNIQUE KEY `i` (`i`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.02 sec) mysql> create table test1(i int not null , j int not null, c varchar(30), primary key (i,j), unique key (i, c)) engine = federated connection='mysql://user:password@127.0.0.1/test/test'; ERROR 1121 (42000): Column 'c' is used with UNIQUE or INDEX but is not defined as NOT NULL How to repeat: create database if not exists test; use test; create table test(i int not null , j int not null, c varchar(30), primary key (i,j), unique key (i, c)); show create table test\G alter table test engine=innodb; show create table test\G create table test1(i int not null , j int not null, c varchar(30), primary key (i,j), unique key (i, c)) engine = federated connection='mysql://user:password@127.0.0.1/test/test'; Suggested fix: None. Removing the index, perhaps?