What I said was wrong. I had to look at the source code and what it does is
a bit more complex.
If the SQL used for the original field definition is different than the SQL
that would be used to define the field in the current model:
If sqlite: ignore it
elif the field type is 'reference', 'double' , 'id' or SQLCutomType:
ignore it
else:
create new tmp column
copy data into tmp column
drop original column
add new column
copy data from tmp field
drop tmp column
This logic can be improved but there are historical reasons for it. At some
point we changed the way sql was generated for id, double and reference
fields and we did not want to trigger a migration that could have failed.
On Friday, 6 July 2012 06:49:38 UTC-5, juaneduardo wrote:
>
> Ok. This is a problem only with Mysql? with ProgreSQL too?
>
> Eduardo
>
> El jueves, 5 de julio de 2012 22:40:56 UTC-4, Massimo Di Pierro escribió:
>>
>> It is not like that field level migrations do not work. They work if, for
>> example you change a field type. They do not work it you change an
>> attribute (unique, notnull).
>>
>> On Thursday, 5 July 2012 16:40:42 UTC-5, MichaelF wrote:
>>>
>>> Thanks for that. I didn't realize field-level migrations don't work with
>>> MySQL. I'm no expert on web2py migrations (or even web2py!); is it just
>>> with MySQL that it has these problems?
>>>
>>> I suppose I could do this as an alternative to what you suggested: In
>>> web2py add a new field with unique, then in MySQL UPDATE all records,
>>> setting newField = oldField. I'd have to handle duplicates here, but at
>>> least I wouldn't lost all my data. Then, when satisfied, delete oldField
>>> and maybe rename newField to oldField.
>>>
>>> On Thursday, July 5, 2012 12:26:31 PM UTC-6, Jim S wrote:
>>>>
>>>> I believe that field level migrations do not work with MySQL. I get
>>>> around this by removing the column, saving, run the app to force
>>>> migration,
>>>> and then add the field back the way I want it. I know this causes you to
>>>> lose the data in that column, but I only do this in my test environment
>>>> and
>>>> have migrations turned off in production.
>>>>
>>>> Alternatively, you could update the column def in web2py, change
>>>> manually in mysql and then run with migrate=False, fake_migrate=True to
>>>> get
>>>> things back in sync.
>>>>
>>>> Hope that helps.
>>>>
>>>> -Jim
>>>>
>>>> On Wednesday, July 4, 2012 10:29:10 AM 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.
>>>>>
>>>>>
