Page MenuHomePhabricator

Dropping a column which is a component of a UNIQUE KEY has surprising behavior in MariaDB
Closed, ResolvedPublic

Description

See https://discourse.phabricator-community.org/t/unable-to-apply-autopache-20180828-phriction-06-documentid-sql/1848/.

In D19619, I dropped a documentID column, but this column will generally be part of a UNIQUE KEY (documentID, version) at the time it is dropped. This is kind of understandably not a great idea, and MariaDB is actively upset about it and considers it an error.

Note that this unique key is also called documentID. The table is an older table from before bin/storage adjust -- new keys are generally named key_xyz (and named explicitly) to avoid this kind of confusion, since having keys with the same names as columns is broadly unhelpful.

In D19624, I changed the patch sequence to drop the key (if it exists) first, then add the new unique key after dropping the column. Explicitly adding unique keys in the migration sequence is generally good practice anyway (there's no guarantee that users complete bin/storage adjust to get them added otherwise) and the old patch sequence wasn't great, it just wasn't bad in a very obvious way, and MySQL was okay with the badness while recent MariaDB was less impressed with my shell game around the documentID / documentPHID column.