Mysql Bit Fields Displayed In Sqlyog

[The Mask]

I have a table containing a bit field but when I do a SELECT * FROM the table, the bit field is displayed as ''. Anybody know why it doesn't display as '1' or '0' ? BTW, the row was definitely entered specifying '1' or '0' for the bit field. I'm using the latest beta release of mySQL.

[Ritesh]

Can you post screenshots of SQLyog?

Is it possible to send sample data so that we can reproduce the issue at our end?

[The Mask]

Ritesh wrote on Sep 20 2005, 03:32 PM:

Can you post screenshots of SQLyog?

Is it possible to send sample data so that we can reproduce the issue at our end?

[post=”7233″]<{POST_SNAPBACK}>[/post]

I have attached a screenshot showing sample code and the result.

Look forward to hearing from you. Thanks

[Ritesh]

Very strange. It works OK at my side. I am attaching a screenshot to show the effect.

What does select version() return?

[The Mask]

Ritesh wrote on Sep 20 2005, 04:10 PM:

Very strange. It works OK at my side. I am attaching a screenshot to show the effect.

What does select version() return?

[post=”7235″]<{POST_SNAPBACK}>[/post]

select version() returns 5.0.11-beta-nt

[Ritesh]

Hmmm… Might be an issue with MySQL 5.0.x. I will try again later with 5.0.x and post my results.

[Ritesh]

Very very interesting!

I am able to reproduce the error with version MySQL 5.0.11. It seems that there is indeed some changes in the way MySQL 5.x handles bit values.

I will do more research tomorrow and let you know the results.

[The Mask]

Ritesh wrote on Sep 20 2005, 06:22 PM:

Very very interesting!

I am able to reproduce the error with version MySQL 5.0.11. It seems that there is indeed some changes in the way MySQL 5.x handles bit values.

I will do more research tomorrow and let you know the results.

[post=”7238″]<{POST_SNAPBACK}>[/post]

Thanks Ritesh, look forward to your findings.

[peterlaursen]

Searching the MySQL docs:

http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html

BIT[(M)]

A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, and BDB. Before 5.0.3, BIT is a synonym for TINYINT(1).

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

BOOL , BOOLEAN

These are synonyms for TINYINT(1). The BOOLEAN synonym was added in MySQL 4.1.0. A value of zero is considered false. Non-zero values are considered true.

In the future, full boolean type handling will be introduced in accordance with

standard SQL.

Changenotes of MySQL 5.0.3 say:

“BIT in column definitions now is a distinct data type; it no longer is treated as a synonym for TINYINT(1).”

http://dev.mysql.com/doc/mysql/en/news-5-0-3.html

SQLyog issues this SQL:

select * from `test`.`mytable` limit 0, 50;

RESULTS as shown in pic nr0.

compare the result of

select distinct * from `test`.`mytable` limit 0, 50;

RESULTS as shown in pic nr1.

With the table definition om pic. nr2

Server changes to as shown in pic nr3

… all testet with MySQL 5.0.12

[peterlaursen]

Did the same with MySQL 4.1.14

Here a BIT and a BOOL are both transformed to a TINYINT(1) by the server.

pic nr4 shows “create table”

pic nr5 shows how it displays in “alter table” when reopened

[peterlaursen]

@The Mask

Quoting:

Quote:

BIT: A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

With this meaning of the BIT type are you sure then that really is what you want and not a BOOL or TINYINT(1) ? If you want a behaviour as with previous MySQL versions you should in my opinion change column-definition to TINYINT(1).

As of now a BOOL and a TINYINT(1) are identical, but won't continue to be so, since MySQL write

Quote:

In the future, full boolean type handling will be introduced in accordance with standard SQL.

… hmmm … this must be for the FAQ when everything is sorted out!

[peterlaursen]

A little more research.

A BIT type as of 5.03++ simply can't be dispalyed in text – because it is purely binary and has no charset and is not a numerical either! In this respect it is similar to a BLOB (no matter that I know perfectly well that a BLOB is often used for character data. Up to MySQL 4.0 it does not really make much difference either. But from MySQL 4.1++ people should stop doing that and use TEXT instead (just my side-remark). It becomes increasingly important to distinguish between data types that have a charset representation and those that have not!)

Se attached pic. If it is defined as BIT(9) (9 or anything greater than 8) it displays like this in RESULT-pane. If it is defined as BIT(8) (8 or less than 8) it displays as a square (nonprintable ANSI-value).

The solution as far as SQLyog goes is in my opinion to enable the BLOB-viewer for BIT-types – and to implement the request to have an option to display binary content in binary/hex view (and to let it recognize BITs LTE 8 as well !)

Using BIT as a boolean type simply is 'wrong' as of MySQL 5.03++! Or rather a BIT(1) of course can of course be used for that. Actually a BIT(8) can be used as an 'array of 8 booleans'.

But for backward compabitility use TINYINT(1), and for the future and for STANDARD SQL compability use BOOL.

[Ritesh]

I guess that solves the issue.

I have added this issue in the TO-DO list of SQLyog. We will implement it in v4.4 along with support for CURRENT_TIMESTAMP etc.

[peterlaursen]

http://www.webyog.com/faq/29_52_en.html

[The Mask]

Thanks guys, for the info and the prompt responses.

[Author]

Posts