VerifyMySQL() alters non Liferay tables

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 6.1.1 CE GA2, 6.1.20 EE GA2
• Fix Version/s: 6.1.20 EE GA2, 6.2.0 CE M2
• Component/s: Upgrades
• Labels: None
• Environment: Upgrade process in using MySQL as database engine.
• Enterprise Requirement:
  Enterprise Requirement
• Similar Issues: Show 5 results ; ;

  LPS-27096	Non-Liferay tables are upgraded
  LPS-29479	Non-Liferay tables upgraded on Oracle
  LPS-12073	Mysql errors on db creation
  LPS-7994	Social Activity table create time field has changed from DATE to LONG and update scripts missing database update
  LPS-7772	Attempting to verify a MySQL table status whose engine column is null results in an error

During upgrading, VerifyMySQL() atlers all table structures including non-Liferay tables.

Although it is not a technical bug, it is a logical consideration.

In real life, we make use of MySQL tables as "Memory type" for performance.
Memory: Stores all data in RAM for extremely fast access in environments that require quick lookups of reference and other like data.
References: http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

com.liferay.portal.verify.VerifyMySQL does not consider to allow different storage types within a database.

Sources: /portal-impl/src/com/liferay/portal/verify
line 72-87:
con = DataAccess.getConnection();

ps = con.prepareStatement("show table status");

rs = ps.executeQuery();

while (rs.next()) {
String tableName = rs.getString("Name");
String engine = GetterUtil.getString(rs.getString("Engine"));

if (!engine.equalsIgnoreCase(
PropsValues.DATABASE_MYSQL_ENGINE))

{ alterTableEngine(tableName); }

}

line 39-40
"Updating table " + tableName + " to use engine " +
PropsValues.DATABASE_MYSQL_ENGINE);

[Shepherd Ching]

One suggestion is adding where-clause statement into "show table status"

Original statement of VerifyMySQL.java
line 74: ps = con.prepareStatement("show table status where engine not in ('memory','null')");

Hence we filter out "Memory" and "View", then it processes "InnoDB" and "MyISAM" only