Bug #106878 LOB pages are not taken into account while calculating table size in I_S.TABLES
Submitted: 31 Mar 2022 4:49 Modified: 31 Mar 2022 6:25
Reporter: Rahul Sisondia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.26, 8.0.28, 5.7.37 OS:Any
Assigned to: CPU Architecture:Any

[31 Mar 2022 4:49] Rahul Sisondia
Description:
I believe since lob pages are stored externally so they are not accounted while calculating table size in the I_S.TABLES table.

In fact there is a note in the manual for NDB enggine but I found that to be true for InnoDB as well :
 https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html 

> For NDB tables, the output of this statement shows appropriate values for the AVG_ROW_LENGTH and DATA_LENGTH columns, with the exception that BLOB columns are not taken into account.

How to repeat:
mysql> CREATE TABLE user_table (
    -> uid int NOT NULL DEFAULT '0',
    -> nickname varchar(32),
    -> bin_data mediumblob
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into user_table (uid, nickname, bin_data) values(ROUND(1.0 + 1048208.0 * RAND()),md5(rand()), repeat(RANDOM_BYTES(1024), 100));                                                                                                
Query OK, 1 row affected (0.08 sec)

mysql> SELECT table_name AS `Table`,
    ->     (data_length + index_length) `Size in Bytes`
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "sbtest"
    ->     AND table_name = "user_table";
+------------+---------------+
| Table      | Size in Bytes |
+------------+---------------+
| user_table |         16384 |
+------------+---------------+
1 row in set (0.07 sec)

But we inserted 102438 bytes in the record. 

mysql> select length(ROUND(1.0 + 1048208.0 * RAND())) + length(md5(rand())) +  length(repeat(RANDOM_BYTES(1024), 100)) 'size in bytes';
+---------------+
| size in bytes |
+---------------+
|        102438 |
+---------------+
1 row in set (0.07 sec)

Suggested fix:
Since lob sizes are usually huge, users not taking into account the their size gives misinformation. 
The first preference should be to consider size of lobs as well but if that is not possible then at least update the manual say that it is applicable for INNODB as well.
[31 Mar 2022 6:25] MySQL Verification Team
Hello Rahul,

Thank you for the report and feedback.

regards,
Umesh