MySQL Row Size Too Large (> 8126) with MyISAM tables

MySQLmysql-5.6

We upgraded a MySQL database using MyISAM tables from v5.1 to v5.6 and have a similar situation to MYISAM can't create table: Row size too large. Other questions like this one Row size too large (> 8126) talk about InnoDB tables.

Row format is already dynamic. It doesn't make sense to change innodb_log_file_size for MyISAM tables, so what is the alternative?

Edits to address issues raised by Rick James:

This is a legacy application, and we have learned a lot since this table was created. However, getting money to make major changes is difficult right now. Converting to InnoDB is an option one day, but probably not right now.

  • There are some mediumtext and mediumblob columns.
  • It is MyISAM in v5.1 and v5.6.
  • The table did not have issues in v5.1.
  • We probably didn't specify the character set in either version. I just copied the tables to the new version and ran mysql-upgrade which didn't change anything.
  • There are some char and varchar columns.
  • The mediumtext columns contain data that was converted to x-UTF-16LE-BOM by Java code (using "UnicodeLittle") before calling setString() on the data in the JDBC driver.

Create table statement:

CREATE TABLE `cp_results` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cp_parent_ID` int(10) unsigned NOT NULL DEFAULT '0',
  `errors` text,
  `errors_gz` blob,
  `status` text,
  `status_gz` blob,
  `st_data` mediumtext,
  `st_data_gz` mediumblob,
  `l_stress_vs_hrs` mediumtext,
  `l_stress_vs_hrs_gz` mediumblob,
  `l_allowable_vs_dp` mediumtext,
  `l_allowable_vs_dp_gz` mediumblob,
  `l_outfile` mediumtext,
  `l_outfile_gz` mediumblob,
  `c_data` mediumtext,
  `c_data_gz` mediumblob,
  `timestamp` varchar(100) NOT NULL DEFAULT '',
  `beta_tables_spc_tension` text,
  `beta_tables_spc_tension_gz` blob,
  `cMarginSafety` float DEFAULT NULL,
  `st_allowable_vs_dp` mediumtext,
  `st_allowable_vs_dp_gz` mediumblob,
  `lMarginSafety` float DEFAULT NULL,
  `l_fail_hrs` float DEFAULT NULL,
  `st_fail_hrs` float DEFAULT NULL,
  `stMarginSafety` float DEFAULT NULL,
  `st_version` varchar(15) NOT NULL DEFAULT '',
  `l_version` varchar(15) NOT NULL DEFAULT '',
  `c_version` varchar(15) NOT NULL DEFAULT '',
  `m_version` varchar(15) NOT NULL DEFAULT '',
  `m_outfile_gz` mediumblob,
  `matdb_version` varchar(15) NOT NULL DEFAULT '',
  `l_fxf_data_gz` mediumblob,
  `l_ssf_data_gz` mediumblob,
  `st_stress_vs_hrs` mediumblob,
  `st_stress_vs_hrs_gz` mediumblob,
  `st_ssf_data_gz` mediumblob,
  `st_fxf_data_gz` mediumblob,
  `create_timestamp` bigint(20) unsigned DEFAULT NULL,
  `c_crit_c` float DEFAULT NULL,
  `c_crit_a` float DEFAULT NULL,
  `c_crit_surface` float DEFAULT NULL,
  `run_version` varchar(20) NOT NULL DEFAULT '',
  `p_outfile` mediumtext,
  `p_outfile_gz` mediumblob,
  `p_fail_hrs` float DEFAULT NULL,
  `p_version` varchar(15) DEFAULT NULL,
  `submitter_name` varchar(190) NOT NULL DEFAULT '',
  `submitter_username` varchar(30) NOT NULL DEFAULT '',
  `submitter_system_id` varchar(30) NOT NULL DEFAULT '',
  `archive_flag` char(2) NOT NULL DEFAULT '0',
  `temp_ID` varchar(60) NOT NULL DEFAULT '',
  `delete_flag` char(2) NOT NULL DEFAULT '0',
  `delete_timestamp` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name` varchar(200) NOT NULL DEFAULT '',
  `beta_tables_spc_compression_gz` blob,
  `beta_tables_preload_tension_gz` blob,
  `beta_tables_preload_compression_gz` blob,
  `beta_tables_residual_gz` blob,
  `c_rstfile_gz` mediumblob,
  `l_rstfile_gz` mediumblob,
  `st_rstfile_gz` mediumblob,
  `l_cxc_data_gz` mediumblob,
  `st_cxc_data_gz` mediumblob,
  `archived_inputFile_gz` mediumblob,
  `m_rstfile_gz` mediumblob,
  `m_fail_hrs` float DEFAULT NULL,
  `m_plot_file_gz` mediumblob,
  `l_init_hrs` float DEFAULT NULL,
  `st_init_hrs` float DEFAULT NULL,
  `m_init_hrs` float DEFAULT NULL,
  `l_single_block_dmg` float DEFAULT NULL,
  `l_cumulative_init_dmg` float DEFAULT NULL,
  `l_cumulative_dmg` float DEFAULT NULL,
  `st_cumulative_init_dmg` float DEFAULT NULL,
  `st_single_block_dmg` float DEFAULT NULL,
  `st_cumulative_dmg` float DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `id_timestamp_key` (`cp_parent_ID`,`create_timestamp`),
  KEY `create_timestamp` (`create_timestamp`),
  KEY `submitter_name` (`submitter_name`),
  KEY `submitter_username` (`submitter_username`),
  KEY `submitter_system_id` (`submitter_system_id`),
  KEY `archive_flag` (`archive_flag`),
  KEY `cp_parent_ID` (`cp_parent_ID`),
  KEY `temp_ID` (`temp_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=16824793 DEFAULT CHARSET=latin1 MAX_ROWS=120000 AVG_ROW_LENGTH=68664;

Best Answer

It is bad practice to have "too many" columns in a table or index. It would be quite handy if you provided SHOW CREATE TABLE for the table that is giving you trouble. Here are some guesses; correct me if I am wrong:

  • There are a lot of columns.
  • No TEXT or BLOB.
  • The table is MyISAM in both 5.1 and 5.6.
  • The table did not have a 8196 problem in 5.1.
  • The character set was not specified in 5.1, but defaulted to latin1.
  • There may be some CHAR columns.
  • The character set was still not specified when creating the table on 5.6.

But 5.6 defaults to utf8, which takes up to 3 bytes per character. In that case, explicitly add CHARACTER SET latin1 to each table definition.

(You really should consider migrating to InnoDB.)

More

The default CHARACTER SET changing from latin1 to utf8 is probably the proximate cause.

Looking at the l_*, p_*, c_*, etc columns, I see something of a pattern. In general it is not wise to splay an array (l/p/c/...) across columns. I recommend you move those columns to another table. It would look something like

CREATE TABLE misc (
    ID int(10) unsigned NOT NULL,   -- for JOINing
    type ENUM('l', 'p', 'c', ...) NOT NULL,
    init_hrs ...,
    fail_hrs ...,
    outfile ...,
    rstfile_gz ...,
    ...
    PRIMARY KEY(ID, type)
);
Related Question