how do I see the contents of 1 bit fields

[expired user #590]'s profile image [expired user #590] posted 17 years ago in General Permalink
I am attempting to bulk load data into an actitime db. (free time tracking).
The user table has 3 bit(1) fields. They do not display
in the data tab and I was wondering if there is a configuration
setting I need to tweak.

Great application, I'm very happy I found it.

I made the mistake of buying 7 licenses
after loving my MySQL Front 2.2. Nothing but bugs.

It all makes sense now....

Any help on the bit(1) fields would be appreciated.
Thanks,
Patrick
ansgar's profile image ansgar posted 17 years ago Permalink
Would be great if you could post the CREATE TABLE statement, so we can reproduce that more easy.
[expired user #590]'s profile image [expired user #590] posted 17 years ago Permalink
Thanks for the reply,

I used the export option to produce the table info.
The db is from http://products.actimind.com/actiTIME/ and
a fellow employee has requested I bulk load 500 users for her.

I loaded a sample of 10 people into this table and also added
them to a user_access_right table, (very simple table), but the
actitime interface isn't exactly the same for them as it is for those
added through their java servlet interface. I'm wondering if bits
are set in the bit(1) fields that I can't see.

Here is a sample sql for 1 of my entries:
INSERT INTO at_user (id,username,username_lower,md5_password, first_name,last_name, email) VALUES ('24','aagarwal','aagarwal','07cc694b9b3fc636710fa08b6922c42b','amit','agarwal','amit_agarwal@mentorg.com');
INSERT INTO user_access_right (user_id,access_right_id) VALUES (24,9);

I included the sql because there is a required field that has a default value
that I'm unable to enter so fortunately it had a default value of 1 or ^A or SOH.

Thanks,
Patrick


# HeidiSQL Dump
#
# --------------------------------------------------------
# Host: removed from info
# Database: actitime
# Server version: 5.0.26-community-log
# Server OS: Win32
# max_allowed_packet: 1047552
# HeidiSQL version: 3.0 RC3 Revision: 111
# --------------------------------------------------------

/*!40100 SET CHARACTER SET latin1;*/


#
# Database structure for database 'actitime'
#

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `actitime` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `actitime`;


#
# Table structure for table 'at_user'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ `at_user` (
`id` int(3) unsigned NOT NULL auto_increment,
`username` varchar(32) NOT NULL default '',
`username_lower` varchar(32) NOT NULL default '',
`md5_password` varchar(32) NOT NULL default '',
`first_name` varchar(32) NOT NULL default '',
`middle_name` varchar(32) default NULL,
`last_name` varchar(32) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`phone` varchar(32) default NULL,
`fax` varchar(32) default NULL,
`mobile` varchar(32) default NULL,
`other_contact` varchar(32) default NULL,
`is_enabled` bit(1) NOT NULL default ' ',
`workday_duration` int(11) default NULL,
`overtime_tracking` bit(1) default NULL,
`overtime_level` int(11) default NULL,
`is_locked` bit(1) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username_lower`),
KEY `is_enabled` (`is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='User';
ansgar's profile image ansgar posted 17 years ago Permalink
Hmmm, seems like you hit a bug in ZeosLib (which is the underlying layer for MySQL). I can't see the bit-fields too. Seems that they are simply excluded to whatever reason, maybe because there is no valid method you can post data to this bit-fields. I have posted that as a new bug:
http://sourceforge.net/tracker/index.php?func=detail&aid=1609520&group_id=164593&atid=832347
ansgar's profile image ansgar posted 17 years ago Permalink
I'm very sorry but I wasn't able to fix that for the RC4 because of too less time. It's definitely an issue which should get fixed for the next release. I started some detective work to find out the cause of the bug, but I only got so far that BIT-fields are now displayed in the data-grids, but the content is NOT fetched NOR stored correctly! Be aware of that if you're using BIT-fields.
[expired user #5221]'s profile image [expired user #5221] posted 13 years ago Permalink
I'm sorry to necro bump this topic, I just wanted to know if this has been fixed yet.

I still can't seem to see or edit the content of bit fields.

Also thanks for the awesome GUI tool~
ansgar's profile image ansgar posted 13 years ago Permalink
Isn't the binary/hex editor called when you start editing a BIT field?
[expired user #5221]'s profile image [expired user #5221] posted 13 years ago Permalink
No, it just starts editing in place. Also the values aren't shown. If I copy and paste it gives a character.

Toggling the "View binary data as text" button doesn't seem to have any effect either.

Using version 5.1.0.3560
[expired user #5221]'s profile image [expired user #5221] posted 13 years ago Permalink
I tried connecting to a different server with another version of MySQL installed (5.0 instead of 5.1) and had the same issue, no bit shown and unable to edit them :<

Also with the table editor it's impossible to set a correct default value for bit columns~
ansgar's profile image ansgar posted 13 years ago Permalink
See issue #766, issue #1163 and issue #1160.
[expired user #5221]'s profile image [expired user #5221] posted 13 years ago Permalink
Oh sorry, I only looked in forum and didn't see the bugtracker!
[expired user #5281]'s profile image [expired user #5281] posted 13 years ago Permalink
Hello,

which of these issues (#766, #1163 and #1160) are tracking the simple display of BIT fields?

I use HeidiSQL a lot, it is a very good tool. But at the moment, I badly need HeidiSQL to at least display BIT columns. My database schema is generated by an external tool (Hibernate), and the MySQL dialect it using the "BIT(1)" type to store boolean values in the database (which is the right way - in earlier versions, TINYINT(1) was used).

In my current database, there are a lot of boolean values, which I can't see in HeidiSQL, all fields are empty in it's data-view.

I'm a developer myself, so I know time is always short, but it would be really helpful, if you may include the diplay of a BIT(1) field as 1 oder 0. Do you think it is possible to implement this for the next "nightly build"?
ansgar's profile image ansgar posted 13 years ago Permalink
Don't know what's coming next but I would not expect these BIT issues to be solved in the near future. To me it looks like most users are using ENUM columns for boolean purposes, while BIT is more rarely used.
[expired user #5221]'s profile image [expired user #5221] posted 13 years ago Permalink
Problem is for those who use MySQL with ODBC Connector, checking a SET or ENUM column data type with ADO returns a char type, while a BIT(1) column is correctly interpreted as boolean :<

But yeah I guess there arn't many in that situation =) I still hope it get fixed sometimes anyway~
ansgar's profile image ansgar posted 13 years ago Permalink
Yes, of course they get fixed. Otherwise theses reports would have been closed in the past. Some issues just take a long time until I am motivated enough to fix themsmileThe oldest issue is already two years old, well.
lemon_juice's profile image lemon_juice posted 13 years ago Permalink

Problem is for those who use MySQL with ODBC Connector, checking a SET or ENUM column data type with ADO returns a char type, while a BIT(1) column is correctly interpreted as boolean :<


I wouldn't agree with the statement that interpreting BIT(1) as boolean is correct. The BIT field, per mysql documentation, is not intended as a boolean substitute but is a number in the form of a set of bits. So in a way it is similar to the binary data type. It's just coincidence that BIT(1) fits nicely with the boolean type purpose because it allows exactly 2 values and no more, like no other datatype in mysql.

The confusion might come from the fact that older versions of mysql silently converted BIT(1) fields to TINYINT(1) giving the impression of being the right one for the boolean. However, now mysql understands a BOOLEAN specification, which now defaults to TINYINT(1) and which will be implemented as a true boolean type in the future.

That is not to say this shouldn't be fixed since in many cases we cannot control what datatype to use in an existing system. Still, there seems to be a lot confusion around the BIT field:

- phpmyadmin displays it as a binary number (e.g. 11)
- mysql workbench displays it as a decimal number (e.g. 3)
- sqlyog displayes it as an unreadable binary string (like Heidi now)
[expired user #5221]'s profile image [expired user #5221] posted 13 years ago Permalink
Yeah I meant correctly as of ODBC connector specifications, myself I would have gladly continued using tinyint(1) columns ;P

I have no idea on why the connector devs decided at a certain point to map boolan type to bit and remove it from tinyint. At least the .net connector has the "Treat Tiny As Boolean" option >_>

Please login to leave a reply, or register at first.