Description:
when doing a backup in MySQL Administrator GUI using default settings, sometimes a column gets omitted from the backup. the workaround is to use the command-line tool mysqldump which works fine.
How to repeat:
In my case, it was a column that had a plain index on it, it was subcat_id in the subcategories table, as defined below, that got omitted. all 3 tables were backed up. I picked these 3 selected tables to backup. I don't know what causes the omission.
CREATE TABLE `dbo`.`categories` (
`cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category here',
PRIMARY KEY(`cat_id`),
UNIQUE KEY `ix_ccat_id` (`cat_id`),
INDEX `ix_ccat`(`category`)
) ENGINE=InnoDB;
CREATE TABLE `dbo`.`subcategories` (
`sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
`cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from categories table',
`subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of subcategory',
FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
PRIMARY KEY(`sub_id`),
INDEX `ix_sccat_id`(`cat_id`),
INDEX `ix_scsubcat_id`(`subcat_id`)
) ENGINE=InnoDB;
CREATE TABLE `dbo`.`categoryroots` (
`root_id` int(10) unsigned NOT NULL auto_increment COMMENT 'ignore',
`cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of cat_id from categories table',
PRIMARY KEY (`root_id`),
FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
UNIQUE KEY `ix_crcat_id` (`cat_id`)
) ENGINE=InnoDB;
INSERT INTO categories(cat_id,category) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d'),
(5,'e'),
(6,'f'),
(7,'g'),
(8,'h'),
(9,'i'),
(10,'j'),
(11,'k'),
(12,'l'),
(13,'m'),
(14,'n');
INSERT INTO categoryroots(cat_id) VALUES
(1),
(14);
INSERT INTO subcategories(cat_id,subcat_id) VALUES
(1,2),
(1,3),
(1,4),
(2,5),
(2,6),
(2,7),
(3,8),
(3,9),
(3,10),
(4,11),
(4,12),
(4,13);
