MySQL Bugs: #10023: Limit of 4G rows per table for MyISAM and MERGE

Bug #10023	Limit of 4G rows per table for MyISAM and MERGE
Submitted:	20 Apr 2005 7:26	Modified:	1 May 2006 16:35
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Any (*)
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
Hi,

There is a limit of 4G rows or 4,294,967,296 which is not clearly documented. The problem arises when the MERGE engine is used to merge MyISAM tables.

The error message returned when opening a MERGE table with >4G of rows should be changed too. Right now error 143 is always returned, whatever is wrong with the table.

Regards,

Geert

How to repeat:

Make tables with ALOT of rows.

I filled up the following table with 270m rows, creating 18 tables. I used smallest field possible because disc space was limited.

CREATE TABLE `mtest_01` (
`id` char(0)
) ENGINE=MyISAM DEFAULT CHARSET=binary;

Then merge the tables. When I merge 16 of them, I get a nice count:

mysql-4.1.11-standard-log> select count(*) from `merged`;
+------------+
| count(*)   |
+------------+
| 4057680330 |
+------------+

Merging 17 of those tables:

mysql-4.1.11-standard-log> select count(*) from `merged`;
ERROR 1016 (HY000): Can't open file: 'merged.MRG' (errno: 143)

I copied the table 17 times, so they are 'exact' the same.

Suggested fix:

This limit and eventually the --with-big-tables compile option (found on http://dev.mysql.com/doc/mysql/en/configure-options.html) should be mentioned on the MERGE engine pages and MyISAM pages.

Maybe this limitation can be removed for the MERGE code?
At least, the error message should be something other than 143. The function for opening the merge table always returns the same error message whatever happens:

mysql-4.1/libmysqld/ha_myisammrg.cc:
int ha_myisammrg::open(const char *name, int mode, uint test_if_locked)
{
.. 
return (my_errno= HA_ERR_WRONG_MRG_TABLE_DEF);
}

Extra note: as of 5.0.4 the standard binaries available from MySQL AB are compiled with option --with-big-tables by default. This options works also for Merge engine tables.

Documented in refman-5.0 and refman-5.1.

For MyISAM: 

          There is a limit of 2<superscript>32</superscript>
          (~4.295E+09) rows in a <literal>MyISAM</literal> table. You
          can increase this limitation if you build MySQL with the
          <literal>--with-big-tables</literal> option then the row
          limitation is increased to
          (2<superscript>32</superscript>)<superscript>2</superscript>
          (1.844E+19) rows. See <xref linkend="configure-options"/>.
          Beginning with MySQL 5.0.4 all standard binaries are built
          with this option.

For MERGE:

            There is a limit of 2<superscript>32</superscript>
            (~4.295E+09)) rows to a <literal>MERGE</literal> table, just
            as there is with a <literal>MyISAM</literal>, it is
            therefore not possible to merge multiple
            <literal>MyISAM</literal> tables that exceed this
            limitation. However, you build MySQL with the
            <literal>--with-big-tables</literal> option then the row
            limitation is increased to
            (2<superscript>32</superscript>)<superscript>2</superscript>
            (1.844E+19) rows. See <xref linkend="configure-options"/>.
            Beginning with MySQL 5.0.4 all standard binaries are built
            with this option.