You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ranger.apache.org by Pradeep Agrawal <pr...@freestoneinfotech.com> on 2017/03/06 13:23:42 UTC
Review Request 57333: RANGER-1378 : Update MySQL Schema to fix issues
related to only_full_group_by restriction of MySQL 5.7 version
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/57333/
-----------------------------------------------------------
Review request for ranger, Ankita Sinha, Don Bosco Durai, Gautam Borad, Abhay Kulkarni, Madhan Neethiraj, Mehul Parikh, Ramesh Mani, Selvamohan Neethiraj, Sailaja Polavarapu, and Velmurugan Periasamy.
Bugs: RANGER-1378
https://issues.apache.org/jira/browse/RANGER-1378
Repository: ranger
Description
-------
**Problem Statement:** Currently create view statement of 'vx_trx_log' contains more than one column in select clause which are not part of group by clause. SQL92 standard does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
Reference : https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
**Impact:**
There is Page not found (404) error when clicking Admin tab in Audit and below given error message is appearing in log file.
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ranger.x_trx_log.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Error Code: 1055
Call: SELECT ID AS a1, ACTION AS a2, ADDED_BY_ID AS a3, ATTR_NAME AS a4, CREATE_TIME AS a5, NEW_VAL AS a6, CLASS_TYPE AS a7, OBJECT_ID AS a8, OBJECT_NAME AS a9, PARENT_OBJECT_CLASS_TYPE AS a10, PARENT_OBJECT_ID AS a11, PARENT_OBJECT_NAME AS a12, PREV_VAL AS a13, REQ_ID AS a14, SESS_ID AS a15, SESS_TYPE AS a16, TRX_ID AS a17, UPDATE_TIME AS a18, UPD_BY_ID AS a19 FROM vx_trx_log ORDER BY CREATE_TIME DESC LIMIT ?, ?
bind => [2 parameters bound]
Query: ReadAllQuery(referenceClass=VXXTrxLog sql="SELECT ID AS a1, ACTION AS a2, ADDED_BY_ID AS a3, ATTR_NAME AS a4, CREATE_TIME AS a5, NEW_VAL AS a6, CLASS_TYPE AS a7, OBJECT_ID AS a8, OBJECT_NAME AS a9, PARENT_OBJECT_CLASS_TYPE AS a10, PARENT_OBJECT_ID AS a11, PARENT_OBJECT_NAME AS a12, PREV_VAL AS a13, REQ_ID AS a14, SESS_ID AS a15, SESS_TYPE AS a16, TRX_ID AS a17, UPDATE_TIME AS a18, UPD_BY_ID AS a19 FROM vx_trx_log ORDER BY CREATE_TIME DESC LIMIT ?, ?")
**Proposed Solution:**
Proposed solution contains changes in schema definiton of 'vx_trx_log' view.
**Note: Please note that this patch contains changes in table creation statement also; From current xa_core_db.sql file it seems create table statements were generated from some db backup tool which contains table creation statements in alphabatical order of table name while it suppose to be in the order of required entities and relations between them.**
Diffs
-----
security-admin/db/mysql/xa_core_db.sql 9a22e2c
Diff: https://reviews.apache.org/r/57333/diff/1/
Testing
-------
**Steps Performed with patch:**
1. Installed and Started Ranger admin.
2. Create user 'testuser1' so that admin audit logs get generated in x_trx_log table.
3. Visited Admin tab of Audit menu.
**Expected Behaviour:**
Admin audit log should show the admin audit logs Rather throwing Page not found (404) error.
**Actual Behaviour:**
Admin audit tab was showing transaction logs of 'testuser1' user creation.
**Note : Tested in MySQL 5.1, 5.6 and 5.7 version.**
Thanks,
Pradeep Agrawal
Re: Review Request 57333: RANGER-1378 : Update MySQL Schema to fix
issues related to only_full_group_by restriction of MySQL 5.7 version
Posted by Velmurugan Periasamy <vp...@hortonworks.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/57333/#review168238
-----------------------------------------------------------
Ship it!
Ship It!
- Velmurugan Periasamy
On March 7, 2017, 1:33 p.m., Pradeep Agrawal wrote:
>
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/57333/
> -----------------------------------------------------------
>
> (Updated March 7, 2017, 1:33 p.m.)
>
>
> Review request for ranger, Ankita Sinha, Don Bosco Durai, Gautam Borad, Abhay Kulkarni, Madhan Neethiraj, Mehul Parikh, Ramesh Mani, Selvamohan Neethiraj, Sailaja Polavarapu, and Velmurugan Periasamy.
>
>
> Bugs: RANGER-1378
> https://issues.apache.org/jira/browse/RANGER-1378
>
>
> Repository: ranger
>
>
> Description
> -------
>
> **Problem Statement:** Currently create view statement of 'vx_trx_log' contains more than one column in select clause which are not part of group by clause. SQL92 standard does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
>
> Reference : https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
>
> **Impact:**
> There is Page not found (404) error when clicking Admin tab in Audit and below given error message is appearing in log file.
> Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ranger.x_trx_log.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> Error Code: 1055
> Call: SELECT ID AS a1, ACTION AS a2, ADDED_BY_ID AS a3, ATTR_NAME AS a4, CREATE_TIME AS a5, NEW_VAL AS a6, CLASS_TYPE AS a7, OBJECT_ID AS a8, OBJECT_NAME AS a9, PARENT_OBJECT_CLASS_TYPE AS a10, PARENT_OBJECT_ID AS a11, PARENT_OBJECT_NAME AS a12, PREV_VAL AS a13, REQ_ID AS a14, SESS_ID AS a15, SESS_TYPE AS a16, TRX_ID AS a17, UPDATE_TIME AS a18, UPD_BY_ID AS a19 FROM vx_trx_log ORDER BY CREATE_TIME DESC LIMIT ?, ?
> bind => [2 parameters bound]
> Query: ReadAllQuery(referenceClass=VXXTrxLog sql="SELECT ID AS a1, ACTION AS a2, ADDED_BY_ID AS a3, ATTR_NAME AS a4, CREATE_TIME AS a5, NEW_VAL AS a6, CLASS_TYPE AS a7, OBJECT_ID AS a8, OBJECT_NAME AS a9, PARENT_OBJECT_CLASS_TYPE AS a10, PARENT_OBJECT_ID AS a11, PARENT_OBJECT_NAME AS a12, PREV_VAL AS a13, REQ_ID AS a14, SESS_ID AS a15, SESS_TYPE AS a16, TRX_ID AS a17, UPDATE_TIME AS a18, UPD_BY_ID AS a19 FROM vx_trx_log ORDER BY CREATE_TIME DESC LIMIT ?, ?")
>
> **Proposed Solution:**
> Proposed solution contains changes in schema definiton of 'vx_trx_log' view.
>
> **Note: Please note that this patch contains changes in table creation statement also; From current xa_core_db.sql file it seems create table statements were generated from some db backup tool which contains table creation statements in alphabatical order of table name while it suppose to be in the order of required entities and relations between them.**
>
>
> Diffs
> -----
>
> security-admin/db/mysql/xa_core_db.sql 9a22e2c
>
>
> Diff: https://reviews.apache.org/r/57333/diff/1/
>
>
> Testing
> -------
>
> **Steps Performed with patch:**
> 1. Installed and Started Ranger admin.
> 2. Create user 'testuser1' so that admin audit logs get generated in x_trx_log table.
> 3. Visited Admin tab of Audit menu.
>
> **Expected Behaviour:**
> Admin audit log should show the admin audit logs Rather throwing Page not found (404) error.
>
> **Actual Behaviour:**
> Admin audit tab was showing transaction logs of 'testuser1' user creation.
>
> **Note : Tested in MySQL 5.1, 5.6 and 5.7 version.**
>
>
> Thanks,
>
> Pradeep Agrawal
>
>
Re: Review Request 57333: RANGER-1378 : Update MySQL Schema to fix
issues related to only_full_group_by restriction of MySQL 5.7 version
Posted by Pradeep Agrawal <pr...@freestoneinfotech.com>.
-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/57333/
-----------------------------------------------------------
(Updated March 7, 2017, 1:33 p.m.)
Review request for ranger, Ankita Sinha, Don Bosco Durai, Gautam Borad, Abhay Kulkarni, Madhan Neethiraj, Mehul Parikh, Ramesh Mani, Selvamohan Neethiraj, Sailaja Polavarapu, and Velmurugan Periasamy.
Changes
-------
Updated branch details.
Bugs: RANGER-1378
https://issues.apache.org/jira/browse/RANGER-1378
Repository: ranger
Description
-------
**Problem Statement:** Currently create view statement of 'vx_trx_log' contains more than one column in select clause which are not part of group by clause. SQL92 standard does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
Reference : https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
**Impact:**
There is Page not found (404) error when clicking Admin tab in Audit and below given error message is appearing in log file.
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ranger.x_trx_log.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Error Code: 1055
Call: SELECT ID AS a1, ACTION AS a2, ADDED_BY_ID AS a3, ATTR_NAME AS a4, CREATE_TIME AS a5, NEW_VAL AS a6, CLASS_TYPE AS a7, OBJECT_ID AS a8, OBJECT_NAME AS a9, PARENT_OBJECT_CLASS_TYPE AS a10, PARENT_OBJECT_ID AS a11, PARENT_OBJECT_NAME AS a12, PREV_VAL AS a13, REQ_ID AS a14, SESS_ID AS a15, SESS_TYPE AS a16, TRX_ID AS a17, UPDATE_TIME AS a18, UPD_BY_ID AS a19 FROM vx_trx_log ORDER BY CREATE_TIME DESC LIMIT ?, ?
bind => [2 parameters bound]
Query: ReadAllQuery(referenceClass=VXXTrxLog sql="SELECT ID AS a1, ACTION AS a2, ADDED_BY_ID AS a3, ATTR_NAME AS a4, CREATE_TIME AS a5, NEW_VAL AS a6, CLASS_TYPE AS a7, OBJECT_ID AS a8, OBJECT_NAME AS a9, PARENT_OBJECT_CLASS_TYPE AS a10, PARENT_OBJECT_ID AS a11, PARENT_OBJECT_NAME AS a12, PREV_VAL AS a13, REQ_ID AS a14, SESS_ID AS a15, SESS_TYPE AS a16, TRX_ID AS a17, UPDATE_TIME AS a18, UPD_BY_ID AS a19 FROM vx_trx_log ORDER BY CREATE_TIME DESC LIMIT ?, ?")
**Proposed Solution:**
Proposed solution contains changes in schema definiton of 'vx_trx_log' view.
**Note: Please note that this patch contains changes in table creation statement also; From current xa_core_db.sql file it seems create table statements were generated from some db backup tool which contains table creation statements in alphabatical order of table name while it suppose to be in the order of required entities and relations between them.**
Diffs
-----
security-admin/db/mysql/xa_core_db.sql 9a22e2c
Diff: https://reviews.apache.org/r/57333/diff/1/
Testing
-------
**Steps Performed with patch:**
1. Installed and Started Ranger admin.
2. Create user 'testuser1' so that admin audit logs get generated in x_trx_log table.
3. Visited Admin tab of Audit menu.
**Expected Behaviour:**
Admin audit log should show the admin audit logs Rather throwing Page not found (404) error.
**Actual Behaviour:**
Admin audit tab was showing transaction logs of 'testuser1' user creation.
**Note : Tested in MySQL 5.1, 5.6 and 5.7 version.**
Thanks,
Pradeep Agrawal