[MCOL-377] columnstore queries show as vtable query rather than original query in audit log Created: 2016-10-26  Updated: 2017-05-05  Resolved: 2017-05-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.4
Fix Version/s: 1.1.0

Type: Bug Priority: Major
Reporter: David Thompson (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 2017-2, 2017-3, 2017-4, 2017-5, 2017-6, 2017-7, 2017-8, 2017-9

 Description   

If you enable and turn on the mariadb audit plugin for the columnstore mariadb server(https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/), any columnstore queries will show up in mysql/db/server_audit.log as something like:
'select * from infinidb_vtable.$vtable_7'

rather than the original query such as select * from loanstats limit 10.

The audit plugin should log the users original query rather than the final vtable query.

In addition the syslog_info should be populated with the columnstore system name.

 Comments   
Comment by Dipti Joshi (Inactive) [ 2016-10-27 ]

dthompson ColumnStore has a Query Statistics feature which logs every query in querystats table see https://mariadb.com/kb/en/mariadb/analyzing-queries-in-columnstore/#query-statistics-history for information.

Comment by David Hill (Inactive) [ 2017-04-06 ]

Please add info on what QA needs to look for to test this and how to test it

Comment by Ben Thompson (Inactive) [ 2017-04-07 ]

See mariadb KB article for how to enable audit plugin.

Run queries for columnstore tables and verify the file mysql/db/server_audit.log contains the original query and not the vtable as shown in the issue description.

Run a fresh install and verify the system name that is entered into postConfigure is also written to my.cnf under mysqld section as "server_audit_syslog_info".
Change the SystemName via setConfig and verify the server_audit_syslog_info field is updated.
Set server audit plugin to log to syslog and enable:
set global server_audit_output_type=syslog;
set global server_audit_logging=on;
at this point the server audit plugin should be writing to syslog and should have the system name displayed in the logging
Example:
Apr 7 11:53:09 localhost mysql-server_auditing: columnstore-1 localhost.localdomain,root,localhost,5,10,QUERY,test,'select * from t2',0
Apr 7 11:53:12 localhost mysql-server_auditing: columnstore-1 localhost.localdomain,root,localhost,5,0,DISCONNECT,test,,0

Comment by Daniel Lee (Inactive) [ 2017-05-05 ]

Build tested: Github source

ommit 349cae544b6bc71910267a3b3b0fa3fb57b0a587
Merge: bd13090 2ecb85c
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Thu May 4 16:06:16 2017 -0500

Merge pull request #50 from mariadb-corporation/10.2-fixes

10.2 fixes

[root@localhost mariadb-columnstore-server]# cd mariadb-columnstore-engine/
[root@localhost mariadb-columnstore-engine]# git show
commit a6dfc3c366c861d5777a9931e18f594405e4aab6
Merge: 1c2a8fc 00cd7f4
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Thu May 4 10:33:30 2017 -0500

Merge pull request #168 from mariadb-corporation/MCOL-698

MCOL-698 Fix joiner for LONGTEXT

Reproduced the issue in 1.0.8-1:

May 5 14:48:22 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,19,QUERY,,'set global server_audit_output_type=syslog',0
May 5 14:48:26 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,20,WRITE,mysql,table_stats,
May 5 14:48:26 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,20,WRITE,mysql,column_stats,
May 5 14:48:26 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,20,WRITE,mysql,index_stats,
May 5 14:48:26 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,20,DROP,infinidb_vtable,$vtable_5,
May 5 14:48:26 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,20,QUERY,mysql,'SELECT DATABASE()',0
May 5 14:48:30 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,22,WRITE,mysql,table_stats,
May 5 14:48:30 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,22,WRITE,mysql,column_stats,
May 5 14:48:30 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,22,WRITE,mysql,index_stats,
May 5 14:48:30 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,22,DROP,infinidb_vtable,$vtable_5,
May 5 14:48:30 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,22,READ,mytest,t2,
May 5 14:48:30 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,22,QUERY,mytest,'select * from infinidb_vtable.$vtable_5',0
May 5 14:48:31 localhost mysql-server_auditing: localhost.localdomain,root,localhost,5,0,DISCONNECT,mytest,,0

Verified the fix in 1.1.0-1
May 5 14:47:22 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,37,QUERY,,'set global server_audit_output_type=syslog',0
May 5 14:47:26 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,38,QUERY,,'select count from orders',1046
May 5 14:47:28 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,39,WRITE,mysql,table_stats,
May 5 14:47:28 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,39,WRITE,mysql,column_stats,
May 5 14:47:28 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,39,WRITE,mysql,index_stats,
May 5 14:47:28 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,39,DROP,infinidb_vtable,$vtable_13,
May 5 14:47:28 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,39,QUERY,mysql,'SELECT DATABASE()',0
May 5 14:47:29 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,41,WRITE,mysql,table_stats,
May 5 14:47:29 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,41,WRITE,mysql,column_stats,
May 5 14:47:29 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,41,WRITE,mysql,index_stats,
May 5 14:47:29 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,41,DROP,infinidb_vtable,$vtable_13,
May 5 14:47:29 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,41,READ,mytest,orders,
May 5 14:47:29 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,41,QUERY,mytest,'select count from orders',0
May 5 14:47:31 localhost mysql-server_auditing: localhost.localdomain,root,localhost,13,0,DISCONNECT,mytest,,0

Generated at Thu Feb 08 02:20:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.