0

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
  • What exactly do you mean with "a table changes its file format"? – Laurenz Albe Oct 1 '19 at 2:56
  • I'm altering the storage engine and the file format changed without an explicit query run. – EvyLion Oct 1 '19 at 10:43
  • Show us the actual ALTER statement used in each case. – Rick James Oct 11 '19 at 1:29
0

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.

  • I'm not sure if I understand this correctly -- I understand that MySQL performs a full table rebuild when altering the storage engine. What is not clear is why would MySQL v. 5.6 change the row format back to compressed from dynamic in this case? Also, one of the tables was changed from dynamic to compact. Thank you. :) – EvyLion Oct 1 '19 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.