Ranger Admin installation is failing in HDP-3.0.1.0 (latest version).

[#1]

Hi,

I'm unable to install Ranger Admin for the latest HDP version 3.0.1.0 (Latest)

MySQL Version -->

mysql Ver 15.1 Distrib 10.2.16-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Error encountered:

Error executing: INSERT INTO x_portal_user_role(create_time,update_time,added_by_id,upd_by_id,user_id,user_role,status) VALUES (UTC_TIMESTAMP(),UTC_TIMESTAMP(),NULL,NULL,2,'ROLE_SYS_ADMIN',1);

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ranger`.`x_portal_user_role`, CONSTRAINT `x_portal_user_role_FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `x_portal_user` (`id`))

SQLException : SQL state: 23000 com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ranger`.`x_portal_user_role`, CONSTRAINT `x_portal_user_role_FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `x_portal_user` (`id`)) ErrorCode: 1452

2018-10-25 02:11:24,781[E] ranger_core_db_mysql.sql file import failed!

*************************************************************************

Error executing: call insert_public_group_in_x_group_table();

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ranger`.`x_group`, CONSTRAINT `x_group_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`))

SQLException : SQL state: 23000 com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ranger`.`x_group`, CONSTRAINT `x_group_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`)) ErrorCode: 1452

2018-10-25 02:13:39,667[JISQL] /usr/jdk64/jdk1.8.0_112/bin/java-cp /usr/hdp/current/ranger-admin/ews/lib/mysql.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://prd-rangervip01.xxxx.nm1/ranger -u 'ranger' -p '********' -noheader -trim -c \; -query "select version from x_db_version_h where version = '006' and active = 'Y';"

2018-10-25 02:13:40,120[JISQL] /usr/jdk64/jdk1.8.0_112/bin/java-cp /usr/hdp/current/ranger-admin/ews/lib/mysql.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://prd-rangervip01.xxxx.nm1/ranger -u 'ranger' -p '********' -noheader -trim -c \; -query "delete from x_db_version_h where version='006' and active='N' and updated_by='prd-xxx115.xxx.nm1';"

2018-10-25 02:13:40,526[E] 006-createdefaultpublicgroup.sql import failed!

*************************************************************************

Error executing: INSERT INTO x_portal_user_role(create_time,update_time,added_by_id,upd_by_id,user_id,user_role,status) VALUES (UTC_TIMESTAMP(),UTC_TIMESTAMP(),NULL,NULL,2,'ROLE_SYS_ADMIN',1);

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ranger`.`x_portal_user_role`, CONSTRAINT `x_portal_user_role_FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `x_portal_user` (`id`))

SQLException : SQL state: 23000 com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ranger`.`x_portal_user_role`, CONSTRAINT `x_portal_user_role_FK_user_id` FOREIGN KEY (`user_id`) REFERENCES `x_portal_user` (`id`)) ErrorCode: 1452

2018-10-25 02:11:24,781[E] ranger_core_db_mysql.sql file import failed!

*************************************************************************

Error executing: call insert_public_group_in_x_group_table();

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ranger`.`x_group`, CONSTRAINT `x_group_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`))

SQLException : SQL state: 23000 com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`ranger`.`x_group`, CONSTRAINT `x_group_FK_added_by_id` FOREIGN KEY (`added_by_id`) REFERENCES `x_portal_user` (`id`)) ErrorCode: 1452

2018-10-25 02:13:39,667[JISQL] /usr/jdk64/jdk1.8.0_112/bin/java-cp /usr/hdp/current/ranger-admin/ews/lib/mysql.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://prd-rangervip01.xxxx.nm1/ranger -u 'ranger' -p '********' -noheader -trim -c \; -query "select version from x_db_version_h where version = '006' and active = 'Y';"

2018-10-25 02:13:40,120[JISQL] /usr/jdk64/jdk1.8.0_112/bin/java-cp /usr/hdp/current/ranger-admin/ews/lib/mysql.jar:/usr/hdp/current/ranger-admin/jisql/lib/* org.apache.util.sql.Jisql -driver mysqlconj -cstring jdbc:mysql://pprd-rangervip01.xxxx.nm1/ranger -u 'ranger' -p '********' -noheader -trim -c \; -query "delete from x_db_version_h where version='006' and active='N' and updated_by='prd-xxx115.xxx.nm1';"

2018-10-25 02:13:40,526[E] 006-createdefaultpublicgroup.sql import failed!

I can see that few .sql imports are FAILED here. I have also followed the correct steps to be executed in MySQL:

Referred URLs:

1. https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.4/bk_security/content/configuring_mysql_for_r...

2. https://community.hortonworks.com/questions/214821/cant-install-ranger.html

Please provide any technical suggestions to resolve this issue.

Thanks,

Shesh Kumar

[#2]

This is probably similar to https://issues.apache.org/jira/browse/RANGER-2234.

Please set auto increment value in your mysql conf to 1.

[#3]

Thank you so much for your suggestion. However, I just happen to resolve this issue. Below I've shared my resolution. Please check and let me know what you think about it 🙂

If I face this situation again. Will try your suggestion the next time.

[#4]

I somehow made it worked.

Here's what I did:

I added the following lines in the beginning and end of ALL the .sql files present in the path:

"/usr/hdp/3.0.1.0-187/ranger-admin/db/mysql/patches/"

and

"/usr/hdp/3.0.1.0-187/ranger-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql"

SET FOREIGN_KEY_CHECKS=0; (Beginning of the .sql file)
SET FOREIGN_KEY_CHECKS=1; (End of the .sql file)

It takes lot of time to edit, add the lines and save and make sure you do this on host where Ranger was planned to installed

Post this, delete the 'ranger' DB in MySQL, delete Ranger Service and again install Ranger in Ambari on same host where the .sql files are present that was edited.

Thanks,

Shesh Kumar

[#5]

Hi @Shesh Kumar, The issue seems probably related to using a different auto-increment being used than the one which is set to default in the database, hence recommend to use the auto_increment property instead of the foreign_key_check property for the database.

Instead of using set FOREIGN_KEY_CHECKS=0; you can use set AUTO_INCREMENT=1; in the sql file.

you will get the same result.

[#6]

Thank you! Will surely check the recommendation next time.