创建语句上的 MySQL 外键子句导致连接丢失错误

Question

我正在运行 MySQL 5.5.43-0+deb8u1 并尝试创建下表:

CREATE TABLE IF NOT EXISTS `car_favorites` (  
  `id` INTEGER UNSIGNED auto_increment ,  
  `user_id` INTEGER UNSIGNED NOT NULL,  
  `car_id` INTEGER UNSIGNED NOT NULL,  
  `created_at` DATETIME NOT NULL,  
  `updated_at` DATETIME NOT NULL,  
  `deleted_at` DATETIME,   
  UNIQUE `user_id_car_id` (`user_id`, `car_id`),   
  PRIMARY KEY (`id`),   
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,   
  FOREIGN KEY (`car_id`) REFERENCES `cars` (`id`) ON DELETE SET NULL ON UPDATE CASCADE   
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

引用的表users 和cars 已经存在。当我尝试运行此查询时，出现以下错误:

ERROR 2013 (HY000): Lost connection to MySQL server during query

之后，当执行类似SHOW TABLES 的操作时会列出该表，但如果我尝试对该表执行查询(即DESCRIBE car_favorites)，该服务会报告表不存在，重启服务表消失。

但是，当我删除这部分查询时:

FOREIGN KEY (`car_id`) REFERENCES `cars` (`id`) ON DELETE SET NULL ON UPDATE CASCADE

表创建没有问题。奇怪的是，如果我运行这个查询:

SHOW ENGINE INNODB STATUS;

运行失败的创建查询后，没有显示或列出任何外键错误。打开 MySQL 日志记录并检查错误日志显示了这个相当模糊的错误:

13:21:41 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

后跟堆栈跟踪和一些其他信息，但没有任何具体信息。

我不知道为什么这个外键子句会导致这次崩溃。任何正确方向的见解或观点将不胜感激。

Answer 1

通过写这个问题的过程，我找到了答案。如果您真的深吸一口气并阅读创建查询，就会很明显。

因为我用 NOT NULL 声明了 car_id 列，所以外键声明的 ON DELETE SET NULL 部分不会任何意义。将该子句更改为 ON DELETE CASCADE 解决了问题。

为什么 MySQL 卡死了，不能给我更具体的错误，我不知道。