Ambari upgrade command gives error as Error Code: 1005. Can't create table 'ambaridb.#sql-4168_34d2' (errno: 150)

[#1]

SYMPTOM: Ambari upgrade command gives error as Error Code: 1005. Can't create table 'ambaridb.#sql-4168_34d2' (errno: 150)

ERROR:

CREATE TABLE blueprint_setting (id BIGINT NOT NULL, blueprint_name VARCHAR(255) NOT NULL, setting_name VARCHAR(255) NOT NULL, setting_data LONGTEXT NOT NULL)
03 Oct 2016 09:30:06,326 INFO [main] DBAccessorImpl:824 - Executing query: ALTER TABLE blueprint_setting ADD CONSTRAINT PK_blueprint_setting PRIMARY KEY (id) 
03 Oct 2016 09:30:06,388 INFO [main] DBAccessorImpl:824 - Executing query: ALTER TABLE blueprint_setting ADD CONSTRAINT UQ_blueprint_setting_name UNIQUE (blueprint_name, setting_name) 
03 Oct 2016 09:30:06,489 INFO [main] DBAccessorImpl:824 - Executing query: ALTER TABLE blueprint_setting ADD CONSTRAINT FK_blueprint_setting_name FOREIGN KEY (blueprint_name) REFERENCES blueprint (blueprint_name) 
03 Oct 2016 09:30:06,545 ERROR [main] DBAccessorImpl:830 - Error executing query: ALTER TABLE blueprint_setting ADD CONSTRAINT FK_blueprint_setting_name FOREIGN KEY (blueprint_name) REFERENCES blueprint (blueprint_name) 
java.sql.SQLException: Can't create table 'ambaridb.#sql-4168_34e3' (errno: 150) 


SHOW ENGINE INNODB STATUS; 
command shows below details, 


------------------------ 
LATEST FOREIGN KEY ERROR 
------------------------ 
161003 9:30:06 Error in foreign key constraint of table ambaridb/#sql-4168_34e3: 
FOREIGN KEY (blueprint_name) REFERENCES blueprint (blueprint_name): 
Cannot find an index in the referenced table where the 
referenced columns appear as the first columns, or column types 
in the table and the referenced table do not match for constraint. 
Note that the internal storage type of ENUM and SET changed in 
tables created with >= InnoDB-4.1.12, and such columns in old tables 
cannot be referenced by such columns in new tables. 
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html 
for correct foreign key definition.

ROOT CAUSE:

===

The issue was related to a mismatch on the character sets of the tables in MySQL.

When Ambari initially created the database it set the tables to use UTF8. During the upgrade, new tables were created via the UpgradeCatalog classes set to use the LATIN1 character set. More specifically, the blueprint was created setting the character set to UTF8 and the blueprint_setting table was created using the LATIN1 character set.

----

Below was seen using a query like:

SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "ambari" 
AND table_name = "blueprint"; 


SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "ambari" 
AND table_name = "blueprint_setting"; 

RESOLUTION: This was fixed by dropping the blueprint_setting table and manually creating table using below syntax - [using - CHARACTER SET as utf8]

CREATE TABLE blueprint_setting ( 
id BIGINT NOT NULL, 
blueprint_name VARCHAR(100) NOT NULL, 
setting_name VARCHAR(100) NOT NULL, 
setting_data MEDIUMTEXT NOT NULL, 
CONSTRAINT PK_blueprint_setting PRIMARY KEY (id), 
CONSTRAINT UQ_blueprint_setting_name UNIQUE(blueprint_name,setting_name), 
CONSTRAINT FK_blueprint_setting_name FOREIGN KEY (blueprint_name) REFERENCES blueprint(blueprint_name)) 
CHARACTER SET utf8;