Bug #76311 Audit log vs Error log discrepancy on aborted connections
Submitted: 13 Mar 2015 10:31 Modified: 14 Aug 2015 12:10
Reporter: Rafael Bos Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Audit Severity:S3 (Non-critical)
Version:5.6.22, 5.6.26,5.6.27 OS:Linux
Assigned to: CPU Architecture:Any

[13 Mar 2015 10:31] Rafael Bos 
Description:
Hi,
there seems to be some discrepancy in records presented in Audit log (created by Audit log plugin) and records in the MySQL error log for, at least, aborted connections.

Part of the error log:
2015-03-12 15:10:42 16828 [Warning] Aborted connection 904326 to db: 'dbname' user: 'username' host: 'hostname' (Got an error reading communication packets)
2015-03-13 10:01:53 16828 [Warning] Aborted connection 955725 to db: 'dbname' user: 'username' host: 'hostname' (Got timeout reading communication packets)

And from audit.log:
 <AUDIT_RECORD>
  <TIMESTAMP>2015-03-12T15:10:42 UTC</TIMESTAMP>
  <RECORD_ID>10133566_2015-03-12T13:19:01</RECORD_ID>
  <NAME>Quit</NAME>
  <CONNECTION_ID>904326</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER/>
  <OS_LOGIN/>
  <HOST/>
  <IP/>
  <COMMAND_CLASS>connect</COMMAND_CLASS>
 </AUDIT_RECORD>

 <AUDIT_RECORD>
  <TIMESTAMP>2015-03-13T10:01:53 UTC</TIMESTAMP>
  <RECORD_ID>10245981_2015-03-12T13:19:01</RECORD_ID>
  <NAME>Quit</NAME>
  <CONNECTION_ID>955725</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER/>
  <OS_LOGIN/>
  <HOST/>
  <IP/>
  <COMMAND_CLASS>connect</COMMAND_CLASS>
 </AUDIT_RECORD>

Based on the status and status_code having 0 values it seems that the connections was properly closed by the client. 

How to repeat:
Start a script connecting to a MySQL instance and during the execution kill it - before running disconnect on the DB connection handle.

Suggested fix:
Audit records for "Quit" operation for the aborted connections should have status_code and status other than 0.
[14 Aug 2015 12:10] MySQL Verification Team 
Hello Rafael Bos,

Thank you for the report.
Verified as described with 5.6.26/5.6.27 builds.

Thanks,
Umesh
[14 Aug 2015 12:13] MySQL Verification Team 
// Start server with --wait_timeout=50 --interactive_timeout=50 and --log-warnings
-- enable audit log plugin
-- enable audit logging for ushastry@localhost'

SET GLOBAL audit_log_include_accounts = 'ushastry@localhost';

-- Connect using ushastry@localhost, keep the session idle for >50sec, check audit log and error log
 -- audit.log
 
  <AUDIT_RECORD TIMESTAMP="2015-08-14T12:01:39 UTC" RECORD_ID="2719_2015-08-14T12:00:45" NAME="Quit" CONNECTION_ID="1" STATUS="0" STATUS_CODE="0" USER="ushastry" OS_LOGIN="" HOST="localhost" IP="" COMMAND_CLASS="connect"/>
  <AUDIT_RECORD TIMESTAMP="2015-08-14T12:10:38 UTC" RECORD_ID="2722_2015-08-14T12:00:45" NAME="Quit" CONNECTION_ID="2" STATUS="0" STATUS_CODE="0" USER="ushastry" OS_LOGIN="" HOST="localhost" IP="" COMMAND_CLASS="connect"/>
  
 -- error log
  2015-08-14 14:01:39 26401 [Warning] Aborted connection 1 to db: 'unconnected' user: 'ushastry' host: 'localhost' (Got timeout reading communication packets)
  2015-08-14 14:10:38 26401 [Warning] Aborted connection 2 to db: 'unconnected' user: 'ushastry' host: 'localhost' (Got timeout reading communication packets)