Bug #71172 There's no way to get default value as binary from a (var)binary column
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)
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.