Bug #71483 Bit field is stored incorrectly. Stored data deformed.
Submitted: 27 Jan 2014 5:28 Modified: 27 Jan 2014 14:16
Reporter: Simon Litt Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.69, 5.1.70, 5.5.32 OS:Linux
Assigned to: CPU Architecture:Any
Tags: BINARY, Bit field

[27 Jan 2014 5:28] Simon Litt
Description:
Values ​​of binary fields are not stored properly.

E.g.
0 becomes b'00110000 ' (field length 8), 
b'11111111' becomes b'00110010 ' (field length 8),
4 becomes b'0011010000000000000000000000000000000000000000000000000000000000' (field length 64),
etc. 

This behavior is repeated as when working through phpMyAdmin, mysql client and queries from the php scripts.

I can always repeat it on my local computer(5.1.69, 5.5.32) and on hoster database (5.1.70) of your website

How to repeat:
Crate database and run following script:

create_tables.sql
--------------------------------------------------------------
DROP TABLE IF EXISTS `test`,`test_def`, `test_long` ;

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL,
  `f` bit(8) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `test_def` (
  `id` int(11) NOT NULL,
  `f` bit(8) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `test_long` (
  `id` int(11) NOT NULL,
  `f` bit(64) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;

INSERT INTO `test` (`id`, `f`) VALUES
(1,  b'00000000'),
(2,  b'00000001'),
(3,  b'00000011'),
(5,  b'00000100'),
(6,  b'00001010'),
(7,  b'00010000'),
(8,  b'00100000'),
(9,  b'01000000'),
(10, b'11111111'),
(11, b'10101010'),
(12, b'01010101');

INSERT INTO `test_def` (`id`, `f`) VALUES
(1,  b'00000000'),
(2,  b'00000001'),
(3,  b'111'),
(4,  b'11111111');
INSERT INTO `test_def` (`id`) VALUES(5);

INSERT INTO `test_long` (`id`, `f`) VALUES
(1,  b'1'),
(2,  b'1111'),
(3,  b'01010101'),
(4,  b'10101010'),
(5,  b'11111111'),
(6,  b'111100001111'),
(7,  b'1111111111111111111111111111111111111111111111111111111111111111'),
(8,  2),
(9,  4);
INSERT INTO `test_long` (`id`) VALUES(10);
---------------------------------------------------

Then run the SELECT query or make a dump and you will see:

dump.sql
--------------------------------------------------------------
INSERT INTO `test` (`id`, `f`) VALUES
(1, b'00110000'),
(2, b'00110001'),
(3, b'00110011'),
(5, b'00110100'),
(6, b'00110001'),
(7, b'00110001'),
(8, b'00110011'),
(9, b'00110110'),
(10, b'00110010'),
(11, b'00110001'),
(12, b'00111000');

INSERT INTO `test_def` (`id`, `f`) VALUES
(1, b'00110000'),
(2, b'00110001'),
(3, b'00110111'),
(4, b'00110010'),
(5, b'00110000');

INSERT INTO `test_long` (`id`, `f`) VALUES
(1, b'0011000100000000000000000000000000000000000000000000000000000000'),
(2, b'0011000100110101000000000000000000000000000000000000000000000000'),
(3, b'0011100000110101000000000000000000000000000000000000000000000000'),
(4, b'0011000100110111001100000000000000000000000000000000000000000000'),
(5, b'0011001000110101001101010000000000000000000000000000000000000000'),
(6, b'0011001100111000001101010011010100000000000000000000000000000000'),
(7, b'0011000100111000001101000011010000110110001101110011010000110100'),
(8, b'0011001000000000000000000000000000000000000000000000000000000000'),
(9, b'0011010000000000000000000000000000000000000000000000000000000000'),
(10, b'0011000000000000000000000000000000000000000000000000000000000000');
[27 Jan 2014 9:16] Peter Laursen
Does this happen also when dumping with 'mysqldump'? I doubt? It is probably the client(s) you use that do(es) not handle BIT types correctly.

Refer http://dev.mysql.com/doc/refman/5.5/en/bit-field-literals.html "To display them in printable form, add 0 or use a conversion function such as BIN()"

Peter
(not a MySQL/Oracle person)
[27 Jan 2014 14:16] Simon Litt
Thanks, Peter!

I try to create a table  through the mysql client and through phpMyAdmin and from scripts. But  I try to view tables and create a dump using phpMyAdmin.

This phpMyAdmin bug.