Bug #71172 There's no way to get default value as binary from a (var)binary column
Submitted: 18 Dec 2013 11:38 Modified: 26 Dec 2013 18:06
Reporter: Maxim Makhotkin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.6.14, 5.6.16, 5.5.35, 5.1.74. 5.0.97 OS:Windows (7 x64)
Assigned to: CPU Architecture:Any
Tags: DEFAULT, varbinary

[18 Dec 2013 11:38] Maxim Makhotkin
Description:
Unable to get varbinary columns' default values from information schema, for default values that contain non-printable characters.

How to repeat:
mysql> use test;
Database changed

mysql> CREATE TABLE `account` ( 
  `CompanyID` int(11) NOT NULL DEFAULT '0',
  `AccessMask1` binary(32) NOT NULL DEFAULT 0x00,
  `AccessMask2` varbinary(32) NOT NULL DEFAULT 0xAA,
  `AccessMask3` varbinary(32) NOT NULL DEFAULT 0xFF
);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW FULL COLUMNS FROM account;
+-------------+---------------+-----------+------+-----+----------------------------------+-------+---------------------------------+---------+
| Field       | Type          | Collation | Null | Key | Default                          | Extra | Privileges                      | Comment |
+-------------+---------------+-----------+------+-----+----------------------------------+-------+---------------------------------+---------+
| CompanyID   | int(11)       | NULL      | NO   |     | 0                                |       | select,insert,update,references |         |
| AccessMask1 | binary(32)    | NULL      | NO   |     |                                  |       | select,insert,update,references |         |
| AccessMask2 | varbinary(32) | NULL      | NO   |     |                                  |       | select,insert,update,references |         |
| AccessMask3 | varbinary(32) | NULL      | NO   |     |                                  |       | select,insert,update,references |         |
+-------------+---------------+-----------+------+-----+----------------------------------+-------+---------------------------------+---------+
4 rows in set, 2 warnings (0.01 sec)

mysql> SELECT HEX(c.column_default), c.column_default, c.column_name FROM information_schema.columns c WHERE table_schema = DATABASE() AND table_name = 'account';
+------------------------------------------------------------------+----------------------------------+-------------+
| HEX(c.column_default)                                            | column_default                   | column_name |
+------------------------------------------------------------------+----------------------------------+-------------+
| 30                                                               | 0                                | CompanyID   |
| 0000000000000000000000000000000000000000000000000000000000000000 |                                  | AccessMask1 |
|                                                                  |                                  | AccessMask2 |
|                                                                  |                                  | AccessMask3 |
+------------------------------------------------------------------+----------------------------------+-------------+
4 rows in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------+
| Level   | Code | Message                                                             |
+---------+------+---------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAA' for column 'COLUMN_DEFAULT' at row 1 |
| Warning | 1366 | Incorrect string value: '\xFF' for column 'COLUMN_DEFAULT' at row 1 |
+---------+------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table account;
+---------+-------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                |
+---------+-------------------------------------------------------------------------------------------------------------+
| account | CREATE TABLE `account` (
  `CompanyID` int(11) NOT NULL DEFAULT '0',
  `AccessMask1` binary(32) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `AccessMask2` varbinary(32) NOT NULL DEFAULT '?',
  `AccessMask3` varbinary(32) NOT NULL DEFAULT '?'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[18 Dec 2013 11:48] Maxim Makhotkin
Reproduced the issue on Amazon RDS (Linux x64, mysql community server version 5.6.13)
[26 Dec 2013 18:06] Sveta Smirnova
Thank you for the report.

Verified as described.