MySQL always returning BIT values as blank

Question

From my create table script, I've defined the hasMultipleColors field as a BIT:

hasMultipleColors BIT NOT NULL,

When running an INSERT, there are no warnings thrown for this or the other BIT fields, but selecting the rows shows that all BIT values are blank.

Manually trying to UPDATE these records from the command line gives odd effect - shows that the record was match and changed (if appropriate), but still always shows blank.

Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

mysql> update pumps set hasMultipleColors = 1 where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select hasMultipleColors from pumps where id = 1;
+-------------------+
| hasMultipleColors |
+-------------------+
|                  |
+-------------------+
1 row in set (0.00 sec)

mysql> update pumps set hasMultipleColors = b'0' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select hasMultipleColors from pumps where id = 1;
+-------------------+
| hasMultipleColors |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

Any thoughts?

Answer 1

You need to cast the bit field to an integer.

mysql> select hasMultipleColors+0 from pumps where id = 1;

This is because of a bug, see: http://bugs.mysql.com/bug.php?id=43670. The status says: Won't fix.