Bug #35940 mysqldump on BIT type as String

• Submitted: 9 Apr 2008 15:14
• Modified: 9 Apr 2008 15:35
• Reporter: Tamás Horváth
• Status: Can't repeat
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 14.12 Distrib 5.0.51a
• OS: Windows (XP SP2)
• CPU Architecture: Any

[9 Apr 2008 15:14] Tamás Horváth

Description:
If you dump a table with a BIT typed column then the mysqldump dups it as string
'\0' or '.' (where . is character with hexa code 0x01)

And is you import it (source ...sql) then all the rows will contain 0 if the column type were BIT.

How to repeat:
use test
create table xx (BIT x);
insert into x values(1);

mysqldump test xx

[9 Apr 2008 15:35] MySQL Verification Team

Thank you for the bug report. I could not repeat with current source Windows
server:

c:\dbs>5.0\bin\mysql -uroot xbit
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.0.60-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table xx (x BIT);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into xx values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select x+0 from xx;
+------+
| x+0  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> exit
Bye

c:\dbs>5.0\bin\mysqldump -uroot xbit > xbit.sql

c:\dbs>5.0\bin\mysql -uroot xbit
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.0.60-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delete from xx;
Query OK, 1 row affected (0.00 sec)

mysql> select x+0 from xx;
Empty set (0.00 sec)

mysql> exit
Bye

c:\dbs>5.0\bin\mysql -uroot xbit < xbit.sql

c:\dbs>5.0\bin\mysql -uroot xbit
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.0.60-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select x+0 from xx;
+------+
| x+0  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

[25 Jun 2010 8:05] Pesho Goshev

This is old, but it just happened to me.
I use 
Server version: 5.1.41-3ubuntu12.3 (Ubuntu)
on latest Ubuntu.

I repeated the steps from Miguel Solorzano and I can see the problem.
In the .sql dump file you can observe that the values that were '1' are now some square symbol - '
'.

[8 Nov 2011 21:52] Joseph Bihnam

this is not about Windows XP. I have this on Windows 7 - 64bit. This is still an issue with the latest MySQL server 5.5.17 and using Workbench 5.2.35 CE

[8 Nov 2011 21:58] Joseph Bihnam

Here is how to replicate this issue with current mysql based on the version(s) i have provided in my previous comment:

CREATE TABLE `client` (
  `ID` int(11) NOT NULL DEFAULT '0',
  `NAME` varchar(60) NOT NULL DEFAULT '',
  `MYBIT` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `client` VALUES (1,'My Name',b'1')

Now, do mysqldump on the table into an sql file and when you are done, open it and view the output

INSERT INTO `client` VALUES (1,'My Name','
');