MySQL Bugs: #17143: Partitions: no error message when failing to add foreign key

Bug #17143	Partitions: no error message when failing to add foreign key
Submitted:	5 Feb 2006 20:42	Modified:	13 Apr 2006 13:45
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.7-beta-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Assigned Account	CPU Architecture:	Any

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)

Thank you for the bug report.

This bug is no longer present in the current system that I can find.
The error message is however not very clear, error: 1

In that case, let's please fix the error message to something that actually tells the user what the problem was. Thanks.

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>

Will take care of all foreign key problems in Bug #14353

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.