Hi dear community,
We currently test the Percona Audit Log Filter component and we have some difficulties to find the right filter to match our needs.
Our test environment runs last Percona Server for MySQL 8.4.4-4 and we want our final filter to capture all queries that are not “select” queries.
We start reading the documentation which shows a number of examples : we can log all or we can filter on event class. But the documention only mentions 3 class that can be used to filter : connection, general and table_access. When we log all events, we see others event classes like “query” class that matches what we’re looking for.
Our first filter was :
{
"filter": {
"class": [
{
"name": "query",
"event": {
"name": ["start", "status_end"]
}
}
]
}
}
In the log file we can see records like
{
"timestamp": "2025-04-23 10:02:43",
"time": 1745402563028121,
"id": 27,
"class": "query",
"event": "query_start",
"connection_id": 22,
"query_data": {
"query": "insert into t1 values (1)",
"status": 0,
"sql_command": "insert"}
},
{
"timestamp": "2025-04-23 10:02:43",
"time": 1745402563028629,
"id": 28,
"class": "query",
"event": "query_status_end",
"connection_id": 22,
"query_data": {
"query": "insert into t1 values (1)",
"status": 0,
"sql_command": "insert"}
},
...
{
"timestamp": "2025-04-23 10:06:13",
"time": 1745402773005071,
"id": 39,
"class": "query",
"event": "query_start",
"connection_id": 22,
"query_data": {
"query": "select * from t1",
"status": 0,
"sql_command": "select"}
},
{
"timestamp": "2025-04-23 10:06:13",
"time": 1745402773005244,
"id": 40,
"class": "query",
"event": "query_status_end",
"connection_id": 22,
"query_data": {
"query": "select * from t1",
"status": 0,
"sql_command": "select"}
},
Now we need to exclude “select” queries based on sql_command field from results and troubles begin… At this point the doc can’t help us because it doesn’t show how to add subclass filter for this “query” class.
We use MySQL documentation to try adding filter on “field” values but without success. Finally thanks to this ticket and this one we add filter based on “field” value and SQL commands as listed in Performance Schema instruments.
Now to exclude unnecessary statements, the desired filter is as follows :
{
"filter": {
"class": [
{
"name": "query",
"event": {
"name": ["start", "status_end"],
"log": {
"not": {
"or": [
{ "field": { "name": "sql_command_id", "value": "select"} },
{ "field": { "name": "sql_command_id", "value": "show_binlogs"} },
{ "field": { "name": "sql_command_id", "value": "show_binlog_events"} },
{ "field": { "name": "sql_command_id", "value": "show_charsets"} },
{ "field": { "name": "sql_command_id", "value": "show_collations"} },
{ "field": { "name": "sql_command_id", "value": "show_create_db"} },
{ "field": { "name": "sql_command_id", "value": "show_create_event"} },
{ "field": { "name": "sql_command_id", "value": "show_create_func"} },
{ "field": { "name": "sql_command_id", "value": "show_create_proc"} },
{ "field": { "name": "sql_command_id", "value": "show_create_table"} },
{ "field": { "name": "sql_command_id", "value": "show_create_trigger"} },
{ "field": { "name": "sql_command_id", "value": "show_create_user"} },
{ "field": { "name": "sql_command_id", "value": "show_databases"} },
{ "field": { "name": "sql_command_id", "value": "show_engine_logs"} },
{ "field": { "name": "sql_command_id", "value": "show_engine_mutex"} },
{ "field": { "name": "sql_command_id", "value": "show_engine_status"} },
{ "field": { "name": "sql_command_id", "value": "show_errors"} },
{ "field": { "name": "sql_command_id", "value": "show_events"} },
{ "field": { "name": "sql_command_id", "value": "show_fields"} },
{ "field": { "name": "sql_command_id", "value": "show_function_code"} },
{ "field": { "name": "sql_command_id", "value": "show_function_status"} },
{ "field": { "name": "sql_command_id", "value": "show_grants"} },
{ "field": { "name": "sql_command_id", "value": "show_keys"} },
{ "field": { "name": "sql_command_id", "value": "show_master_status"} },
{ "field": { "name": "sql_command_id", "value": "show_open_tables"} },
{ "field": { "name": "sql_command_id", "value": "show_plugins"} },
{ "field": { "name": "sql_command_id", "value": "show_privileges"} },
{ "field": { "name": "sql_command_id", "value": "show_procedure_code"} },
{ "field": { "name": "sql_command_id", "value": "show_procedure_status"} },
{ "field": { "name": "sql_command_id", "value": "show_processlist"} },
{ "field": { "name": "sql_command_id", "value": "show_profile"} },
{ "field": { "name": "sql_command_id", "value": "show_profiles"} },
{ "field": { "name": "sql_command_id", "value": "show_relaylog_events"} },
{ "field": { "name": "sql_command_id", "value": "show_slave_hosts"} },
{ "field": { "name": "sql_command_id", "value": "show_slave_status"} },
{ "field": { "name": "sql_command_id", "value": "show_status"} },
{ "field": { "name": "sql_command_id", "value": "show_storage_engines"} },
{ "field": { "name": "sql_command_id", "value": "show_tables"} },
{ "field": { "name": "sql_command_id", "value": "show_table_status"} },
{ "field": { "name": "sql_command_id", "value": "show_triggers"} },
{ "field": { "name": "sql_command_id", "value": "show_variables"} },
{ "field": { "name": "sql_command_id", "value": "show_warnings"} }
]
}
}
}
}
]
}
}
The problem with this filter comes when we run the query to define the filter, we got the error :
ERROR 1123 (HY000): Can't initialize function 'audit_log_filter_set_filter'; Wrong argument: definition is too long, max length is 1024
We tried to split the filter into several filters but the component allows only to define one filter by user so this idea is not the good one.
Do you think there is a solution to our problem ? Any help would be appreciated.
