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
