Thank you for your help! If the status of the bug report you submitted changes,
you will be notified. You may return here and check on the status or update
your report at any time. That URL for your bug report is:
http://bugs.mysql.com/106878.
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.