Description:
When `--skip-binary-as-hex` option is set to mysql client, it changes the output of `select * from t`, on the other hand it does not change the output of `show full fields`, `desc` and `information_schema.column.column_default` output.
- The related issue at Ruby on Rails framework
https://github.com/rails/rails/issues/45832
- The same question at forum is not answered.
https://forums.mysql.com/read.php?24,685577,685577
- Related bug reported
https://bugs.mysql.com/bug.php?id=99283
How to repeat:
-- Prepare test database and table
create database test;
use test;
create table t (foo varbinary(1024) default 'livebeef');
insert into t values();
-- Retrieve varbinary data with --skip-binary-as-hex enabled
mysql -uroot --skip-binary-as-hex test
use test;
select * from t;
\s
show full fields from t;
desc t;
select c.column_default
from information_schema.columns as c
where c.table_name like 't'
and c.table_schema = 'test'
and c.column_name = 'foo';
Output of "Retrieve varbinary data with --skip-binary-as-hex enabled"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.30 Homebrew
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t;
+----------+
| foo |
+----------+
| livebeef |
+----------+
1 row in set (0.00 sec)
mysql> \s
--------------
mysql Ver 8.0.30 for macos12.4 on x86_64 (Homebrew)
Connection id: 13
Current database: test
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.30 Homebrew
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Uptime: 5 min 6 sec
Threads: 2 Questions: 73 Slow queries: 0 Opens: 637 Flush tables: 4 Open tables: 85 Queries per second avg: 0.238
--------------
mysql> show full fields from t;
+-------+-----------------+-----------+------+-----+--------------------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-----------------+-----------+------+-----+--------------------+-------+---------------------------------+---------+
| foo | varbinary(1024) | NULL | YES | | 0x6C69766562656566 | | select,insert,update,references | |
+-------+-----------------+-----------+------+-----+--------------------+-------+---------------------------------+---------+
1 row in set (0.00 sec)
mysql> desc t;
+-------+-----------------+------+-----+--------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+--------------------+-------+
| foo | varbinary(1024) | YES | | 0x6C69766562656566 | |
+-------+-----------------+------+-----+--------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> select c.column_default
-> from information_schema.columns as c
-> where c.table_name like 't'
-> and c.table_schema = 'test'
-> and c.column_name = 'foo';
+--------------------+
| COLUMN_DEFAULT |
+--------------------+
| 0x6C69766562656566 |
+--------------------+
1 row in set (0.00 sec)
mysql> ^DBye
Suggested fix:
I expect `-skip-binary-as-hex` option also change the output of `show full fields from <table>`, `desc <table>` and `information_schema.column.column_default` to string.