You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ranger.apache.org by Kirby Zhou <ki...@gmail.com> on 2023/03/02 02:49:21 UTC
Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/74319/#review225236
-----------------------------------------------------------
security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql
Lines 19 (patched)
<https://reviews.apache.org/r/74319/#comment313971>
Can we avoid using stored procedures? TiDB does not support stored procedures.
- Kirby Zhou
On 二月 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> -----------------------------------------------------------
>
> (Updated 二月 23, 2023, 10:35 p.m.)
>
>
> Review request for ranger, Abhishek Kumar, Dineshkumar Yadav, madhan, Madhan Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
>
>
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
>
>
> Repository: ranger
>
>
> Description
> -------
>
> Currently, Service Definitions are persisted in several database tables in a normalized form. However, as operations on a Service Definition object is not granular, but act on the object as a whole, there is not much advantage in normalizing it for storage, and a distinct disadvantage of having to recreate it by accessing multiple database tables during the initialization of Ranger admin process.
>
> This Jira denormalizes service-definition object's persistent state and also removes several tables used only for normalizing service-definition object.
>
>
> Diffs
> -----
>
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java 8632dd6bc
> security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 9a79fe8ad
> security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql fd6cec9a7
> security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 4d5a8cedf
> security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql 3ed2a5b9c
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql ca8f7da1f
> security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 0e5388646
> security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 562467e80
> security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52
> security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 418557bcb
> security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java db1878a35
> security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 7f8d6193f
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java f5f9f8956
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java fb7e5fd40
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java 656bc0184
> security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java 85e457efa
> security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 691ab52b3
> security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 528f4e511
>
>
> Diff: https://reviews.apache.org/r/74319/diff/1/
>
>
> Testing
> -------
>
> Ran all unit tests successfully. Ran ranger-admin and component services in a cluster and verified they work correctly.
>
>
> Thanks,
>
> Abhay Kulkarni
>
>
Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup
Posted by Pradeep Agrawal <pr...@gmail.com>.
> On March 2, 2023, 2:49 a.m., Kirby Zhou wrote:
> > security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql
> > Lines 19 (patched)
> > <https://reviews.apache.org/r/74319/diff/1/?file=2274752#file2274752line19>
> >
> > Can we avoid using stored procedures? TiDB does not support stored procedures.
How the other old sql patches with stored procedure are working in TiDB.
Also if TiDB defers from mysql then TiDB support can be added seperately.
- Pradeep
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/74319/#review225236
-----------------------------------------------------------
On Feb. 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> -----------------------------------------------------------
>
> (Updated Feb. 23, 2023, 10:35 p.m.)
>
>
> Review request for ranger, Abhishek Kumar, Dineshkumar Yadav, madhan, Madhan Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
>
>
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
>
>
> Repository: ranger
>
>
> Description
> -------
>
> Currently, Service Definitions are persisted in several database tables in a normalized form. However, as operations on a Service Definition object is not granular, but act on the object as a whole, there is not much advantage in normalizing it for storage, and a distinct disadvantage of having to recreate it by accessing multiple database tables during the initialization of Ranger admin process.
>
> This Jira denormalizes service-definition object's persistent state and also removes several tables used only for normalizing service-definition object.
>
>
> Diffs
> -----
>
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java 8632dd6bc
> security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 9a79fe8ad
> security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql fd6cec9a7
> security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 4d5a8cedf
> security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql 3ed2a5b9c
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql ca8f7da1f
> security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 0e5388646
> security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 562467e80
> security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52
> security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 418557bcb
> security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java db1878a35
> security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 7f8d6193f
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java f5f9f8956
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java fb7e5fd40
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java 656bc0184
> security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java 85e457efa
> security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 691ab52b3
> security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 528f4e511
>
>
> Diff: https://reviews.apache.org/r/74319/diff/1/
>
>
> Testing
> -------
>
> Ran all unit tests successfully. Ran ranger-admin and component services in a cluster and verified they work correctly.
>
>
> Thanks,
>
> Abhay Kulkarni
>
>
Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup
Posted by Kirby Zhou <ki...@gmail.com>.
> On 三月 2, 2023, 2:49 a.m., Kirby Zhou wrote:
> > security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql
> > Lines 19 (patched)
> > <https://reviews.apache.org/r/74319/diff/1/?file=2274752#file2274752line19>
> >
> > Can we avoid using stored procedures? TiDB does not support stored procedures.
>
> Pradeep Agrawal wrote:
> How the other old sql patches with stored procedure are working in TiDB.
> Also if TiDB defers from mysql then TiDB support can be added seperately.
The old sql patches have the same problem……
TiDB is a 95% mysql-compatible NewSQL database. For legal reason, we have to deploy ranger based on tidb. But TiDB is missing some features, which makes ranger unable to install properly.
https://docs.pingcap.com/tidb/stable/mysql-compatibility#unsupported-features
The biggest problem affecting ranger is missing "Stored procedures and functions", "Select into".
ranger use Stored procedures in setup scripts to simplify SQL.
Some work is needed to remove the stored procedure.
Because few old versions of Ranger are using TiDb, we mainly focus on sql patch after ranger-2.3.
https://issues.apache.org/jira/browse/RANGER-3984
- Kirby
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/74319/#review225236
-----------------------------------------------------------
On 二月 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> -----------------------------------------------------------
>
> (Updated 二月 23, 2023, 10:35 p.m.)
>
>
> Review request for ranger, Abhishek Kumar, Dineshkumar Yadav, madhan, Madhan Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
>
>
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
>
>
> Repository: ranger
>
>
> Description
> -------
>
> Currently, Service Definitions are persisted in several database tables in a normalized form. However, as operations on a Service Definition object is not granular, but act on the object as a whole, there is not much advantage in normalizing it for storage, and a distinct disadvantage of having to recreate it by accessing multiple database tables during the initialization of Ranger admin process.
>
> This Jira denormalizes service-definition object's persistent state and also removes several tables used only for normalizing service-definition object.
>
>
> Diffs
> -----
>
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java 8632dd6bc
> security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 9a79fe8ad
> security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql fd6cec9a7
> security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 4d5a8cedf
> security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql 3ed2a5b9c
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql ca8f7da1f
> security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 0e5388646
> security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 562467e80
> security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52
> security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 418557bcb
> security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java db1878a35
> security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 7f8d6193f
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java f5f9f8956
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java fb7e5fd40
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java 656bc0184
> security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java 85e457efa
> security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 691ab52b3
> security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 528f4e511
>
>
> Diff: https://reviews.apache.org/r/74319/diff/1/
>
>
> Testing
> -------
>
> Ran all unit tests successfully. Ran ranger-admin and component services in a cluster and verified they work correctly.
>
>
> Thanks,
>
> Abhay Kulkarni
>
>
Re: Review Request 74319: RANGER-4108: Speed up loading of the service-definitions during Ranger startup
Posted by Pradeep Agrawal <pr...@gmail.com>.
> On March 2, 2023, 2:49 a.m., Kirby Zhou wrote:
> > security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql
> > Lines 19 (patched)
> > <https://reviews.apache.org/r/74319/diff/1/?file=2274752#file2274752line19>
> >
> > Can we avoid using stored procedures? TiDB does not support stored procedures.
>
> Pradeep Agrawal wrote:
> How the other old sql patches with stored procedure are working in TiDB.
> Also if TiDB defers from mysql then TiDB support can be added seperately.
>
> Kirby Zhou wrote:
> The old sql patches have the same problem……
>
>
> TiDB is a 95% mysql-compatible NewSQL database. For legal reason, we have to deploy ranger based on tidb. But TiDB is missing some features, which makes ranger unable to install properly.
> https://docs.pingcap.com/tidb/stable/mysql-compatibility#unsupported-features
>
> The biggest problem affecting ranger is missing "Stored procedures and functions", "Select into".
> ranger use Stored procedures in setup scripts to simplify SQL.
> Some work is needed to remove the stored procedure.
>
> Because few old versions of Ranger are using TiDb, we mainly focus on sql patch after ranger-2.3.
>
>
> https://issues.apache.org/jira/browse/RANGER-3984
Would not like to mix with MySQL. TiDB support can be added separately(as a separate db flavor).
Earlier there was issue with MySQL functions so i moved them to MySQL procedures. Now again won't prefer to put effort for moving out of procedure for TiDB.
Also dont have environment of TiDB and not familier to it.
- Pradeep
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/74319/#review225236
-----------------------------------------------------------
On Feb. 23, 2023, 10:35 p.m., Abhay Kulkarni wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/74319/
> -----------------------------------------------------------
>
> (Updated Feb. 23, 2023, 10:35 p.m.)
>
>
> Review request for ranger, Abhishek Kumar, Dineshkumar Yadav, madhan, Madhan Neethiraj, Pradeep Agrawal, and Velmurugan Periasamy.
>
>
> Bugs: RANGER-4108
> https://issues.apache.org/jira/browse/RANGER-4108
>
>
> Repository: ranger
>
>
> Description
> -------
>
> Currently, Service Definitions are persisted in several database tables in a normalized form. However, as operations on a Service Definition object is not granular, but act on the object as a whole, there is not much advantage in normalizing it for storage, and a distinct disadvantage of having to recreate it by accessing multiple database tables during the initialization of Ranger admin process.
>
> This Jira denormalizes service-definition object's persistent state and also removes several tables used only for normalizing service-definition object.
>
>
> Diffs
> -----
>
> agents-common/src/main/java/org/apache/ranger/plugin/store/AbstractServiceStore.java 8632dd6bc
> security-admin/db/mysql/optimized/current/ranger_core_db_mysql.sql 9a79fe8ad
> security-admin/db/mysql/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/mysql/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/oracle/optimized/current/ranger_core_db_oracle.sql fd6cec9a7
> security-admin/db/oracle/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/oracle/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/postgres/optimized/current/ranger_core_db_postgres.sql 4d5a8cedf
> security-admin/db/postgres/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/postgres/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/sqlanywhere/optimized/current/ranger_core_db_sqlanywhere.sql 3ed2a5b9c
> security-admin/db/sqlanywhere/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/sqlanywhere/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/db/sqlserver/optimized/current/ranger_core_db_sqlserver.sql ca8f7da1f
> security-admin/db/sqlserver/patches/059-update-x-portal-user-table.sql 0e5388646
> security-admin/db/sqlserver/patches/061-update-schema-for-x-service-def.sql PRE-CREATION
> security-admin/db/sqlserver/patches/062-remove-unused-ranger-tables-v1.sql PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/biz/ServiceDBStore.java 562467e80
> security-admin/src/main/java/org/apache/ranger/biz/XUserMgr.java bbbf90c52
> security-admin/src/main/java/org/apache/ranger/common/db/BaseDao.java 418557bcb
> security-admin/src/main/java/org/apache/ranger/entity/XXAccessTypeDef.java db1878a35
> security-admin/src/main/java/org/apache/ranger/entity/XXServiceDefBase.java 7f8d6193f
> security-admin/src/main/java/org/apache/ranger/patch/PatchForRemovalOfUnusedRangerDbTables_J10059.java PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/patch/PatchForSolrSvcDefAndPoliciesUpdate_J10055.java f5f9f8956
> security-admin/src/main/java/org/apache/ranger/patch/PatchForUpdatingServiceDefJson_J10058.java PRE-CREATION
> security-admin/src/main/java/org/apache/ranger/patch/PatchPreSql_058_ForUpdateToUniqueResoureceSignature_J10053.java fb7e5fd40
> security-admin/src/main/java/org/apache/ranger/service/RangerServiceDefServiceBase.java 656bc0184
> security-admin/src/main/java/org/apache/ranger/service/XPortalUserService.java 85e457efa
> security-admin/src/test/java/org/apache/ranger/biz/TestServiceDBStore.java 691ab52b3
> security-admin/src/test/java/org/apache/ranger/biz/TestXUserMgr.java 528f4e511
>
>
> Diff: https://reviews.apache.org/r/74319/diff/1/
>
>
> Testing
> -------
>
> Ran all unit tests successfully. Ran ranger-admin and component services in a cluster and verified they work correctly.
>
>
> Thanks,
>
> Abhay Kulkarni
>
>