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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.