| Bug #17143 | Partitions: no error message when failing to add foreign key | ||
|---|---|---|---|
| Submitted: | 5 Feb 2006 21:42 | Modified: | 13 Apr 2006 15:45 |
| Reporter: | Peter Gulutzan | ||
| Status: | Duplicate | ||
| Category: | Server: Partition | Severity: | S3 (Non-critical) |
| Version: | 5.1.7-beta-debug | OS: | Linux (SUSE 10.0) |
| Assigned to: | Mikael Ronstrom | Target Version: | |
[6 Feb 2006 1:44]
Miguel Solorzano
Thank you for the bug report.
[21 Mar 2006 18:19]
Mikael Ronstrom
This bug is no longer present in the current system that I can find. The error message is however not very clear, error: 1
[24 Mar 2006 7:38]
Jon Stephens
In that case, let's please fix the error message to something that actually tells the user what the problem was. Thanks.
[4 Apr 2006 21:32]
Miguel Solorzano
I still get the same behavior as originally reported:
miguel@hegel:~/dbs/mysql-5.1-new> bk changes | head
ChangeSet@1.2282, 2006-04-04 20:25:23+02:00, joerg@mysql.com
Merge
ChangeSet@1.2281, 2006-04-04 14:15:01+03:00, timour@mysql.com
Merge mysql.com:/home/timka/mysql/src/5.0-tmp-merge
into mysql.com:/home/timka/mysql/src/5.1-merge
miguel@hegel:~/dbs/5.1> bin/mysqladmin -uroot create db55
miguel@hegel:~/dbs/5.1> bin/mysql -uroot db55
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.9-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table txx1 (s1 int, primary key (s1)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> create table txx2 (s1 int) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table txx2 add constraint foreign key (s1) references txx1 (s1);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table txx2\G
*************************** 1. row ***************************
Table: txx2
Create Table: CREATE TABLE `txx2` (
`s1` int(11) DEFAULT NULL,
KEY `s1` (`s1`),
CONSTRAINT `txx2_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `txx1` (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> create table txx3 (s1 int) engine=innodb partition by list (s1)
-> (partition p1 values in (1));
Query OK, 0 rows affected (0.01 sec)
mysql> alter table txx3 add constraint foreign key (s1) references txx1 (s1);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table txx3\G
*************************** 1. row ***************************
Table: txx3
Create Table: CREATE TABLE `txx3` (
`s1` int(11) DEFAULT NULL,
KEY `s1` (`s1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY LIST (s1) (PARTITION p1 VALUES IN (1)
ENGINE = InnoDB)
1 row in set (0.01 sec)
mysql>
[13 Apr 2006 15:45]
Mikael Ronstrom
Will take care of all foreign key problems in Bug #14353
[4 Dec 2007 2:23]
Kolbe Kegel
This bug was never resolved as of 5.1.22, neither on its own or as part of the resolution for bug #14353. mysql 5.1.22-rc (root) [test]> create table txx3 (s1 int) engine=innodb partition by list (s1) (partition p1 values in (1)); Query OK, 0 rows affected (0.05 sec) mysql 5.1.22-rc (root) [test]> alter table txx3 add constraint foreign key (s1) references txx1 (s1); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.1.22-rc (root) [test]> show create table txx3\G *************************** 1. row *************************** Table: txx3 Create Table: CREATE TABLE `txx3` ( `s1` int(11) DEFAULT NULL, KEY `s1` (`s1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (s1) (PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */ 1 row in set (0.02 sec) The ALTER TABLE to add the FK constraint to txx3 appears to succeed, but the FK constraint is *not* reflected in a subsequent SHOW CREATE TABLE.
Description: I can't add a foreign key to a partitioned table. That's a severe failure, so I should see an error message. I don't even see a warning. How to repeat: /* First I'll create a primary-key table with InnoDB. Then I'll create and alter a foreign-key table, just to demonstrate that the result will be a table with a foreign key. Then I'll create and alter a foreign-key partitioned table. It seems to succeed with no error but, as the SHOW CREATE shows, it doesn't. */ mysql> create table txx1 (s1 int, primary key (s1)) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> create table txx2 (s1 int) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> alter table txx2 add constraint foreign key (s1) references txx1 (s1); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table txx2; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | txx2 | CREATE TABLE `txx2` ( `s1` int(11) default NULL, KEY `s1` (`s1`), CONSTRAINT `txx2_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `txx1` (`s1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create table txx3 (s1 int) engine=innodb partition by list (s1) (partition p1 values in (1)); Query OK, 0 rows affected (0.01 sec) mysql> alter table txx3 add constraint foreign key (s1) references txx1 (s1); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table txx3; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | txx3 | CREATE TABLE `txx3` ( `s1` int(11) default NULL, KEY `s1` (`s1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY LIST (s1) (PARTITION p1 VALUES IN (1) ENGINE = InnoDB) | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)