Microsoft SQL Server Migrate Trying to Create New Table instead of Editing Old One

[PN]

Version: Using web2py 2.6.4

Steps:

====

1. I used web2py DAL to create a table in ms sql. This ran in production for a while.

2. I downloaded the web2py app to my development machine, and added a column to the table in DAL.

3. When I try to go to the table in web2py admin (database administration) with migrate=True, I get an error message "<class 'pyodbc.ProgrammingError'> ('42S01', "[42S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'BIG_Mapping' in the database. (2714) (SQLExecDirectW)")

4. The error message shows that web2py is issuing a CREATE command, not the ALTER command (in the error log)

Any suggestions on further troubleshooting? I can alter the table manually but wanted to test migrations through DAL so I understand them for future reference.

[Niphlod]

probably your .table files weren't in sync with your model. web2py issues a CREATE statement only if the corresponding .table file is not found into the databases/ folder (implicating that that table doesn't exist on the backend)

[PN]

So, in theory, will the following process fix the issue?

1. Start web2py with old DAL definitions and migrate = True. This should create the .table files as the old structure

2. Then change the DAL to reflect the new database structure, migrate still = True

3. Restart web2py and my tables should get migrated?

[PN]

For future reference, verifying that this can be fixed using the fake_migrate feature (http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Fixing-broken-migrations)

Summary of steps:

1. Set your model in define_table to the old model that matches existing database

2. Set fake_migrate to True in define_table(..., migrate=True, fake_migrate=True)

3. Run your app - this will create the migrate files

4. Remove the fake_migrate argument from define_table

5. Run your app again, this will do a migrate with an ALTER instead of a CREATE