Description:
We were seeing the backup/restore failing and were not seeing exactly why..
We had a situation that we inserted records without specifying an ENUM "status" and it later crashed backup/restore.
How to repeat:
Quick example:
-- ----------------------------
-- Table structure for `status`
-- ----------------------------
DROP TABLE IF EXISTS `status`;
CREATE TABLE `status` (
`id_auto` int(11) NOT NULL AUTO_INCREMENT,
`my_status` enum('Active','Inactive') NOT NULL,
PRIMARY KEY (`id_auto`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of status
-- ----------------------------
INSERT INTO `status` VALUES ('1', 'Active');
INSERT INTO `status` VALUES ('2', 'Inactive');
-- ----------------------------
-- This should not be allowed
-- ----------------------------
INSERT IGNORE INTO status (my_status) VALUES (NULL);
-- ----------------------------
SELECT * FROM status;
-- ----------------------------
1 Active
2 Inactive
3
-- ----------------------------
Suggested fix:
Enforce ENUM rules and NOT NULL
Description: We were seeing the backup/restore failing and were not seeing exactly why.. We had a situation that we inserted records without specifying an ENUM "status" and it later crashed backup/restore. How to repeat: Quick example: -- ---------------------------- -- Table structure for `status` -- ---------------------------- DROP TABLE IF EXISTS `status`; CREATE TABLE `status` ( `id_auto` int(11) NOT NULL AUTO_INCREMENT, `my_status` enum('Active','Inactive') NOT NULL, PRIMARY KEY (`id_auto`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of status -- ---------------------------- INSERT INTO `status` VALUES ('1', 'Active'); INSERT INTO `status` VALUES ('2', 'Inactive'); -- ---------------------------- -- This should not be allowed -- ---------------------------- INSERT IGNORE INTO status (my_status) VALUES (NULL); -- ---------------------------- SELECT * FROM status; -- ---------------------------- 1 Active 2 Inactive 3 -- ---------------------------- Suggested fix: Enforce ENUM rules and NOT NULL