Bug #116794 Blobs in dropped columns will be residual after a update.
| Submitted: | 27 Nov 2024 4:10 | Modified: | 28 Nov 2024 2:25 |
|---|---|---|---|
| Reporter: | mengchu shi (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | BLOB, instant DROP column, UPDATE | ||
[27 Nov 2024 22:54]
MySQL Verification Team
Hi, Can you share your tool? This looks like normal innodb behavior.
[27 Nov 2024 23:36]
Jean-François Gagné
I have not tested, but a way to repro could be to fill the tables with many rows which have an off-page blob. After an ALTER DROP blob and an UPDATE all, I would expect to have many free pages: the off-page blob, after the UPDATE, should generate free pages if this is indeed a bug.
[28 Nov 2024 2:23]
mengchu shi
I use this tool only to show that Blob was residual. ----------------------------------- You can check this bug as below without any tools: # 1. flush all pages into disk SET GLOBAL innodb_log_checkpoint_now=ON; # 2. The page #5 is FIL_PAGE_TYPE_LOB_FIRST which saves one lob data. ╰─$ hexdump -C -n 16384 -s 81920 t1.ibd 00014000 54 af 68 2d 00 00 00 05 00 00 00 00 ff ff ff ff |T.h-............| 00014010 00 00 00 00 01 4e 10 6d 00 18 00 00 00 00 00 00 |.....N.m........| 00014020 00 00 00 00 00 08 00 00 00 00 00 01 00 00 00 00 |................| 00014030 07 ac 00 00 00 00 00 00 1f 40 00 00 00 00 07 ac |.........@......| 00014040 00 00 00 01 00 00 00 05 00 60 00 00 00 05 00 60 |.........`.....`| 00014050 00 00 00 09 00 00 00 05 00 9c 00 00 00 05 02 7c |...............|| 00014060 ff ff ff ff 00 00 ff ff ff ff 00 00 00 00 00 00 |................| * 00014080 07 ac 00 00 00 00 07 ac 00 00 00 00 00 00 00 00 |................| 00014090 00 00 00 05 1f 40 00 00 00 00 00 01 ff ff ff ff |.....@..........| 000140a0 00 00 00 00 00 05 00 d8 00 00 00 00 ff ff ff ff |................| 000140b0 00 00 ff ff ff ff 00 00 00 00 00 00 00 00 00 00 |................| 000140c0 00 00 00 00 00 00 00 00 00 00 00 00 ff ff ff ff |................| 000140d0 00 00 00 00 00 00 00 00 00 00 00 05 00 9c 00 00 |................| 000140e0 00 05 01 14 00 00 00 00 ff ff ff ff 00 00 ff ff |................| 000140f0 ff ff 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00014100 00 00 00 00 00 00 00 00 ff ff ff ff 00 00 00 00 |................| 00014110 00 00 00 00 00 00 00 05 00 d8 00 00 00 05 01 50 |...............P| 00014120 00 00 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00 |................| 00014130 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00014140 00 00 00 00 ff ff ff ff 00 00 00 00 00 00 00 00 |................| 00014150 00 00 00 05 01 14 00 00 00 05 01 8c 00 00 00 00 |................| 00014160 ff ff ff ff 00 00 ff ff ff ff 00 00 00 00 00 00 |................| 00014170 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00014180 ff ff ff ff 00 00 00 00 00 00 00 00 00 00 00 05 |................| 00014190 01 50 00 00 00 05 01 c8 00 00 00 00 ff ff ff ff |.P..............| 000141a0 00 00 ff ff ff ff 00 00 00 00 00 00 00 00 00 00 |................| 000141b0 00 00 00 00 00 00 00 00 00 00 00 00 ff ff ff ff |................| 000141c0 00 00 00 00 00 00 00 00 00 00 00 05 01 8c 00 00 |................| 000141d0 00 05 02 04 00 00 00 00 ff ff ff ff 00 00 ff ff |................| 000141e0 ff ff 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 000141f0 00 00 00 00 00 00 00 00 ff ff ff ff 00 00 00 00 |................| 00014200 00 00 00 00 00 00 00 05 01 c8 00 00 00 05 02 40 |...............@| 00014210 00 00 00 00 ff ff ff ff 00 00 ff ff ff ff 00 00 |................| 00014220 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00014230 00 00 00 00 ff ff ff ff 00 00 00 00 00 00 00 00 |................| 00014240 00 00 00 05 02 04 00 00 00 05 02 7c 00 00 00 00 |...........|....| 00014250 ff ff ff ff 00 00 ff ff ff ff 00 00 00 00 00 00 |................| 00014260 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| 00014270 ff ff ff ff 00 00 00 00 00 00 00 00 00 00 00 05 |................| 00014280 02 40 ff ff ff ff 00 00 00 00 00 00 ff ff ff ff |.@..............| 00014290 00 00 ff ff ff ff 00 00 00 00 00 00 00 00 00 00 |................| 000142a0 00 00 00 00 00 00 00 00 00 00 00 00 ff ff ff ff |................| 000142b0 00 00 00 00 00 00 00 00 78 78 78 78 78 78 78 78 |........xxxxxxxx| 000142c0 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 |xxxxxxxxxxxxxxxx| * 000161f0 78 78 78 78 78 78 78 78 00 00 00 00 00 00 00 00 |xxxxxxxx........| 00016200 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 00017ff0 00 00 00 00 00 00 00 00 54 af 68 2d 01 4e 10 6d |........T.h-.N.m| # 3. The page #4 is only one FIL_PAGE_INDEX which has only one record. ╰─$ hexdump -C -n 16384 -s 65536 t1.ibd 00010000 c7 ca 0e a2 00 00 00 04 ff ff ff ff ff ff ff ff |................| 00010010 00 00 00 00 01 4e 6b 0d 45 bf 00 00 00 00 00 00 |.....Nk.E.......| 00010020 00 00 00 00 00 08 00 02 00 94 80 03 00 00 00 00 |................| 00010030 00 7f 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................| 00010040 00 00 00 00 00 00 00 00 00 a6 00 00 00 08 00 00 |................| 00010050 00 02 02 72 00 00 00 08 00 00 00 02 01 b2 01 00 |...r............| 00010060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......| 00010070 73 75 70 72 65 6d 75 6d 03 01 40 00 10 ff f1 80 |supremum..@.....| 00010080 00 00 00 00 00 00 04 00 00 00 00 07 bb 02 00 00 |................| 00010090 01 26 01 51 00 00 00 00 00 00 00 00 00 00 00 00 |.&.Q............| 000100a0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................| * 00013ff0 00 00 00 00 00 70 00 63 c7 ca 0e a2 01 4e 6b 0d |.....p.c.....Nk.| 00014000 # 4. Let's analyze this record. There's no Blob in it. 03 | 01 | 40 00 10 ff f1 | 80 00 00 00 00 00 00 04 | 00 00 00 00 07 bb | 02 00 00 01 26 01 51 nulls | ver | HDRs | `id` | TRX_ID | ROLL_PTR ----------------------------------- You can also check the codes in function page_cur_delete_rec. It directly free the old record but doesn't handle blobs. It's no matter if it's a normal record: 1. If the blob column in record is updated, undo will log it so that it will be cleanned during UNDO PURGE stage. 2. Otherwise, it's no need to be cleanned. But a blob in a instant dropped column can't be logged in undo, so is residual forever.
[28 Nov 2024 2:25]
MySQL Verification Team
I did verify the bug but I reduced the severity to S3. Do you have any special reasoning behind S2? Thanks
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Blobs in instant dropped columns will be residual after a update. In my opinion, these Blobs should be purged when the column is instant dropped. How to repeat: use test; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `c1` longtext DEFAULT NULL, `c2` longtext DEFAULT NULL, `c3` longtext DEFAULT NULL ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; # Insert a value longer than 8126 to call page_zip_rec_needs_ext INSERT INTO `t1` (id, `c1`, `c2`) VALUES (4, repeat('x', 8000), repeat('x', 7000)); # Instat drop column ALTER TABLE `t1` DROP COLUMN `c1`, ALGORITHM=INSTANT; # Update record. whether the new record is external or internal, the # blob of dropped `c1` in old record will be missed. UPDATE `t1` SET `c2`=NULL where id = 4; # Then we use our tool and found a residual blob page. | PAGE_TYPE | START | END | COUNT | VALIDITY | | ------------------------ | ----- | --- | ----- | -------- | | FSP_HDR | 0 | 0 | 1 | NoCheck | | IBUF_BITMAP | 1 | 1 | 1 | NoCheck | | INODE | 2 | 2 | 1 | NoCheck | | SDI | 3 | 3 | 1 | NoCheck | | INDEX | 4 | 4 | 1 | NoCheck | | LOB_FIRST | 5 | 5 | 1 | NoCheck | | ALLOCATED | 6 | 6 | 1 | NoCheck |