Bug #13341 mysqldump --xml omit on delete restrict on update restrict

• Submitted: 20 Sep 2005 8:00
• Modified: 20 Sep 2005 17:24
• Reporter: Frédéric Hardy
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: Ver 10.9 Distrib 4.1.14/5.0.XX
• OS: FreeBSD (Freebsd)
• CPU Architecture: Any

[20 Sep 2005 8:00] Frédéric Hardy

Description:
if an InnoDB table has on update cascade and on delete cascade constraints, only on update cascade is in the xml dump.

How to repeat:
Create an InnoDB table with on delete cascade and on update cascade foreign keys constraint, and make a mysqldump --xml on this table.

[20 Sep 2005 16:58] MySQL Verification Team

Using the sample from the Manual ON DELETE RESTRICT vs ON DELETE CASCADE
and SHOW CREATE TABLE output. It at least should be documented.

miguel@hegel:~/dbs/4.1> bin/mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.15-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE dbx;
Query OK, 1 row affected (0.02 sec)

mysql> USE dbx;
Database changed
mysql> CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
    ->                       price DECIMAL,
    ->                       PRIMARY KEY(category, id)) engine=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE customer (id INT NOT NULL,
    ->                       PRIMARY KEY (id)) engine=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
    ->                       product_category INT NOT NULL,
    ->                       product_id INT NOT NULL,
    ->                       customer_id INT NOT NULL,
    ->                       PRIMARY KEY(no),
    ->                       INDEX (product_category, product_id),
    ->                       FOREIGN KEY (product_category, product_id)
    ->                         REFERENCES product(category, id)
    ->                         ON UPDATE CASCADE ON DELETE RESTRICT,
    ->                       INDEX (customer_id),
    ->                       FOREIGN KEY (customer_id)
    ->                         REFERENCES customer(id)) engine=INNODB;
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW CREATE TABLE product_order;

| product_order | CREATE TABLE `product_order` (
  `no` int(11) NOT NULL auto_increment,
  `product_category` int(11) NOT NULL default '0',
  `product_id` int(11) NOT NULL default '0',
  `customer_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`no`),
  KEY `product_category` (`product_category`,`product_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `product_order_ibfk_1` FOREIGN KEY (`product_category`, `product_id`) REFERENCES `product` (`category`, `id`) ON UPDATE CASCADE,
  CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.00 sec)

mysql> DROP DATABASE dbx;
Query OK, 3 rows affected (0.03 sec)

mysql> CREATE DATABASE dbx;
Query OK, 1 row affected (0.00 sec)

mysql> USE dbx;
Database changed
mysql> CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
    ->                       price DECIMAL,
    ->                       PRIMARY KEY(category, id)) engine=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE customer (id INT NOT NULL,
    ->                       PRIMARY KEY (id)) engine=INNODB;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
    ->                       product_category INT NOT NULL,
    ->                       product_id INT NOT NULL,
    ->                       customer_id INT NOT NULL,
    ->                       PRIMARY KEY(no),
    ->                       INDEX (product_category, product_id),
    ->                       FOREIGN KEY (product_category, product_id)
    ->                         REFERENCES product(category, id)
    ->                         ON UPDATE CASCADE ON DELETE CASCADE,
    ->                       INDEX (customer_id),
    ->                       FOREIGN KEY (customer_id)
    ->                         REFERENCES customer(id)) engine=INNODB;
Query OK, 0 rows affected (0.09 sec)

mysql> SHOW CREATE TABLE product_order;
 
| product_order | CREATE TABLE `product_order` (
  `no` int(11) NOT NULL auto_increment,
  `product_category` int(11) NOT NULL default '0',
  `product_id` int(11) NOT NULL default '0',
  `customer_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`no`),
  KEY `product_category` (`product_category`,`product_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `product_order_ibfk_1` FOREIGN KEY (`product_category`, `product_id`) REFERENCES `product` (`category`, `id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.01 sec)

[20 Sep 2005 17:24] Heikki Tuuri

Hi!

SHOW CREATE TABLE does NOT omit ON DELETE CASCADE or ON UPDATE CASCADE.

It does omit ON DELETE RESTRICT and ON UPDATE RESTRICT, because RESTRICT is the default behavior of FOREIGN KEY constraints. It does not add anything to write those clauses to SHOW CREATE TABLE.

I believe this is not a bug. Changing the status to 'Not a bug'.

Regards,

Heikki