web2py does not know how to migrate when you add a unique=True because
existing data may break the contraint. You have some option:
1) if there is no data in db, comment the line in model (it will be
dropped), then add it again with the unique constraint
2) if you have data, add the unique constraint to model, set migrate=True,
fake_migrate=True then manually do the alter table and make it unique
3) if you upgrade to web2py trank it will silently ignore the alter table
(bad) but add a constraint at the web2py level.
On Thursday, 5 July 2012 09:41:26 UTC-5, MichaelF wrote:
>
> I have a working app using web2py `(1, 99, 7, datetime.datetime(2012, 3,
> 4, 22, 12, 8), 'stable'); Python 2.5.4: C:\Program Files
> (x86)\web2py\web2py_no_console.exe`) and MySQL 5.5. If I change one field
> to add `unique=True` the web2py migration fails with this error: `"<type
> 'exceptions.KeyError'> 'institution_name'"` where institution_name is the
> name of the field in question.
>
> I've recreated the problem using a single-table application in web2py
> using MySQL. Here's the model code:
>
> To start off (field not defined as unique):
>
> ... (usual model/db.py boilerplate)
> db = DAL('mysql://w2ptest:abcde...@mysql5.server.com:3307/abc_web2py')
> ...
> db.define_table('Institution',
> Field('Institution_name', 'string', length=60,
> required=True),
> format='%(Institution_name)s')
>
> I go to the appadmin page and everything looks fine. Then, making
> Institution_name unique:
>
> db.define_table('Institution',
> Field('Institution_name', 'string', length=60,
> required=True,
> unique=True),
> format='%(Institution_name)s')
>
> I then refresh the appadmin page and get a ticket with the error. The
> error line in the traceback is the last line of the modified statement
> above. And, to make things worse, I can go in and undo the `unique=True`,
> but web2py doesn't respond if I refresh the appadmin page...or any page
> served by that web server, even in other applications! The cpu is
> <b>not</b> pinned while in this state. I have to recreate the app and
> database to clear the problem. (Well, I think I have to go that far. Just
> restarting web2py doesn't clear it in the full case, but does clear it in
> my little one-table test case.) I try to stop the server
> (web2py_no_console.exe), but it fails to respond.
>
> Instead of the `unique=True` I can `db.executesql('ALTER TABLE
> abc_web2py.Institution ADD UNIQUE INDEX UX_Iname (Institution_name) ;');`
> but I'd rather not, particularly as then I have to `try` that statement
> because MySQL has no `...IF NOT EXIST...` capability for index creation.
>
> Also, if I start off the model with `unique=True` in the first place,
> everything is fine, and MySQL even shows the unique index as created.
>
