Unable to add primary key to innodb table without one

Question

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.

The following:

ALTER TABLE my_wonky_table 
    ADD COLUMN count SMALLINT NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (id, count);

yields:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Any ideas?

Answer 1

You can have an auto-Incrementing column, as long as there is an index (key) on it, to fix this issue you need to execute those commands:

ALTER TABLE my_wonky_table ADD COLUMN count SMALLINT NOT NULL;
ALTER TABLE my_wonky_table ADD INDEX (count);
ALTER TABLE my_wonky_table CHANGE count count SMALLINT NOT NULL AUTO_INCREMENT;
ALTER TABLE my_wonky_table ADD PRIMARY KEY (id, count);

Answer 2

It is not the "hidden PK" that is in the way. ALTER TABLE ... ADD PRIMARY KEY(...) will replace it. The problem is that the "partition key", id in your case, must be part of every UNIQUE key, and the PK is UNIQUE.

It is more efficient to do all ALTERs in a single statement. (There are a few exceptions.)

ALTER TABLE my _wonky_table
    ADD COLUMN count SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    ADD PRIMARY KEY(count, id);

count SMALLINT for the PK:

• You are not expecting more than 32K rows in the table? In general you should not use PARTITIONing on a table with fewer than 1M rows.
• Don't you want SMALLINT UNSIGNED?
• "count" is an unusual name for the AI; that, plus having id not be the AI, will confuse readers; please rethink both column names.

"Index prefixing" is rarely good. Recommend removing "(16)".

KEY `my_wonky_table_ip_idx` (`ip`(16),`id`)

Do you really have only one partition? If so, why partition at all?

Let's see some of the SELECTs; we can discuss the utility (or lack of) of the indexes.