CODEWITHSUNDEEP
mysqlsqlplanetscale
reactor
reactor

Reputation: 1931

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

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?

Upvotes: 1

Views: 504

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

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.

Upvotes: 2

Related Questions