Bug #15511 Create or change default value in table editor for a field of type bit fails.
Submitted: 6 Dec 2005 3:52 Modified: 21 Sep 2006 14:22
Reporter: Phil McSharry Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.1.17 OS:Windows (Windows XP SP2)
Assigned to: Mike Lischke CPU Architecture:Any
Tags: Object Editors

[6 Dec 2005 3:52] Phil McSharry
Description:
When the Table Editor is used to 
Create a table with a bit field having a default value OR
Alter the default value of a bit(1) field it generates SQL with the new default value in quotes.
i.e.  set default '1';
This fails in the parser with error 1067

How to repeat:
Right click test Database in QB
Choose "Create New Table"
Create a table containing two columns with the following properties :
ID int not null auto Primary Key
bit1 bit   default 0

Click "Apply Changes"
the generated SQL triggers error 1067

Suggested fix:
Copy the create or alter table statement from the editor into a script window.
Discard the Changes in the Edit dialogue
remove the quotes around the default value
execute the statement.
[6 Dec 2005 4:27] Phil McSharry
The Problem goes beyond the QM issue and the parser.
The SQL which works does not create an effective default value.
I'm using  MySQL 5.0.15-nt and this issue  may be related to bug #13601 (Java interface)

Suggested workaround now is to use TINYINT(1) aka BOOL data type instead.
[6 Dec 2005 10:23] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with QB 1.1.17. It generates the following SQL:

CREATE TABLE `test`.`tbit2` (
  `id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  `bit1` BIt NOT NULL DEFAULT '0',
  PRIMARY KEY(`id`)
)
ENGINE = InnoDB;

and error 1067, as a result.

You may open a separate (Server) issue about incorrect handling of the default value for bit columns, or just add comment to that one you found.
[6 Jun 2006 6:03] Ralf Hauser
unfortunately still happens with 1.1.20

It would be great if the QB would not allow to edit the file with the pop-up editor if the typ is BIT, but to only "toggle" or checkbox ...

... or translate a "1" into the appropriate "true" bit as the command-line

    update table01 set active = 1 

correctly does
[6 Jun 2006 6:30] Ralf Hauser
BTW,this does not happen only for the default value, but also if I try to edit record values!
[21 Jul 2006 19:25] Adam Marzi
This problem also occurs with the TIMESTAMP datatype using CURRENT_TIMESTAMP as the default value.  Use the drop/create new method described above to resolve.
[21 Sep 2006 14:22] Mike Lischke
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html