Page MenuHomePhabricator

Duplicate column name "spacePHID"
Closed, WontfixPublic

Description

While running ./bin/storage upgrade I got the following error:

Applying patch 'phabricator:20150609.spaces.1.pholio.sql'...
[2015-06-13 20:12:40] EXCEPTION: (AphrontQueryException) #1060: Duplicate column name 'spacePHID' at [<phutil>/src/aphront/storage/connection/mysql/AphrontBaseMySQLDatabaseConnection.php:311]
arcanist(head=master, ref.master=cdaa0e32e473), phabricator(head=master, ref.master=e74f027f0ac2, custom=1), phutil(head=master, ref.master=a9e581f6488f)
  #0 AphrontBaseMySQLDatabaseConnection::throwQueryCodeException(integer, string) called at [<phutil>/src/aphront/storage/connection/mysql/AphrontBaseMySQLDatabaseConnection.php:275]
  #1 AphrontBaseMySQLDatabaseConnection::throwQueryException(mysqli) called at [<phutil>/src/aphront/storage/connection/mysql/AphrontBaseMySQLDatabaseConnection.php:181]
  #2 AphrontBaseMySQLDatabaseConnection::executeRawQuery(string) called at [<phutil>/src/xsprintf/queryfx.php:6]
  #3 queryfx(AphrontMySQLiDatabaseConnection, string, string) called at [<phabricator>/src/infrastructure/storage/management/PhabricatorStorageManagementAPI.php:220]
  #4 PhabricatorStorageManagementAPI::applyPatchSQL(string) called at [<phabricator>/src/infrastructure/storage/management/PhabricatorStorageManagementAPI.php:188]
  #5 PhabricatorStorageManagementAPI::applyPatch(PhabricatorStoragePatch) called at [<phabricator>/src/infrastructure/storage/management/workflow/PhabricatorStorageManagementUpgradeWorkflow.php:190]
  #6 PhabricatorStorageManagementUpgradeWorkflow::execute(PhutilArgumentParser) called at [<phutil>/src/parser/argument/PhutilArgumentParser.php:406]
  #7 PhutilArgumentParser::parseWorkflowsFull(array) called at [<phutil>/src/parser/argument/PhutilArgumentParser.php:301]
  #8 PhutilArgumentParser::parseWorkflows(array) called at [<phabricator>/scripts/sql/manage_storage.php:176]

The web interface was down and all daemons were stopped. Is this a bug or I am doing something wrong?

Event Timeline

SalmonKiller raised the priority of this task from to Needs Triage.
SalmonKiller updated the task description. (Show Details)
SalmonKiller added a subscriber: SalmonKiller.

Did you run bin/storage upgrade more than once?

That is expected, because you're basically adding the same columns twice. Why did you run it multiple times?

I ran bin/storage upgrade, then I stopped it while it was executing to stop the daemons that I left running. The next time I ran bin/storage upgrade I got the error, probably because the script didn't finish adding the elements to the table.

epriestley claimed this task.
epriestley added a subscriber: epriestley.

(It sounds like @lpriestley helped you resolve this specific issue.)

Running the upgrade script multiple times is fine, but it's expected that interrupting the upgrade script may leave you in a bad state.

Normally, we use database transactions to prevent only part of a change from applying. However, we can't perform some updates transactionally, because issuing an ALTER TABLE statement commits any outstanding transaction:

https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

For some statements, like DROP DATABASE, there is an idempotent alternative we can use (like DROP DATABASE IF EXISTS...). This makes migrations repeatable, but it isn't available for all statements. Particularly, there is no ALTER TABLE ONLY IF THIS ALTERATION WOULD HAVE AN EFFECT... or equivalent, as far as I know. (Until 5.7.4, there is an ALTER IGNORE TABLE..., but this doesn't do what we need and has been removed in newer MySQL.)

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

We mark patches as applied immediately after applying them, so this issue should be exceedingly rare (I don't think we've seen other users encounter it in the past) but I don't see a straightforward way we can completely prevent it. (I suppose, possibly, we could install an interrupt handler and intercept ^C, then decline to exit until the next patch fully applied.)

We could continue putting a thicker layer of indirection on top of patches, and eventually move more (or even all) migration statements into PHP. This will probably happen in the long run (we've needed to put more and more indirection on top of patches to deal with extensions, UTF8mb4, table keys, and other issues) but it's a huge amount of work and hard to prioritize on its own given how rare these issues are in practice.

Could someone post the solution here? I am also experiencing this problem.

Delete all the text in 20150609.spaces.1.pholio.sql (but not the file itself), then run bin/storage upgrade again.

(If you immediately get another error, there's some more serious problem with state tracking. This usually means you imported/exported awkwardly or mucked with the data somehow, and recovery depends on exactly what state you're in and how you got there.)

(If you immediately get another error, there's some more serious problem with state tracking. This usually means you imported/exported awkwardly or mucked with the data somehow, and recovery depends on exactly what state you're in and how you got there.)