| 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
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 |