[MDEV-12169] port "Print binary data as hex in the mysql client" by @dveeden Created: 2017-03-03  Updated: 2017-07-17  Resolved: 2017-07-17

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Fix Version/s: 5.5.57

Type: Task Priority: Major
Reporter: Sergey Vojtovich Assignee: Sergey Vojtovich
Resolution: Fixed Votes: 0
Labels: contribution, foundation

Issue Links:
PartOf
is part of MDEV-13294 5.5.57 merge Closed

 Description    

original code: mysql/mysql-server#118
 
 
 
MariaDB [test]> create table t1(id binary(16) primary key, name varchar(100));
 
Query OK, 0 rows affected (0.03 sec)
 
 
 
MariaDB [test]> insert into t1 values(unhex(replace(uuid(),'-','')), 'test 1');
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [test]> insert into t1 values(unhex(replace(uuid(),'-','')), 'test 2');
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [test]> insert into t1 values(unhex(replace(uuid(),'-','')), 'test 3');
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [test]> select * from t1;
 
+------------------+--------+
 
| id | name |
 
+------------------+--------+
 
| ^_?m?T?dl?^T?|H | test 1 |
 
| !?T?dl?^T?|H | test 2 |
 
| #t1?T??dl?^T??|H | test 3 |
 
+------------------+--------+
 
3 rows in set (0.00 sec)
 
 
 
With the addition of this patch, the following is possible:
 
 
 
MariaDB [test]> select * from t1;
 
+------------------------------------+--------+
 
| id | name |
 
+------------------------------------+--------+
 
| 0x1FE6166DFF5411E6AB646C8814987C48 | test 1 |
 
| 0x21EFDCB2FF5411E6AB646C8814987C48 | test 2 |
 
| 0x237431D4FF5411E6AB646C8814987C48 | test 3 |
 
+------------------------------------+--------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1 where id = 0x21EFDCB2FF5411E6AB646C8814987C48;
 
+------------------------------------+--------+
 
| id | name |
 
+------------------------------------+--------+
 
| 0x21EFDCB2FF5411E6AB646C8814987C48 | test 2 |
 
+------------------------------------+--------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1\G
 
*************************** 1. row ***************************
 
id: 0x1FE6166DFF5411E6AB646C8814987C48
 
name: test 1
 
*************************** 2. row ***************************
 
id: 0x21EFDCB2FF5411E6AB646C8814987C48
 
name: test 2
 
*************************** 3. row ***************************
 
id: 0x237431D4FF5411E6AB646C8814987C48
 
name: test 3
 
3 rows in set (0.01 sec)
 
 
 
This patch also introduces the new option --binary-as-hex=0 in
 
order to disable this new behavior.



 Comments   
Comment by Sergei Golubchik [ 2017-03-03 ]

I'd like something that makes it clear when a value is auto-converted to hex and when a value is simply printed as such (and looks like a hex number, because it's stored in a string as hex representation of something). For example

+-----------------------------------+--------+
 
| id                                | name   |
 
+-----------------------------------+--------+
 
|x'21EFDCB2FF5411E6AB646C8814987C48'| test 2 |
 
+-----------------------------------+--------+

See, spaces are missing — it's impossible to have that for a normal value, so it must mean some special code inside mysql client (indeed, special auto-hex code). I don't mean this is what the output should look like, it's probably not even a good output suggestion, it's just to illustrate the point. Another one:

+-------------------------------------+--------+
 
| id                                  | name   |
 
+-------------------------------------+--------+
 
|= 0x21EFDCB2FF5411E6AB646C8814987C48 | test 2 |
 
+-------------------------------------+--------+

Ugh, it's even worse. But you get the point.

Comment by Eric Herman [ 2017-03-06 ]

I don't see the need.

How is what you're highlighting different than strings which happen to contain numbers?

MariaDB [test]> select * from t0;
 
+------+------+
 
| id   | name |
 
+------+------+
 
|    1 | 1    |
 
+------+------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SHOW CREATE TABLE t0\G
 
*************************** 1. row ***************************
 
       Table: t0
 
Create Table: CREATE TABLE `t0` (
 
  `id` int(11) DEFAULT NULL,
 
  `name` varchar(11) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0.00 sec)

If it is a binary column, the values are binary, and I seldom want client experience which happens to print okay in some cases, and not others. With this patch, if I want that "loose" behavior I can get it in a couple ways, "unhex(col)" for case-by-case usage or globally by setting --binary-as-hex=0 (on the comand line, or in the conf).

Comment by Sergei Golubchik [ 2017-03-06 ]

You cannot do unhex() for case by case usage, because unhex is a server function, and auto-hex works on the client.

Anyway, if you feel there's no need to do it and no ambiguity, let's not do it.
It can be always added later, if users will request it.

Comment by Eric Herman [ 2017-03-06 ]

D'oh! Excellent point! Indeed unhex() would not work like that.

That said, indeed I continue to think that the commandline switch is enough for those rare cases where I am piping between processes.

(Speaking for myself, I truly /never/ want the output to trash my terminal's state because I did something as seemingly harmless as "select * from foo limit 1".)

Comment by Sergei Golubchik [ 2017-07-17 ]

merged in MDEV-13294

Generated at Thu Feb 08 07:55:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.