"boolean" type in MySQL: CHAR(1) vs TINYINT(1)

[Kevin Ivarsen]

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

[simon]

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.

[Osman Masood]

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.

[Massimo Di Pierro]

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?

[MichaelF]

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?

[MichaelF]

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.

[Massimo Di Pierro]

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:

[MichaelF]

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

[Massimo Di Pierro]

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

[MichaelF]

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", 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=...).

[Massimo Di Pierro]

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

[MichaelF]

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.

[MichaelF]

I have come across one bug with this. If I add a record using the admin interface, I check the 'Is_home_team' checkbox (Is_home_team is defined as a boolean, of course), yet the record has 0 for that field. Given that, as you might expect then, all records have a 0 for that field.

??

[MichaelF]

Converting to 2.x fixed the problems.

[MichaelF]

I spoke too soon about this fixing the problem. It seems that adding/updating a record with such a field using the admin interface, and using a smartgrid, doesn't do it.

I create this table:

db.define_table('Test_bool',

                Field('test_bool', 'boolean'))

I also have the following at the start of my db.py model file:

db._adapter.types = copy.copy(db._adapter.types)

db._adapter.types['boolean']='TINYINT(1)'

Once web2py creates the table I confirm that MySQL has ccreated the field as TINYINT(1).

I go into the admin interface and insert a record, checking the test_bool checkbox. The INSERTed record has a 0 for that field. I try it again, same result. I then UPDATE one of those records, checking the test_bool checkbox, and the field remains at 0.

I then create a simple smartgrid:

def test_bool():

   grid = SQLFORM.smartgrid(

             db.Test_bool,

             deletable = True, editable = True, create = True

          )

   return locals()

I edit one of the records, checking the box, yet it doesn't 'take'.

Now, if I go in manually ans set the field to 1 (using MySQL Workbench, outside the web2py environment), then go to the grid, I see that the box is checked. If I uncheck it, that 'takes'.

??

Thanks.

[Massimo Di Pierro]

I understand the problem. You can chance the DB type (and you did) but web2py still tries to put an 'F' or a 'T' in there.

I modified trunk so that this can be achieved:

import copy

db =DAL()

db._adapter.types = copy.copy(db._adapter.types)

db._adapter.types['boolean']='TINYINT(1)'

db._adapter.TRUE = 1

db._adapter.FALSE = 0

db.define_table('test',Field('b', 'boolean'))

db.test.insert(b=True)

db.test.insert(b=False)

rows = db(db.test).select()

print db.executesql(db(db.test)._select())

this should work fine.