Migrating Ambari database from MyISAM to InnoDB

[#1]

Problem summary:

Users can end up in a situation where the SQL Engine for all Ambari tables is set to MyISAM instead of InnoDB which is required for Ambari. There are few ways to land up in this situation. Prior to MySQL version 5.5.5 the default engine was MyISAM. It could also be a configured default as a global default in my.cnf which affected Ambari versions before 2.5. AMBARI-18951 addressed this issue by making InnoDB default for new databases (2.5+ Ambari deployments) as well as throwing an explicit error preventing Ambari upgrade if the wrong engine is set in order to avoid landing up in an intermediate non-upgraded state.

Following is an sample error that would be encountered on an upgrade path from 2.2.2 to 2.4.2

ERROR [main] DBAccessorImpl:830 - Error executing query: ALTER TABLE hostcomponentdesiredstate ADD CONSTRAINT hstcmpnntdesiredstatecmpnntnme FOREIGN KEY (component_name, service_name, cluster
_id) REFERENCES servicecomponentdesiredstate (component_name, service_name, cluster_id)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'hstcmpnntdesiredstatecmpnntnme'

Reason for failure:

When MyISAM is chosen as database engine FK create statements doesn't create any object in DB which describes FK, but creates corresponding index. This is the reason of failure, the Ambari UpgradeCatalogs check existence of FK which is absent, but there is an index with same name. Also there is an issue with migration. Because there is no information about FK stored in DB, a simple engine change won't fix this problem.

Suggested approach:

MySQL docs suggest steps for migration from MyISAM to InnoDB by creating a new InnoDB table and doing a select and insert. Although, this sounds doable for 106 tables it needs renaming of all Foreign keys for new tables to prevent collision with existing objects in the database and then again renamed back after dropping old tables since this will be required for smooth upgrades of Ambari in the future. Instead the approach described below takes a more direct but simpler approach and avoids any hand edits of the DDL to make the migration. The recommendation is to create a blank schema using DDL script and insert data using the db dump.

Note 1: This has been tried and tested on 2.2.2 Ambari version and upgrading to 2.4.2. Although the approach and steps are generic enough to apply to any 2.X version and upwards.

Note 2: If you are in the middle of upgrade and hit this error related to the storage engine, please make sure to revert back to the last backup of the database as well as the ambari bits (yum donwgrade)

Steps:

1. Edit /var/lib/ambari-server/Ambari-DDL-MySQL-CREATE.sql from your ambari-server host to add the following lines that will set the appropriate engine on re-creating the database. These should be added right at the beginning of the file.

-- Set default_storage_engine to InnoDB
-- storage_engine variable should be used for versions prior to MySQL 5.6
set @version_short = substring_index(@@version, '.', 2);
set @major = cast(substring_index(@version_short, '.', 1) as SIGNED);
set @minor = cast(substring_index(@version_short, '.', -1) as SIGNED);
set @engine_stmt = IF(@major >= 5 AND @minor>=6, 'SET default_storage_engine=INNODB', 'SET storage_engine=INNODB');
prepare statement from @engine_stmt;
execute statement;
DEALLOCATE PREPARE statement;

2. Take a backup of your good working condition non-upgraded Ambari database, lets call it, dbdump.sql

3. Create the INSERT only SQL, lets call it insert_final.sql without the CREATE statements from your dbdump file. (Optional) Purge the dbdump.sql of historical information to make the database dump of manageable size, getting rid of historical items. Use the following cat / grep statement to achieve this:

cat dbdump.sql | grep "INSERT INTO" | grep -v "INSERT INTO \`alert" | grep -v "INSERT INTO \`host_role_command\`" | grep -v "INSERT INTO \`execution_command\`" | grep -v "INSERT INTO \`request" | grep -v "INSERT INTO \`stage" | grep -v "INSERT INTO \`topology" | grep -v "INSERT INTO \`blueprint" | grep -v "INSERT INTO \'qrtz" | grep -v "INSERT INTO \`hostgroup" > insert_final.sql

(Without purge option use only the first grep statement)

4. Drop Ambari database and Recreate it using the /var/lib/ambari-server/Ambari-DDL-MySQL-CREATE.sql

mysql> drop database ambari;
mysql> create database ambari;
mysql> use ambari;
mysql> source /var/lib/ambari-server/Ambari-DDL-MySQL-CREATE.sql

5. Make sure the new storage engine is set to InnoDB

SELECT `ENGINE` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='ambari';

6. Add the following statements to the beginning of the insert_final.sql. These will delete the default INSERT(s) coming from the DDL script which would be correctly re-inserted from your db dump later on in the file. Without the deletes you would get duplicate constraint violations. The silencing of constraints allows out of order inserts without constraint violations.

SET unique_checks=0;
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM `adminpermission`;
DELETE FROM `adminprincipal`;
DELETE FROM `adminpermission`;
DELETE FROM `adminprincipaltype`;
DELETE FROM `adminprivilege`;
DELETE FROM `adminresource`;
DELETE FROM `adminresourcetype`;
DELETE FROM `viewentity`;
DELETE FROM `metainfo`;
DELETE FROM `users`;
DELETE FROM `ambari_sequences`;

7. Turn on the constraints at the end of the dump file. Append the following at the end of the insert_final.sql file.

SET unique_checks=1;
SET FOREIGN_KEY_CHECKS=1;

8. Execute the insert_final.sql on the ambari database.

mysql> use ambari;
mysql> source insert_final.sql

(These instruction should not fail with any error messages. If you get primary key constraint violations, it is in all likelyhood some seed data inserted by the DDL script. Just add the DELETE statement at the beginning of the file.)

9. With no errors on step 8, start the Ambari server and verify everything looks good and are no SQL errors in the server logs. If step 8 fails, make the minor adjustments and just rinse and repeat using Step 4 and Step 8 until there are no errors.

10. Proceed with Ambari upgrade to the new desired version.