-----------------------------------------------------------
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
