drop primary key giving error saying it doesn't exist but won't allow me to add since it says one alraedy exists

Question

I have a mysql table:

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| hash    | varchar(255) | NO   | PRI | NULL    |       |
| user_id | bigint       | NO   | PRI | NULL    |       |
+---------+--------------+------+-----+---------+-------+

but when running alter table sessions drop primary key; I get

ERROR 1091 (42000): target: my-table.-.primary: vttablet: rpc error: code = FailedPrecondition desc = Can't DROP 'PRIMARY'; check that column/key exists (errno 1091) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "alter table sessions drop primary key", BindVars: {}

so it sounds like I'm in the clear to add a primary key like so alter table sessions add primary key (user_id);, but I get:

ERROR 1062 (23000): target: my-table.-.primary: vttablet: rpc error: code = AlreadyExists desc = Duplicate entry '1' for key 'sessions.PRIMARY' (errno 1062) (sqlstate 23000) (CallerID: planetscale-admin): Sql: "alter table sessions add primary key (user_id)", BindVars: {}

how can I fix this?

Provide your table as its complete CREATE TABLE script. PS. The error message shown is not MySQL one... — Akina, Oct 16 '22 at 18:49
The SQL code which drops PK is correct itself: https://dbfiddle.uk/ZRWh8Rfd — Akina, Oct 16 '22 at 18:58

score 2 · Accepted Answer · answered Oct 16 '22 at 19:27

I see the caller is planetscale-admin. So I guess you're using PlanetScale Vitess. One of the requirements of PlanetScale is that the table must have a primary or unique key on not-null columns at all times. They use a "ghost table" to perform online table alters, and that tool requires both the original and the ghost table to have a primary key (or non-null unique key).

Read https://planetscale.com/docs/learn/change-single-unique-key for instructions on migrating your primary key without violating their requirement.

drop primary key giving error saying it doesn't exist but won't allow me to add since it says one alraedy exists

1 Answers1