Testing silent truncation of zeros in BINARY columns in MySQL

mysql_binary_truncation.sql

mysql> CREATE TABLE foo(id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bar BINARY(16), baz CHAR(36));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO foo (bar, baz) VALUES (UUID_TO_BIN('dc23a9b9-a129-11ee-95fb-0242ac110000'), 'dc23a9b9-a129-11ee-95fb-0242ac110000');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO foo (bar, baz) VALUES (UUID_TO_BIN('dc23a9b9-a129-11ee-95fb-0242ac110000'), 'dc23a9b9a12911ee95fb0242ac110000');
Query OK, 1 row affected (0.01 sec)

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000003 |      6240 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> exit;
Bye

❯ mysql -h 127.0.0.1 -u root -e "SELECT id, BIN_TO_UUID(bar) bar, baz FROM test.foo;"
+----+--------------------------------------+--------------------------------------+
| id | bar                                  | baz                                  |
+----+--------------------------------------+--------------------------------------+
|  1 | dc23a9b9-a129-11ee-95fb-0242ac110000 | dc23a9b9-a129-11ee-95fb-0242ac110000 |
|  2 | dc23a9b9-a129-11ee-95fb-0242ac110000 | dc23a9b9a12911ee95fb0242ac110000     |
+----+--------------------------------------+--------------------------------------+

❯ mysql -h 127.0.0.1 -u root -p -e "TRUNCATE TABLE test.foo;"

❯ docker cp mysql:/var/lib/mysql/binlog.000003 .
Successfully copied 9.22kB to /Users/sgarland/git/

❯ mysqlbinlog -vv binlog.000003 | grep -B10 TRUNCATE
#231222 19:27:48 server id 1  end_log_pos 7244 CRC32 0x40828c1c 	Anonymous_GTID	last_committed=26	sequence_number=27	rbr_only=no	original_committed_timestamp=1703291268652273	immediate_commit_timestamp=1703291268652273	transaction_length=175
# original_commit_timestamp=1703291268652273 (2023-12-22 19:27:48.652273 EST)
# immediate_commit_timestamp=1703291268652273 (2023-12-22 19:27:48.652273 EST)
/*!80001 SET @@session.original_commit_timestamp=1703291268652273*//*!*/;
/*!80014 SET @@session.original_server_version=80035*//*!*/;
/*!80014 SET @@session.immediate_server_version=80035*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 7244
#231222 19:27:48 server id 1  end_log_pos 7342 CRC32 0x08e19d61 	Query	thread_id=19	exec_time=0	error_code=0	Xid = 349
SET TIMESTAMP=1703291268/*!*/;
TRUNCATE TABLE test.foo

❯ mysqlbinlog --stop-position 7244 binlog.000003 | mysql -h 127.0.0.1 -u root -p

❯ mysql -h 127.0.0.1 -u root -p -e "SELECT id, BIN_TO_UUID(bar), baz FROM test.foo;"
Enter password: 
+----+--------------------------------------+--------------------------------------+
| id | BIN_TO_UUID(bar)                     | baz                                  |
+----+--------------------------------------+--------------------------------------+
|  1 | dc23a9b9-a129-11ee-95fb-0242ac110000 | dc23a9b9-a129-11ee-95fb-0242ac110000 |
|  2 | dc23a9b9-a129-11ee-95fb-0242ac110000 | dc23a9b9a12911ee95fb0242ac110000     |
+----+--------------------------------------+--------------------------------------+