Data loss on table renaming. #17

Closed
ThePixelDeveloper opened this Issue Nov 7, 2010 · 8 comments

Comments

Projects
None yet
7 participants
Contributor

ThePixelDeveloper commented Nov 7, 2010

With the feature $schema->renameTable('old', 'new'); you end up losing your data due to how this function works.

Doctrine creates a new table with the old table definitions and then drops the old table. It fails to migrate the data into the new table. There are two solutions to this problem (1st one preferred):

  1. Use the native statement (RENAME TABLE old TO new) Documentation
  2. SELECT the old data into the new table.

Thoughts appreciated.

Here's the function in question in the Schema class:
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Schema.php#L198

There you can tell how the internal Table is simply being renamed (& persisted) while the old is removed because of the subsequent drop command.

It looks like some logic already exists for performing RENAMEs on columns, so we just need to have this apply to TABLE renaming as well. (The code listed here looks to be specific to the ALTER table commands):
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php#L409

Option 1 looks to avoid locking the table, while Option 2 would easily choke a database with thousands of records.

Member

lsmith77 commented Feb 18, 2011

ouch that sounds bad .. could you cook up a patch for this?

Ran into this same issue today. Is there a workaround?

After reviewing this again, Doctrine's able to rename columns because there's a diff from the old to the new. Since table renames seem much more rudimentary, I'd need to dig a bit deeper to figure out how to support this...

Owner

beberlei commented Jun 30, 2012

I removed renameColumn() - Its not really possible to support this on schema diffs, because there is no way to semantically detect renames between two schema instances:

doctrine/dbal@9b0bd88

@beberlei beberlei closed this Jun 30, 2012

5 years later, $schema->renameTable() still loses all data from source table.

Member

stof commented Jul 20, 2015

@catasoft the generated SQL is only migrating the schema. Migrating data is never guaranteed to be done properly by generated queries because this is impossible to guarantee without knowing your data structure (adding fields also requires populating them)

Member

stof commented Jul 20, 2015

and btw, this is exactly why the schema:update command is marked as being unsafe for prod: it does not give any way to tweak the SQL, while migrations do as you can do what you want with the migration after the initial generation (you can even start from scratch without generating any SQL btw)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment