Details
-
Task
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
Description
Add a new command line option to mysqlbinlog, to choose between my_b_write_quoted() and pure HEX notation.
Create a table which has fields of type BLOB or GEOMETRY. Have binary log format as 'row'.
For example:
mysql> create table t(f blob) engine=innodb;
|
Query OK, 0 rows affected (0.07 sec)
|
mysql> insert into t values (0x000000000101000000000000000000F03F000000000000F03F);
|
Query OK, 1 row affected (0.01 sec)
|
Try to print the binarylog by using "mysqlbinlog -v"
### INSERT INTO `test`.`t`
|
### SET
|
### @1='\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00�?\x00\x00\x00\x00\x00\x00�?'
|
# at 574
|
#200923 15:31:19 serve
|
The BLOB field value gets printed in semi hex format. Hence when the above
'mysqlbinlog verbose' query is fed back to 'mysql client' by replacing '@1' with appropriate field name it inserts entirely a new value. Please find the following output.
mysql> insert into t values ('\x00\x00\x00\x00\x01\x01\x00\x00\x00\x00\x00\x00\x00\x00\x00�?\x00\x00\x00\x00\x00\x00�?');
|
Query OK, 1 row affected (0.05 sec)
|
mysql> select * from t;
|
+-------------------------------------------------------------------------+
|
| f |
|
+-------------------------------------------------------------------------+
|
| �? �? |
|
| x00x00x00x00x01x01x00x00x00x00x00x00x00x00x00�?x00x00x00x00x00x00�? |
|
+-------------------------------------------------------------------------+
|
2 rows in set (0.00 sec)
|
Suggested fix by Bar is mentioned below:
Make to row event decoder to print data in pure HEX format.
For ex:
### INSERT INTO `test`.`t`
|
### SET
|
### @1=0x0000000001010000000000000000.....;
|
With the above the 'mysqlbinlog verbose query' can be given as input to mysql client.
Add a new command line option to mysqlbinlog, to choose between `my_b_write_quoted() ` and pure HEX notation: 0xAABBCC. `my_b_write_quoted()` prints control character using escape sequences, and other characters - literally. This is not something the parser can understand. It's supposed to be read by a human. Add a new function, to print ALL characters using HEX notation. Both control and non-control.
I tried to recreate the problem on 11.4, but I don't see the semi-hex format with mariadb-binlog -v <binlogfile
/!/;#231206 11:46:05 server id 1 end_log_pos 555 CRC32 0xf79f5ff7 GTID 0-1-2/!/;#231206 11:46:05 server id 1 end_log_pos 697 CRC32 0x818536b3 Query thread_id=5 exec_time=0 error_code=0 xid=0/!/;