Description:
Hi,
I have the following table:
CREATE TABLE test (
pk1 smallint unsigned NOT NULL,
pk2 tinyint unsigned NOT NULL,
99x bigint NOT NULL DEFAULT '0'
PRIMARY KEY (pk1,pk2)
) ENGINE=InnoDB;
I am expecting the row size to be ~800 bytes (99*8 + 1 + 2 + row headers).
When I trying to compress that table with KEY_BLOCK_SIZE=1 and without any rows in it, I get the following error:
> ALTER TABLE test KEY_BLOCK_SIZE=1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
Note that this table does not contain TEXT or BLOBs, so the advise in the message above does not apply.
Note also that the 8126 in the message above applies to uncompressed table and that my expected row size is much smaller than this (~800 bytes).
My understanding is that 2 rows must fit in an InnoDB page, but that this restriction is relaxed for COMPRESSED tables. In [1], I can read:
"Generally, MySQL requires that each B-tree page in an InnoDB table can accommodate at least two records. For compressed tables, this requirement has been relaxed."
[1]: https://dev.mysql.com/doc/refman/5.7/en/innodb-compression-internals.html
Note that KEY_BLOCK_SIZE=1 will succeed with 87x bigint in the table, but will fail with 88. I looks like there is a 768 byte limit somewhere.
For ease of testing, below is a bash script that generate the corresponding CREATE TABLE statement:
echo "CREATE TABLE test (
pk1 smallint(5) unsigned NOT NULL,
pk2 tinyint(3) unsigned NOT NULL,"; \
seq -f " f%02.0f bigint NOT NULL DEFAULT '0'," 1 99; \
echo " PRIMARY KEY (pk1,pk2)
) ENGINE=InnoDB;";
Is that a bug or an undocumented restriction ? In all cases, the error message could be clearer.
Many thanks,
JFG
How to repeat:
In bash:
$ mysql <<< "select version()"
version()
5.7.17-log
$ (
echo "CREATE TABLE test (
pk1 smallint(5) unsigned NOT NULL,
pk2 tinyint(3) unsigned NOT NULL,"; \
seq -f " f%02.0f bigint NOT NULL DEFAULT '0'," 1 99; \
echo " PRIMARY KEY (pk1,pk2)
) ENGINE=InnoDB;";
) | mysql test
$ mysql test <<< "alter table test KEY_BLOCK_SIZE=1;"
ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
Suggested fix:
If this is not a bug, update the documentation and the error message.
