Description:
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 https://stackoverflow.com/questions/30419920/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 version of 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. But, no apparent equivalent for Innodb.
How to repeat:
Install x86 MySQL 5.7.23 on 32-bit Windows.
Create the following procedure to create and populate the table.
DROP PROCEDURE IF EXISTS `eds180810b`.`pop`;
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `eds180810b`.`pop`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
CREATE TABLE component (
recno bigint auto_increment,
estno int(11) UNSIGNED NOT NULL default '0',
cellno int(11) UNSIGNED NOT NULL default '0',
indcode char(6) NOT NULL default '',
occcd char(7) NOT NULL default '',
screenwhat char(1) NOT NULL default '',
family char(12) NOT NULL default '',
schstfips char(2) NOT NULL default '',
schnum char(9) NOT NULL default '',
empl int(11) NOT NULL default '0',
impute tinyint NOT NULL default '0',
wage double NULL default NULL,
PRIMARY KEY(recno),
KEY ind1 (cellno),
KEY ind2 (estno),
KEY ind3 (schstfips,schnum,estno)
) AVG_ROW_LENGTH=72 MAX_ROWS=10000000000;
insert into `component` (`recno`, `estno`, `cellno`, `indcode`, `occcd`, `screenwhat`, `family`, `schstfips`, `schnum`, `empl`, `impute`, `wage`) values('1','1','1','000000','00-0000','X','F1234','37','123456789','0','0','0');
while 1 do
insert into component select 0, `estno`, `cellno`, `indcode`, `occcd`, `screenwhat`, `family`, `schstfips`, `schnum`, `empl`, `impute`, `wage` from component limit 1000000;
end while;
END$$
DELIMITER ;
Execute the stored procedure:
CALL pop()
On my VM, a "Table is full" occurs at about 27M records.
