Well, that's unfortunate. I've migrated this semi-manually; I had only four 'boolean' fields.
Other than that, the suggested fix ( db._adapter.types['boolean']='TINYINT(1)' ) seems to work. On Monday, September 17, 2012 8:42:24 PM UTC-6, Massimo Di Pierro wrote: > > I cannot reproduce this error with your code in 2.0.9 and the lines in > your traceback do not correspond to the source code I have. I think you may > be using an older dal.py > > On Monday, 17 September 2012 16:43:30 UTC-5, MichaelF wrote: >> >> Yes; here it is: >> >> 1. >> 2. >> 3. >> 4. >> 5. >> 6. >> 7. >> 8. >> 9. >> >> Traceback (most recent call last): >> File "gluon/restricted.py", line 205, in restricted >> File "C:/Program Files >> (x86)/web2py/applications/NCAA_schedule/models/db.py" >> <http://127.0.0.1:8000/admin/default/edit/NCAA_schedule/models/db.py>, line >> 165, in <module> >> File "gluon/dal.py", line 6320, in define_table >> File "gluon/dal.py", line 742, in create_table >> File "gluon/dal.py", line 797, in migrate_table >> File "gluon/dal.py", line 6714, in __getitem__ >> KeyError: 'length_is_yards' >> >> The table definition follows: >> >> db.define_table('Pool', >> Field('Pool_name', 'string', required=True, unique=True), >> Field('Address1', 'string', length=60), >> Field('Address2', 'string', length=60), >> Field('City', 'string', length=60), >> Field('State', 'string', length=2), >> Field('Zip', 'string', length=15), >> Field('Nr_lanes', 'integer', required=True), >> Field('Length', 'integer', required=True), >> Field('Length_is_yards', 'boolean', >> required=True,default=True), >> Field('Has_moveable_bulkhead', 'boolean', required=True, >> default=False), >> format='%(Pool_name)s %(Nr_lanes)s') >> >> Line 165 is the last line of the statement (format=...). >> >> On Monday, September 17, 2012 3:15:08 PM UTC-6, Massimo Di Pierro wrote: >>> >>> Do you have a traceback with more information? >>> >>> On Monday, 17 September 2012 14:23:56 UTC-5, MichaelF wrote: >>>> >>>> Thanks. However, I refer to that field with upper case in all places. >>>> Can you tell me where the lower case 'pending' comes from? The field name >>>> has always been defined as upper case, and the app has been working up >>>> until I made that latest change. So I went into the db and changed the >>>> field name to start with lower case, then changed the model file to make >>>> it >>>> lower-case 'pending'. That worked, but now the next boolean field in the >>>> db.py file has an upper-case/lower-case problem. The field is >>>> "Length_is_yards" in both the db.py file and the db, and has been that way >>>> for weeks, and we've been through several db migrations for the past >>>> several weeks (not sure about on those particular tables, though). Now I >>>> get the KeyError as shown above, but this time it's for field >>>> 'length_is_yards'. It looks to me that web2py is assuming it's lower case. >>>> >>>> One of my migrations last week was the "fake_migrate_all=True" type; >>>> don't know if that's relevant. >>>> >>>> Also, in the .database file the field name is Length_is_yards (leading >>>> "L" is capital), as is the field name in the MySQL db. >>>> >>>> I'm confused. >>>> >>>> Michael >>>> >>>> On Monday, September 17, 2012 12:51:34 PM UTC-6, Massimo Di Pierro >>>> wrote: >>>>> >>>>> Field('Pending' <<< upper case >>>>> ... >>>>> <type 'exceptions.KeyError'> 'pending' <<< lower case >>>>> >>>>> >>>>> >>>>> On Monday, 17 September 2012 11:37:13 UTC-5, MichaelF wrote: >>>>>> >>>>>> I did a simple import of 'copy' and that got me by that first >>>>>> problem. But now I have the following problem: >>>>>> >>>>>> db.define_table('Person_certification', >>>>>> Field('Person', db.Person), >>>>>> ... >>>>>> Field('Pending', 'boolean', default = False), >>>>>> ... >>>>>> >>>>>> I get the following error on the line that defines field 'Pending' >>>>>> (and this is the first 'boolean' type in the file): >>>>>> <type 'exceptions.KeyError'> 'pending'I have not changed the >>>>>> underlying MySQL db yet; all the booleans are still char(1). Do I need >>>>>> to >>>>>> change them first to Tinyint(1)? I tried that; same error. >>>>>> >>>>>> Thanks. >>>>>> >>>>>> On Monday, September 17, 2012 9:21:37 AM UTC-6, MichaelF wrote: >>>>>>> >>>>>>> 1. What will I need to import to get it to recognize 'copy'? I run >>>>>>> the suggested code and get told that 'copy' does not exist. (I'm >>>>>>> running >>>>>>> 2.5; what do I conditionally import?) >>>>>>> >>>>>>> 2. Are we doing a copy because all the adapters share the same >>>>>>> 'types' object? >>>>>>> >>>>>>> On Tuesday, August 7, 2012 3:48:35 PM UTC-6, Massimo Di Pierro wrote: >>>>>>>> >>>>>>>> On can always do: >>>>>>>> >>>>>>>> db=DAL('mssql://...') >>>>>>>> db._adapter.types = copy.copy(db._adapter.types) >>>>>>>> db._adapter.types['boolean']='TINYINT(1)' >>>>>>>> >>>>>>>> It should work. Can you please check it? >>>>>>>> >>>>>>>> On Tuesday, 7 August 2012 11:56:59 UTC-5, Osman Masood wrote: >>>>>>>>> >>>>>>>>> However, web2py maintains the promise of backwards compatibility. >>>>>>>>> One way is to have a 'tinyint_boolean' datatype for those who want to >>>>>>>>> use >>>>>>>>> tinyints as booleans. But that looks kind of messy and inelegant. >>>>>>>>> >>>>>>>>> An alternative is this: We could add a migration script to >>>>>>>>> /scripts to convert all boolean data types from CHAR(1) to >>>>>>>>> TINYINT(1), and >>>>>>>>> from 'T' to 1 and 'F' to 0. Also, when a table model is called in >>>>>>>>> define_table(), it would check whether its boolean data types are >>>>>>>>> CHAR or >>>>>>>>> INT, and save the result somewhere (so it wouldn't have to keep >>>>>>>>> checking.) >>>>>>>>> If the server is restarted, it would once again perform this check. >>>>>>>>> So, a >>>>>>>>> user would run the migration script and simply restart the server. >>>>>>>>> >>>>>>>>> On Thursday, July 12, 2012 9:18:33 PM UTC+8, simon wrote: >>>>>>>>>> >>>>>>>>>> I have just come across this exact same issue. >>>>>>>>>> >>>>>>>>>> The web2py adapter converts boolean to char(1) but in MySQL the >>>>>>>>>> specification is that boolean is stored as tinyint with 0 and 1. So >>>>>>>>>> web2py >>>>>>>>>> adapter is incorrect. Not changing it perpetuates the mistake. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Sunday, 6 March 2011 05:14:49 UTC, Kevin Ivarsen wrote: >>>>>>>>>>> >>>>>>>>>>> I'm connecting to a legacy MySQL database (migrate=False) with a >>>>>>>>>>> lot >>>>>>>>>>> of fields declared BOOLEAN, and noticed that attempts to modify >>>>>>>>>>> these >>>>>>>>>>> fields with the DAL failed. The DAL issues a query like this: >>>>>>>>>>> >>>>>>>>>>> UPDATE sometable SET someflag='T' WHERE ... >>>>>>>>>>> >>>>>>>>>>> but this gets rejected by MySQL. >>>>>>>>>>> >>>>>>>>>>> Reading through dal.py, I see that the "boolean" type maps to >>>>>>>>>>> CHAR(1) >>>>>>>>>>> in MySQLAdapter, and represent() converts to "T" and "F" values. >>>>>>>>>>> However, the BOOLEAN type is a synonym for TINYINT(1) in MySQL, >>>>>>>>>>> with >>>>>>>>>>> values 0 or 1, according to: >>>>>>>>>>> >>>>>>>>>>> http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html >>>>>>>>>>> >>>>>>>>>>> I can trivially change this behavior in dal.py for my purposes, >>>>>>>>>>> but it >>>>>>>>>>> would be interested to try to incorporate this into the main >>>>>>>>>>> web2py >>>>>>>>>>> distribution. Unfortunately, the trivial change will break >>>>>>>>>>> backwards >>>>>>>>>>> compatibility for people who are already depending on the >>>>>>>>>>> current >>>>>>>>>>> behavior. Any thoughts on how this could be done in a backwards- >>>>>>>>>>> compatible way, or is it too much of an edge case to worry >>>>>>>>>>> about? >>>>>>>>>>> >>>>>>>>>>> Cheers, >>>>>>>>>>> Kevin >>>>>>>>>> >>>>>>>>>> --
