We use CodeIgniter and PHP ActiveRecord, a light weight ORM) in many of our applications.  We have always used the TINYINT datatype in MySQL for columns which contain flags.  However, on a recent project I decided to try out the BIT datatype for flags.  Made sense to me as we were only looking to store a single bit of data.

I quickly ran into some problems when I started trying to use ActiveRecord to update a record with a bit flag on it.  Strangely, the flag would update to 1 no matter what value was passed into ActiveRecord.  A bit of digging showed that AR didn’t have any built in support for Bits, which means that they are treated as strings by default.  After a few tweaks, we were able to add support for bits to AR, and treat them like INTS.  To me, this seemed intuitive, however this change didn’t seem to fix the problem.

We use a PDO database connection within our CI app that AR piggybacks off of.  After getting down into the core of AR, it was clear that the problem existed within this function in Connection.php.

Using the PHP PDO debugDumpParams function produced the following output:

Notice that the detected param_type of all parameters is 2, which is PDO::PARAM_STR.  After doing some more digging, I finally figured out the problem.  By default, PDO uses (PDO::ATTR_EMULATE_PREPARES = TRUE) for all prepared statements.  When using emulated prepares and not manually binding query parameters, PDO uses lazy type detection and just marks every param as a string.  This is fine, because MySQL automatically tries to cast all values into the appropriate type.  For whatever reason, however, this doesn’t work with BITs.  Updating a BIT column to “1”, or “0” will always result in the column being set to 1.  So, as a result, trying to use BIT columns with ActiveRecord, PDO, and MySQL will not work.

Possible work arounds:

  1. Try turning off PDO::ATTR_EMULATE_PREPARES, and forcing the DBMS to actually build the prepared statements.  We tried this, and it appears that some of the built in AR queries (like the query to set the connection character encoding) are not compatible with this option.  Some effort would need to be expended to correct the queries.
  2. Alter the AR query function to build out the parameters of the query using either the bindValue or bindParam functions.  Both of these functions allow for specifying the data type of the parameter.
  3. Don’t use the BIT data type with ActiveRecord, PDO and MySQL.

For the moment we are going with option 3, however we would like to revisit options 1 and 2 at some point in the future.