MySQL 64kb size limit for MyISAM table-based metadata storage unclear - differences between MySQL and MariaDB

Question

The information found in the official MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-file-removal.html does not match the information shown in the manual at https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html for version 8.

First link says that the 64kb limit was dropped, the 2nd link says it exists.

It gets even more confusing when looking at MariaDB, as the documentation for MariaDB does not give any limits (which certainly exist).

Answer 1

You're mixing up several different limits.

MySQL has a 64KB limit on the size of a given row (not counting BLOB/TEXT/JSON). This limit still exists.

MySQL used to store metadata about the table in a file with a .frm extension. This did not store any rows, but it stored metadata, i.e. most of what becomes SHOW CREATE TABLE. The table name, columns, data types, constraints, defaults, character sets, metadata comments, etc.

A part of the .frm file was also limited to 64KB, but that had nothing to do with the 64KB row size limit. The fact that the size was the same was effectively a coincidence. This blog covers this subject in more detail: https://www.percona.com/blog/understanding-the-maximum-number-of-columns-in-a-mysql-table/

The .frm files were obsoleted in MySQL 8.0 when they reimplemented the metadata system with the data dictionary. That's what the first link explains.

In the second link, there is no mention of a 46KB size limit. There is a mention that MySQL has a limit of 4096 columns per table, but this is not related to the size limit of a row or size limit of metadata. Did you misread this?

As far as MariaDB goes, it is a fork of MySQL so many limits are still the same as what MySQL's limits were at the time of the fork. https://mariadb.com/kb/en/innodb-limitations/ states:

MariaDB imposes a row-size limit of 65,535 bytes for the combined sizes of all columns. If the table contains BLOB or TEXT columns, these only count for 9 - 12 bytes in this calculation, given that their content is stored separately.

65,535 bytes is the same as 64KB.

That said, MariaDB is different from MySQL. MariaDB forked from MySQL in 2010, and MariaDB is more and more incompatible with MySQL as time goes on and both products make changes without regard to maintaining compatibility. For example, MariaDB still uses .frm files because they forked from MySQL long before the new data dictionary was implemented.

Answer 2

Apples vs oranges.

The .frm file was limited to 64KB. That file no longer exists in 8.0. That file went away in 8.0 when the internal Data Dictionary was implemented. It is now an internal InnoDB set of tables. Note: 8.0 supports only ENGINE=InnoDB.

MariaDB, as far as I know, still uses .frm files as "data dictionaries", even for InnoDB.

The .frm can be considered a copy of the CREATE TABLE (encoded in some way). So, a table with lots of long-named columns and ENUMs and INDEXes could extend past 64KB. (I contend such bulk is a sign of poor schema design.) In 8.0's internal DD, there is no equivalent limit.

In all cases, there are limits on the character length of various names (columns, tables, indexes). Some are limited to 64, some allow more. The charset seems to be stuck at utf8mb3. That is, a 64-char column name could be as big as 192 bytes.

MyISAM should not be used. And it was removed from 8.0. But it still has .frm files for the table definition. So, the removal from 8.0 docs is a matter of cleaning out cruft relating to MyISAM.

MariaDB still supports MyISAM, and may have an undocumented limit of 64KB.