Bug 1207575

Summary: Zanata still creates MyISAM (not InnoDB) tables in some cases
Product: [Retired] Zanata Reporter: Sean Flanigan <sflaniga>
Component: DatabaseChange, PerformanceAssignee: Sean Flanigan <sflaniga>
Status: CLOSED CURRENTRELEASE QA Contact: Damian Jansen <djansen>
Severity: high Docs Contact:
Priority: unspecified    
Version: 3.6CC: dchen, zanata-bugs
Target Milestone: ---   
Target Release: 3.7   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: 3.7.0-SNAPSHOT Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2015-07-22 02:19:35 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:

Description Sean Flanigan 2015-03-31 08:48:07 UTC
Description of problem:

The file db.changelog-1.0.xml includes a couple of changesets which attempt to prevent Zanata from creating MyISAM tables by accident, but they don't seem to be working in all cases (recent versions of mysql?).  Since Zanata is supposed to be using InnoDB, this can lead to all sorts of transaction consistency and performance bugs.

This sql can be used to show the MyISAM tables in a schema:
  SELECT TABLE_NAME FROM information_schema.TABLES WHERE ENGINE='MyISAM' and TABLE_SCHEMA='zanata';

On my machine with a fairly recent production backup (March 2015), multiple tables are showing as MyISAM:

	Activity
	DATABASECHANGELOG
	DATABASECHANGELOGLOCK
	HAccountOption
	HDocumentUpload
	HDocumentUploadPart
	HDocument_RawDocument
	HPersonEmailValidationKey
	HProjectIteration_Validation
	HProject_Validation
	HRawDocument
	HRoleAssignmentRule
	HTextFlowTargetReviewComment
	IterationGroup_Locale
	TransMemory
	TransMemoryUnit
	TransMemoryUnitVariant
	TransMemory_Metadata

I think we need to revisit the way we ensure tables are created as InnoDB in Liquibase.  One option is modifySql: http://www.liquibase.org/documentation/modify_sql.html  A cleaner solution is probably a custom CreateTableGenerator, as suggested here: http://blog.liquibase.org/2011/01/liquibase-2-0-officially-released.html

We also need to run another changeset to convert any potentially affected tables to InnoDB.  See the original fix for an example of this: https://github.com/zanata/zanata-server/commit/45d8e57e7476a1146e72abea0011f657555cbb75

Comment 1 Sean Flanigan 2015-04-13 06:16:40 UTC
Pull request: https://github.com/zanata/zanata-server/pull/773

Comment 2 Damian Jansen 2015-05-12 05:20:06 UTC
Verified (master) at c36663118b0fb358c8b1cd048014e14788dd6c5c