Description:
Before MySQL 5.7.6, the MySQL query optimizer internally used the MyISAM storage engine for executing joins, ORDER BY, INSERT...SELECT and similar.
This changed in the following worklog:
WL#6737 InnoDB: Enabling InnoDB temp-tables as default internal SE for MySQL Optimizer
With this change, the SQL executor started to use ENGINE=InnoDB ROW_FORMAT=COMPACT for its internal tables.
This introduced the following limitations when using the default settings (such as innodb_page_size=16k):
1. A row cannot have more than 10 columns that are longer than 788 bytes.
2. A row cannot have more than 1017 columns.
3. A row cannot be longer than half the page size (for example, cannot have 1000 columns with 10 bytes each).
This mostly affects usage with other engines than InnoDB.
With MyISAM, you can probably create up to some 4000 columns in a table.
How to repeat:
SET GLOBAL internal_tmp_disk_storage_engine=InnoDB;
CREATE TABLE t1(col1 TEXT, col2 TEXT, col3 TEXT, ..., col11 TEXT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
INSERT INTO t1 SET col1=REPEAT('a',1000),col2=REPEAT('a',1000),...,col11=REPEAT('a',1000);
INSERT INTO t1 SELECT * FROM t1; -- fails in 5.7.6 because there are more than 10 externally stored columns
-- Note: WL#8307 can alleviate this by changing the default to ROW_FORMAT=DYNAMIC, raising the limit to 200 columns
CREATE TABLE t2(...1018 columns...) ENGINE=MyISAM;
INSERT INTO t2 SELECT * FROM t2; -- fails in 5.7.6 because there are more than 1017 columns
CREATE TABLE t3(col1 CHAR(10) NOT NULL,col2 CHAR(10) NOT NULL,...,col1000 CHAR(10) NOT NULL) ENGINE=MyISAM;
INSERT INTO t3 VALUES();
INSERT INTO t3 SELECT * FROM t3; -- fails in 5.7.6 because the InnoDB record would be too big
Suggested fix:
Idea from Erlend:
If creating the temporary table fails, fall back to MyISAM.
If using the temporary table fails (such as in case 1), alter the temporary table to MyISAM and keep using MyISAM.
Failing that, document a workaround:
SET GLOBAL internal_tmp_disk_storage_engine=MyISAM;
