I've inherited myself a fun table with some peculiar indexes (and no primary key) on it, that looks as follows:
CREATE TABLE `my_wonky_table` ( `id` bigint(20) unsigned NOT NULL, `login` varchar(127) DEFAULT NULL, `timestamp` int(10) unsigned NOT NULL, `ip` varchar(32) CHARACTER SET ascii DEFAULT NULL, `val_1` int(10) unsigned DEFAULT NULL, `val_2` varchar(127) DEFAULT NULL, `val_3` varchar(255) DEFAULT NULL, `val_4` varchar(127) DEFAULT NULL, `val_5` int(10) unsigned DEFAULT NULL, KEY `my_wonky_table_id_idx` (`id`), KEY `my_wonky_table_timestamp_idx` (`timestamp`), KEY `my_wonky_table_val_1_idx` (`val_1`,`id`), KEY `my_wonky_table_val_2_idx` (`val_2`,`id`), KEY `my_wonky_table_val_4_idx` (`val_4`,`id`), KEY `my_wonky_table_val_5_idx` (`val_5`,`id`), KEY `my_wonky_table_ip_idx` (`ip`(16),`id`), KEY `my_wonky_table_login_idx` (`login`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (`id`) (PARTITION pdefault VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
Now, because it has no primary key, nor a fully unique index, MySQL adds a
GEN_CLUST_INDEX (that's taking up quite some space, but that's a different matter).
Note that even though there is a column called
id, it's not actually a unique entry. For this reason, I want to add an (auto incremented) column, that, combined with the
id column, should act as the primary key.
However, I appear to be unable to add an auto_incremented column; I suspect this is because of the 'secret' column MySQL adds, because of the error message saying I already have an auto-incremented column, even though I don't believe I do.
ALTER TABLE my_wonky_table ADD COLUMN count SMALLINT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id, count);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key