You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ranger.apache.org by di...@apache.org on 2024/10/28 10:21:47 UTC

(ranger) branch ranger-2.6 updated: RANGER-4964: Issue with x_trx_log_IDX_trx_id Index in x_trx_log Table, causing patch failure

This is an automated email from the ASF dual-hosted git repository.

dineshkumar pushed a commit to branch ranger-2.6
in repository https://gitbox.apache.org/repos/asf/ranger.git


The following commit(s) were added to refs/heads/ranger-2.6 by this push:
     new a4b276adc RANGER-4964: Issue with x_trx_log_IDX_trx_id Index in x_trx_log Table, causing patch failure
a4b276adc is described below

commit a4b276adcda875989ef787b13bc193af00f4aad2
Author: Rakesh Gupta <ra...@gmail.com>
AuthorDate: Wed Oct 23 17:08:58 2024 +0530

    RANGER-4964: Issue with x_trx_log_IDX_trx_id Index in x_trx_log Table, causing patch failure
    
    Signed-off-by: Dineshkumar Yadav <di...@outlook.com>
---
 .../db/mysql/patches/074-create-index-for-x_trx_log.sql   |  8 +++++---
 .../db/oracle/optimized/current/ranger_core_db_oracle.sql |  2 +-
 ...r-x_trx_log.sql => 074-create-index-for-x_trx_log.sql} | 12 ++++++++----
 .../postgres/patches/074-create-index-for-x_trx_log.sql   | 12 ++++++++----
 .../optimized/current/ranger_core_db_sqlanywhere.sql      |  4 +---
 ...r-x_trx_log.sql => 074-create-index-for-x_trx_log.sql} |  6 +++++-
 .../optimized/current/ranger_core_db_sqlserver.sql        |  2 +-
 ...r-x_trx_log.sql => 074-create-index-for-x_trx_log.sql} | 15 +++++++++------
 8 files changed, 38 insertions(+), 23 deletions(-)

diff --git a/security-admin/db/mysql/patches/074-create-index-for-x_trx_log.sql b/security-admin/db/mysql/patches/074-create-index-for-x_trx_log.sql
index 9a8f3c0d2..9458c56e7 100644
--- a/security-admin/db/mysql/patches/074-create-index-for-x_trx_log.sql
+++ b/security-admin/db/mysql/patches/074-create-index-for-x_trx_log.sql
@@ -17,9 +17,11 @@ drop procedure if exists create_index_for_x_trx_log;
 
 delimiter ;;
 create procedure create_index_for_x_trx_log() begin
-if not exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_trx_log' AND index_name='x_trx_log_IDX_trx_id') then
-	CREATE INDEX x_trx_log_IDX_trx_id ON x_trx_log(trx_id);
- end if;
+if exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_trx_log') then
+	if not exists (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='x_trx_log' AND index_name='x_trx_log_IDX_trx_id') then
+		CREATE INDEX x_trx_log_IDX_trx_id ON x_trx_log(trx_id);
+	end if;
+end if;
 end;;
 
 delimiter ;
diff --git a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
index 14b1cdb9f..7912eeceb 100644
--- a/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
+++ b/security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql
@@ -1992,11 +1992,11 @@ INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,act
 INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '060',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
 INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '065',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
 INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '066',sys_extract_utc(systimestamp),'Ranger 2.5.0',sys_extract_utc(systimestamp),'localhost','Y');
-INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '067',sys_extract_utc(systimestamp),'Ranger 2.5.0',sys_extract_utc(systimestamp),'localhost','Y');
 INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '068',sys_extract_utc(systimestamp),'Ranger 2.5.0',sys_extract_utc(systimestamp),'localhost','Y');
 INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '069',sys_extract_utc(systimestamp),'Ranger 2.5.0',sys_extract_utc(systimestamp),'localhost','Y');
 INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '072',sys_extract_utc(systimestamp),'Ranger 2.5.0',sys_extract_utc(systimestamp),'localhost','Y');
 INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '073',sys_extract_utc(systimestamp),'Ranger 2.5.0',sys_extract_utc(systimestamp),'localhost','Y');
+INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, '074',sys_extract_utc(systimestamp),'Ranger 2.5.0',sys_extract_utc(systimestamp),'localhost','Y');
 INSERT INTO x_db_version_h (id,version,inst_at,inst_by,updated_at,updated_by,active) VALUES (X_DB_VERSION_H_SEQ.nextval, 'DB_PATCHES',sys_extract_utc(systimestamp),'Ranger 1.0.0',sys_extract_utc(systimestamp),'localhost','Y');
 
 INSERT INTO x_user_module_perm (id,user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (X_USER_MODULE_PERM_SEQ.nextval,getXportalUIdByLoginId('admin'),getModulesIdByName('Reports'),sys_extract_utc(systimestamp),sys_extract_utc(systimestamp),getXportalUIdByLoginId('admin'),getXportalUIdByLoginId('admin'),1);
diff --git a/security-admin/db/oracle/patches/067-create-index-for-x_trx_log.sql b/security-admin/db/oracle/patches/074-create-index-for-x_trx_log.sql
similarity index 67%
rename from security-admin/db/oracle/patches/067-create-index-for-x_trx_log.sql
rename to security-admin/db/oracle/patches/074-create-index-for-x_trx_log.sql
index 1ef726b53..5a813f091 100644
--- a/security-admin/db/oracle/patches/067-create-index-for-x_trx_log.sql
+++ b/security-admin/db/oracle/patches/074-create-index-for-x_trx_log.sql
@@ -16,10 +16,14 @@
 
 DECLARE
 	v_index_exists number:=0;
+	v_table_exists number := 0;
 BEGIN
-	SELECT COUNT(*) INTO v_index_exists FROM USER_INDEXES WHERE INDEX_NAME = upper('x_trx_log_IDX_trx_id') AND TABLE_NAME= upper('x_trx_log');
-	IF (v_index_exists = 0) THEN
-		execute IMMEDIATE 'CREATE INDEX x_trx_log_IDX_trx_id ON x_trx_log(trx_id)';
-		commit;
+	SELECT COUNT(*) INTO v_table_exists FROM USER_TABLES WHERE TABLE_NAME = upper('x_trx_log');
+	IF (v_table_exists > 0) THEN
+		SELECT COUNT(*) INTO v_index_exists FROM USER_INDEXES WHERE INDEX_NAME = upper('x_trx_log_IDX_trx_id') AND TABLE_NAME= upper('x_trx_log');
+		IF (v_index_exists = 0) THEN
+			execute IMMEDIATE 'CREATE INDEX x_trx_log_IDX_trx_id ON x_trx_log(trx_id)';
+			commit;
+		END IF;
 	END IF;
 END;/
diff --git a/security-admin/db/postgres/patches/074-create-index-for-x_trx_log.sql b/security-admin/db/postgres/patches/074-create-index-for-x_trx_log.sql
index 34cf77ccc..2e6352a91 100644
--- a/security-admin/db/postgres/patches/074-create-index-for-x_trx_log.sql
+++ b/security-admin/db/postgres/patches/074-create-index-for-x_trx_log.sql
@@ -18,11 +18,15 @@ CREATE OR REPLACE FUNCTION create_index_for_x_trx_log()
 RETURNS void AS $$
 DECLARE
 	v_attnum1 integer := 0;
+	v_table_exists integer := 0;
 BEGIN
-	select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_trx_log') and attname in('trx_id');
-	IF v_attnum1 > 0 THEN
-		IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_trx_log') and indkey[0]=v_attnum1) THEN
-			CREATE INDEX x_trx_log_IDX_trx_id ON x_trx_log(trx_id);
+	SELECT COUNT(*) INTO v_table_exists FROM pg_class WHERE relname = 'x_trx_log';
+	IF v_table_exists > 0 THEN
+		select attnum into v_attnum1 from pg_attribute where attrelid in(select oid from pg_class where relname='x_trx_log') and attname in('trx_id');
+		IF v_attnum1 > 0 THEN
+			IF not exists (select * from pg_index where indrelid in(select oid from pg_class where relname='x_trx_log') and indkey[0]=v_attnum1) THEN
+				CREATE INDEX x_trx_log_IDX_trx_id ON x_trx_log(trx_id);
+			END IF;
 		END IF;
 	END IF;
 END;
diff --git a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
index 649a0b250..9c0551317 100644
--- a/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
+++ b/security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql
@@ -2297,9 +2297,7 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
 GO
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('073',CURRENT_TIMESTAMP,'Ranger 2.5.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
-INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('067',CURRENT_TIMESTAMP,'Ranger 3.0.0',CURRENT_TIMESTAMP,'localhost','Y');
-GO
-INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('068',CURRENT_TIMESTAMP,'Ranger 3.0.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('074',CURRENT_TIMESTAMP,'Ranger 3.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 GO
diff --git a/security-admin/db/sqlanywhere/patches/067-create-index-for-x_trx_log.sql b/security-admin/db/sqlanywhere/patches/074-create-index-for-x_trx_log.sql
similarity index 76%
rename from security-admin/db/sqlanywhere/patches/067-create-index-for-x_trx_log.sql
rename to security-admin/db/sqlanywhere/patches/074-create-index-for-x_trx_log.sql
index ae76a6de9..d4d39c8c2 100644
--- a/security-admin/db/sqlanywhere/patches/067-create-index-for-x_trx_log.sql
+++ b/security-admin/db/sqlanywhere/patches/074-create-index-for-x_trx_log.sql
@@ -13,7 +13,11 @@
 -- See the License for the specific language governing permissions and
 -- limitations under the License.
 
-CREATE INDEX IF NOT EXISTS x_trx_log_IDX_trx_id ON x_trx_log(trx_id);
+IF EXISTS(select * from SYS.SYSTABLES where tname = 'x_trx_log') THEN
+	IF EXISTS(select * from SYS.SYSCOLUMNS where tname = 'x_trx_log' and cname = 'trx_id') THEN
+		CREATE INDEX IF NOT EXISTS x_trx_log_IDX_trx_id ON x_trx_log(trx_id);
+	END IF;
+END IF;
 GO
 
 EXIT
diff --git a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
index 9466f2783..8aea57bea 100644
--- a/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
+++ b/security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql
@@ -4153,11 +4153,11 @@ INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('060',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('065',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('066',CURRENT_TIMESTAMP,'Ranger 2.5.0',CURRENT_TIMESTAMP,'localhost','Y');
-INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('067',CURRENT_TIMESTAMP,'Ranger 2.6.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('068',CURRENT_TIMESTAMP,'Ranger 2.5.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('069',CURRENT_TIMESTAMP,'Ranger 2.5.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('072',CURRENT_TIMESTAMP,'Ranger 2.5.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('073',CURRENT_TIMESTAMP,'Ranger 2.5.0',CURRENT_TIMESTAMP,'localhost','Y');
+INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('074',CURRENT_TIMESTAMP,'Ranger 2.6.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_db_version_h (version,inst_at,inst_by,updated_at,updated_by,active) VALUES ('DB_PATCHES',CURRENT_TIMESTAMP,'Ranger 1.0.0',CURRENT_TIMESTAMP,'localhost','Y');
 INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Reports'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
 INSERT INTO x_user_module_perm (user_id,module_id,create_time,update_time,added_by_id,upd_by_id,is_allowed) VALUES (dbo.getXportalUIdByLoginId('admin'),dbo.getModulesIdByName('Resource Based Policies'),CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,dbo.getXportalUIdByLoginId('admin'),dbo.getXportalUIdByLoginId('admin'),1);
diff --git a/security-admin/db/sqlserver/patches/067-create-index-for-x_trx_log.sql b/security-admin/db/sqlserver/patches/074-create-index-for-x_trx_log.sql
similarity index 69%
rename from security-admin/db/sqlserver/patches/067-create-index-for-x_trx_log.sql
rename to security-admin/db/sqlserver/patches/074-create-index-for-x_trx_log.sql
index 3efb9eebb..3db055a8c 100644
--- a/security-admin/db/sqlserver/patches/067-create-index-for-x_trx_log.sql
+++ b/security-admin/db/sqlserver/patches/074-create-index-for-x_trx_log.sql
@@ -13,13 +13,16 @@
 -- See the License for the specific language governing permissions and
 -- limitations under the License.
 GO
-IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'x_trx_log_IDX_trx_id' AND object_id = OBJECT_ID('x_trx_log'))
+IF OBJECT_ID('x_trx_log') IS NOT NULL
 BEGIN
-	CREATE NONCLUSTERED INDEX [x_trx_log_IDX_trx_id] ON [x_trx_log]
-	(
-		[trx_id] ASC
-	)
-	WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
+	IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = 'x_trx_log_IDX_trx_id' AND object_id = OBJECT_ID('x_trx_log'))
+	BEGIN
+		CREATE NONCLUSTERED INDEX [x_trx_log_IDX_trx_id] ON [x_trx_log]
+		(
+			[trx_id] ASC
+		)
+		WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
+	END
 END
 Go