MySQL 5.6 row format changes when changing storage engine from MyISAM to InnoDB

antelopeinnodbmyisamMySQL

I am testing an upgrade of all existing MySQL 5.6 tables from MyISAM to InnoDB. I converted all row formats to dynamic first for all the tables to be on Barracuda then running alter table <tablename> engine = InnoDB for 16 tables. 12 of the 16 tables changed file formats as well without an alter table command. I am at a loss to understand this. I think this may be related to the .frm files, but I'm not sure how. I've checked environment variables:

innodb_file_format is showing Barracuda

innodb_file_format_check is ON

A couple of the tables: Articletranslations is showing as compressed, pubmedabstractauthors and pubmedtranslated are showing as compact.
The create table statements from tables that I had changed to dynamic file format before changing the storage engine to InnoDB.

Table: articletranslations

Create Table: CREATE TABLE `articletranslations` (
  `TranslationID` int(11) NOT NULL AUTO_INCREMENT,
  `ArticleID` int(11) NOT NULL,
  `language` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `TextContent` longtext COLLATE utf8_unicode_ci,
  `Name` text COLLATE utf8_unicode_ci,
  `Tags` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Detail_Abstract` longtext COLLATE utf8_unicode_ci,
  `Disclosures` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Discussion` longtext COLLATE utf8_unicode_ci,
  `Acknowledgements` longtext COLLATE utf8_unicode_ci,
  `D` longtext COLLATE utf8_unicode_ci,
  `Materials` text COLLATE utf8_unicode_ci,
  `HTMLTopContent` text COLLATE utf8_unicode_ci,
  `Rep_Results` longtext COLLATE utf8_unicode_ci,
  `Introduction` text COLLATE utf8_unicode_ci,
  `IsMachine` tinyint(1) NOT NULL DEFAULT '1',
  `DateTranslated` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TranslationID`),
  KEY `ArticleTranslations_Language_ArticleID` (`language`,`ArticleID`),
  KEY `ArticleTranslations_ArticleID` (`ArticleID`)
) ENGINE=InnoDB AUTO_INCREMENT=177437 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

Table: pubmedabstractauthors

Create Table: CREATE TABLE `pubmedabstractauthors` (
  `AuthorID` int(11) NOT NULL AUTO_INCREMENT,
  `ForeName` varchar(255) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `Initials` varchar(255) NOT NULL,
  PRIMARY KEY (`AuthorID`),
  KEY `names` (`ForeName`,`LastName`,`Initials`)
) ENGINE=InnoDB AUTO_INCREMENT=712515 DEFAULT CHARSET=latin1

Table: pubmedtranslated

Create Table: CREATE TABLE `pubmedtranslated` (
  `PMID` int(11) NOT NULL,
  `ArticleTitle` text COLLATE utf8_unicode_ci NOT NULL,
  `ArticleAbstract` text COLLATE utf8_unicode_ci NOT NULL,
  `LanguageID` smallint(6) NOT NULL,
  PRIMARY KEY (`PMID`,`LanguageID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Best Answer

Please note that you ran ALTER TABLE ... ENGINE=InnoDB; That is a full table rebuild whether you go from MyISAM to InnoDB, or InnoDB to InnoDB (known as the NULL ALTER).

Please note the paragraph from the MySQL 5.7 Docs on innodb_file_format:

The innodb_file_format default value was changed to Barracuda in MySQL 5.7.

The innodb_file_format setting is ignored when creating tables that use the DYNAMIC row format. A table created using the DYNAMIC row format always uses the Barracuda file format, regardless of the innodb_file_format setting. To use the COMPRESSED row format, innodb_file_format must be set to Barracuda.

Related Question