Bug- Alter Table boolean fields

[Harry711]

Wonderful Product – I have found a bug when using Interactive Alter table. If you have a field tinyint(1) and you change it to type Boolean, the length field is locked and you cannot remove the length. This causes syntax error upon save. Please change code to clear length field if type is changed to Boolean. Work Around is to change type to INT , remove length, then change back to Boolean.

[Harry711]

I forgot to add version to previous post , I'm using ver 4.03

[Ritesh]

Just press Del when the focus is in the len column.

[Ritesh]

SQLyog v4.05 BETA 3 will automatically delete the length value for datatypes not supporting length.

[seanhogge]

I did a search, and only found two posts regarding this problem, so forgive me if this is not the best place to mention this.

I'm using v4.05, and I am adding a column to a table. For the life of me I cannot make it add a bool type column. It converts it to a tinyint(1). Is there something I'm missing? I wouldn't put it past me to be doing something obviously wrong (it's late, I've been working for a while).

Thanks for your assistance and for what is, overall, a brilliant product.

[Ritesh]

In MySQL, tinyint(1) is a synonym for BOOL.

Check http://dev.mysql.com/doc/mysql/en/numeric-types.html for more details.

[seanhogge]

Alright. That's embarrassing. I just assumed – well, you know the rest.

Thanks for replying so quickly. Good morning!

[peterlaursen]

Wrong Ritesh!

See

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

Tinyint is a 1-byte integer, ie. it ranges from -128 to 127

Tinyint(1) is wide used a a replacement for a boolean and the ability to set it for instance (-1) can be very usefull. But it IS not a boolean!

did you confuse it with a “bit” type ?

“As of MySQL 5.0.3, a BIT data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets BIT as TINYINT(1).) In MySQL 5.0.3, BIT is supported only for MyISAM. MySQL 5.0.5 extends BIT support to MEMORY, InnoDB, and BDB.”

[seanhogge]

I think Ritesh was correct as far as MySQL's changing of BOOL to TINYINT(1) based on the following link:

http://dev.mysql.com/doc/mysql/en/other-ve…lumn-types.html

I agree that is is definitely not the same data type in a classical sense, though. As long as it's easy to store what is essentially a 1 or 0 (true/false) value it suits my purposes. I suppose it might cause conflicts in code that doesn't use stored procedures (such as they are in MySQL 5).

[peterlaursen]

Ritesh was not right …

.. but as I wrote it is commonly used to use a tinyint as a substitute for a boolean. With a tinyint you can for instance use values -1, 0 and 1, and that gives the opportunity to let -1 have a special meaning.

One appplication that makes use of this is the popular forum software phpbb2. With the config-table of this application 1 means true, 0 means false and -1 means “use defaults”. I believe that this also is a “workaround” that is used by this software so that it will run with the same code on more database-server systems.

But a tinyint(4) is NOT a boolean. Value could be “-127” ….

[seanhogge]

I see now. I didn't think he was saying that a TINYINT(1) is precisely the same as BOOL, only that their function was similar. It was early in the morning, maybe it was pre-coffee.

[peterlaursen]

no bad feelings …

the point is that a tinyint works fine and seamlessly as a boolean, so if you use like that you just don't think about it after a while … 😀

[seanhogge]

I've been limited on database choices since I began doing this stuff. I'm only now coming into my own hardware and software choices, so these minor details are excellent to debate.

[Ritesh]

Quote:

As of MySQL 5.0.3, a BIT data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets BIT as TINYINT(1)

Probably he was using MySQL version < 5.0.3 😀

[Author]

Posts