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)
