Audit plugin reports "command_class=error" for server-side prepared statements

[Andrew Garner]

When logging a prepared statement through the prepared statement api, the command class is logged as "error" even for successful executions.

Here is an example snippet from logs where a successful "SHOW GLOBAL VARIABLES WHERE Variable_name = ?", "max_allowed_packet" is executed:

 {"audit_record":{"name":"Prepare","record":"132355_2017-09-26T04:31:44","timestamp":"2017-09-26T04:31:57 UTC","command_class":"error","connection_id":"4","status":0,"sqltext":"","user":"root[root] @ [127.0.0.1]","host":"","os_user":"","ip":"127.0.0.1","db":""}}
{"audit_record":{"name":"Execute","record":"132356_2017-09-26T04:31:44","timestamp":"2017-09-26T04:31:57 UTC","command_class":"error","connection_id":"4","status":0,"sqltext":"SHOW GLOBAL VARIABLES WHERE Variable_name = 'max_allowed_packet'","user":"root[root] @ [127.0.0.1]","host":"","os_user":"","ip":"127.0.0.1","db":""}}
{"audit_record":{"name":"Close stmt","record":"132357_2017-09-26T04:31:44","timestamp":"2017-09-26T04:31:57 UTC","command_class":"error","connection_id":"4","status":0,"sqltext":"","user":"root[root] @ [127.0.0.1]","host":"","os_user":"","ip":"127.0.0.1","db":""}}

This seems to be due to the audit api setting general_sql_command based on sql_statement_names, where there's no matching entry for the current binary protocol command.

This is confusing for users consuming these logs who might be using server-side prepared statements.

[Nickolay Ihalainen (ihanick)]

5.5.57-38.9, 5.6.37-82.2, 5.7.19-17
docker run --rm -it -e MYSQL_ALLOW_EMPTY_PASSWORD=1 --name p55 percona:5.5
apt-get update ; apt-get install -y python3-mysql.connector
mysql -e "INSTALL PLUGIN audit_log SONAME 'audit_log.so';"
python3 << 'EOF'
import mysql.connector
cnx = mysql.connector.connect(database='information_schema', user='root')
cursor = cnx.cursor(prepared=True)
stmt = "SHOW GLOBAL VARIABLES WHERE Variable_name = ?"
cursor.execute(stmt, ("max_allowed_packet",))
cursor.fetchall()
cursor.close()
cnx.close()
EOF

In addition to prepared statements command class is error for Ping:
<AUDIT_RECORD
  NAME="Ping"
  RECORD="7_2017-10-04T04:45:30"
  TIMESTAMP="2017-10-04T04:47:37 UTC"
  COMMAND_CLASS="error"
  CONNECTION_ID="2"
  STATUS="0"
  SQLTEXT=""
  USER="root[root] @ [127.0.0.1]"
  HOST=""
  OS_USER=""
  IP="127.0.0.1"
/>

Andrew, please provide information about your mysql client library (what programming language you are using?)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1116