1

x86 MySQL 5.7.23 on Win 7 Pro x86. NTFS.

Encountering error 1114 "the table is full" when the table space exceeds 4G. I've tried creating databases with both 0 and 1 for the "file_per_table" setting.

This thread How many rows can mysql store? suggests I should be good all the way up to 2T.

Converting the table to MyISAM works but would like to keep the logging and recovery that comes with Innodb.

x64 MySQL on Win 7 Pro x64 (also NTFS) works.

MAX_ROWS=10000000000 in the CREATE TABLE. I realize that this is a MyISAM-only parameter and is supposed to be ignored for InnoDb. Is there an Innodb-specific setting somewhere else? Or is this a bug?

Tom Price
  • 191
  • 1
  • 10
  • Looking at the two bug reports, it seems that temp tables are to blame. How are you creating your table? Perhaps `INSERT ... SELECT ...` and the `SELECT` is generating a 4GB temp table? – Rick James Aug 27 '18 at 23:30

1 Answers1

0

Edit: Don't use 32-bit OS or 32-bit MySQL. Use only 64-bit.

https://dev.mysql.com/doc/mysql-installation-excerpt/5.7/en/windows-installation.html says:

If you need tables with a size larger than 4GB, install MySQL on an NTFS or newer file system.

It's probably too much of a coincidence that your tables max out at 4GB, and that's the max file size for FAT32 filesystem.

I suggest you double-check that the filesystem where your data is stored is NTFS. Note that it is not necessarily the C: drive. Run this SQL query in the MySQL client:

SELECT @@datadir, @@innodb_data_file_path;

The datadir is the default location of most MySQL data files and log files.

If innodb_data_file_path just names the data file, then it'll be relative to the datadir. But innodb_data_file_path can also name a full pathname to the file, and that can be on another location that is not under the datadir.

The innodb_data_file_path can also optionally set a max size for the global tablespace, for example if the value is like this:

ibdata1:12M:autoextend:max:4GB

See https://dev.mysql.com/doc/refman/5.7/en/innodb-init-startup-configuration.html for more information on that.

Also, individual tables can name a location that is outside the datadir, but you would probably know it if you did, because it requires an explicit option when you CREATE TABLE.

CREATE TABLE `tablename` ( ... ) DATA DIRECTORY = '/alternative/directory';

See https://dev.mysql.com/doc/refman/5.7/en/tablespace-placing.html for more information on that.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • It's definitely NTFS. I was able to build a MyISAM version in the same partition that was significantly larger than 4G. Starting to think it may be a sizeof(int*) issue in the Innodb code. – Tom Price Aug 13 '18 at 21:40
  • Then I would conclude it's a limit of the 32-bit code. Use only 64-bit code. – Bill Karwin Aug 13 '18 at 22:39
  • Known bug with 5.7 on x86. MySQL 5.6.41 (x86) works. . https://bugs.mysql.com/bug.php?id=92014 https://bugs.mysql.com/bug.php?id=80149 Report 80149 is dated January 2016. I would hold little hope that it’s going to be fixed. – Tom Price Aug 15 '18 at 16:18
  • Good find! But again, the easiest workaround is to use the 64-bit MySQL binary, which according to that bug report is not affected by the bug. – Bill Karwin Aug 15 '18 at 16:21
  • The MySQL Installer for Windows automatically installs x64 on an x64 OS.But, in this case, the customer had Win x86. Yes, far from optimal but all we had to work with. – Tom Price Aug 16 '18 at 15:01
  • I know how that goes. I did a consultation for a company using MySQL on Windows Server. They had been doing RAM upgrades to try to address performance, but they didn't seem to realize they were using Windows Server 32-bit, and all the RAM they had added could not be addressed by the OS. – Bill Karwin Aug 16 '18 at 15:36