[
https://issues.apache.org/jira/browse/RANGER-5002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17899353#comment-17899353
]
Mahalingam commented on RANGER-5002:
------------------------------------
This exception was thrown whilst executing the view creation:
[https://github.com/apache/ranger/blob/ranger-2.5/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql#L1903|https://urldefense.com/v3/__https:/github.com/apache/ranger/blob/ranger-2.5/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql*L1903__;Iw!!JUyETn1neQ!8gDHTHtDLLceG-p-8fJFrds7nVYLHvjQU5C75AeJLmnUJZMJ1h43O64fRKdQXIbDcrot3cva1lPOZ0GZQNLExA$]
The view vx_principal is created as
{code:java}
SELECT col,col, UNION SELECT col,col UNION SELECT col, col.{code}
{code:java}
CREATE VIEW vx_principal as
(SELECT u.user_name AS principal_name, 0 AS principal_type, u.status status,
u.is_visible is_visible, u.other_attributes other_attributes, u.create_time
create_time, u.update_time update_time, u.added_by_id added_by_id, u.upd_by_id
upd_by_id FROM x_user u) UNION
(SELECT g.group_name principal_name, 1 AS principal_type, g.status status,
g.is_visible is_visible, g.other_attributesother_attributes, g.create_time
create_time, g.update_time update_time, g.added_by_id added_by_id, g.upd_by_id
upd_by_id FROM x_group g) UNION
(SELECT r.name principal_name, 2 AS principal_name, 1 status, 1 is_visible,
null other_attributes, r.create_time create_time, r.update_time update_time,
r.added_by_id added_by_id, r.upd_by_id upd_by_id FROM x_role r);
{code}
Of these 3 tables, other_attributes column is defined as a CLOB datatype in
x_user & x_group. Their table definition can be found
[https://github.com/apache/ranger/blob/ranger-2.5/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql#L477|https://urldefense.com/v3/__https:/github.com/apache/ranger/blob/ranger-2.5/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql*L477__;Iw!!JUyETn1neQ!8gDHTHtDLLceG-p-8fJFrds7nVYLHvjQU5C75AeJLmnUJZMJ1h43O64fRKdQXIbDcrot3cva1lPOZ0EoOo_EwQ$]
&
[https://github.com/apache/ranger/blob/ranger-2.5/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql#L441|https://urldefense.com/v3/__https:/github.com/apache/ranger/blob/ranger-2.5/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql*L441__;Iw!!JUyETn1neQ!8gDHTHtDLLceG-p-8fJFrds7nVYLHvjQU5C75AeJLmnUJZMJ1h43O64fRKdQXIbDcrot3cva1lPOZ0GVVy4pGg$]
Whilst executing the select statement on sqlplus, we could get the same issue.
The schema objects, like sequence, tables & indexes got created successfully.,
before the exception was thrown.
SQL*Plus: Release 23.0.0.0.0 - Production on Mon Nov 11 09:07:52 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Last Successful login time: Mon Nov 11 2024 09:02:31 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
SQL> (SELECT u.user_name AS principal_name, 0 AS principal_type, u.status
status, u.is_visible is_visible, u.other_attributes other_attributes,
u.create_time create_time, u.update_time update_time, u.added_by_id
added_by_id, u.upd_by_id upd_by_id FROM x_user u) UNION
(SELECT g.group_name principal_name, 1 AS principal_type, g.status status,
g.is_visible is_visible, g.other_attributes other_attributes, g.create_time
create_time, g.update_time update_time, g.added_by_id added_by_id, g.upd_by_id
upd_by_id FROM x_group g) UNION
(SELECT r.name principal_name, 2 AS principal_name, 1 status, 1 is_visible,
null other_attributes, r.create_time create_time, r.update_ 2 time
update_time, r.added_by_id added_by_id, r.upd_by_id upd_by_id FROM x_role r);
3
(SELECT u.user_name AS principal_name, 0 AS principal_type, u.status status,
u.is_visible is_visible, u.other_attributes other_attributes, u.create_time
create_time, u.update_time update_time, u.added_by_id added_by_id, u.upd_by_id
upd_by_id FROM x_user u) UNION
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
Help:
[https://docs.oracle.com/error-help/db/ora-00932/|https://urldefense.com/v3/__https:/docs.oracle.com/error-help/db/ora-00932/__;!!JUyETn1neQ!8gDHTHtDLLceG-p-8fJFrds7nVYLHvjQU5C75AeJLmnUJZMJ1h43O64fRKdQXIbDcrot3cva1lPOZ0F1IwjuHA$]
As we were stuck with the installation, We have made changes to the
vx_principal object creation. We have changed UNION to UNION ALL. Have
proceeded with the installation of the Ranger. Please review this, Hopefully
this change makes sense, and the evidences produced here are enough to make
this fix part of the Ranger.
> Installation with Oracle failed during schema vx_principal creation
> -------------------------------------------------------------------
>
> Key: RANGER-5002
> URL: https://issues.apache.org/jira/browse/RANGER-5002
> Project: Ranger
> Issue Type: Bug
> Components: admin
> Affects Versions: 2.5.0
> Reporter: Mahalingam
> Priority: Major
>
> During the installation of the apache Ranger we got the following exception:
> Environment Setup: Apache Ranger 2.5.0
> Database: Oracle 19c
>
>
> {code:java}
> 2024-11-09 23:25:46,863 [JISQL] /usr/lib/jvm/java-17-openjdk-amd64/bin/java
> -Djava.security.egd=file:///dev/urandom -cp
> /usr/share/java/ojdbc10.jar:/opt/ranger/ranger-2.5.0-admin/jisql/lib/*
> org.apache.util.sql.Jisql -driver oraclethin -cstring
> jdbc:oracle:thin:@//***:1521/*** -u 'usename' -p '********' -noheader -trim
> -input
> /opt/ranger/ranger-2.5.0admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
> -c \;
> SQLException : SQL state: 42000 java.sql.SQLSyntaxErrorException: ORA-00932:
> inconsistent datatypes: expected - got CLOB ErrorCode: 932
> SQLException : SQL state: 42000 java.sql.SQLSyntaxErrorException: ORA-00932:
> inconsistent datatypes: expected - got CLOB ErrorCode: 932
>
>
> 2024-11-09 23:25:51,191 [E] ranger_core_db_oracle.sql file import failed!
>
>
> 2024-11-09 23:25:51,192 [I] Unable to create DB schema, Please drop the
> database and try again
>
> 2024-11-09 23:25:51,192 [JISQL] /usr/lib/jvm/java-17-openjdk-amd64/bin/java
> -Djava.security.egd=file:///dev/urandom -cp
> /usr/share/java/ojdbc10.jar:/opt/ranger/ranger-2.5.0-admin/jisql/lib/*
> org.apache.util.sql.Jisql -driver oraclethin -cstring
> jdbc:oracle:thin:@//**:1521/**** -u 'username' -p '********' -noheader -trim
> -c \; -query "delete from x_db_version_h where version = 'CORE_DB_SCHEMA'
> and active = 'N' and updated_b y=***';"
>
> 2024-11-09 23:25:52,978 [E] CORE_DB_SCHEMA import failed!
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
