Query Logging ignore the rules set at mysql_query_rules;

[SteveZhengMe]

Hi team,
I am running proxysql_2.2.0-ubuntu20_arm64. I set the query logging as follow:

• SET mysql-eventslog_filename='db_audit.log';
• SET mysql-eventslog_filesize=1048576000;
• SET mysql-eventslog_format=2;
• SET mysql-eventslog_default_log=1
• LOAD MYSQL VARIABLES TO RUNTIME;
• SAVE MYSQL VARIABLES TO DISK;

I set log=0 to all my rules, but the db_audit.log.000001 is growing and I can see all the queries are logged.

Then I added a rules:
INSERT INTO mysql_query_rules (rule_id, active, match_digest, log, apply) VALUES (1, 1, 'INSERT into ', 1, 0);

Now, in the mysql_query_rules,

rule_id	digest	match_digest	log	apply
1	NULL	insert into	1	0
11	NULL	^SELECT.*FOR UPDATE$	0	1
101	XXXXXX	NULL	0	1
102	XXXXXX	NULL	0	1
103	... ...	NULL	0	1

I suppose the query log only has the "insert " statements, but it still logs all the queries.

It seems to ignore the query rules.

Is there anything I missed?

Thank you :)

[SteveZhengMe]

I misunderstood the mysql-eventslog_default_log.
If it sets to 1, all the rules will be ignored.

If you want to use your customized log rules, mysql-eventslog_default_log should be set to 0.

[renecannao]

Hi @SteveZhengMe .
Reopening this issue for further investigation, maybe it is a bug

[JavierJF]

Hi @SteveZhengMe,

I have tried reproducing this issue but I couldn't manage to, I have tried several scenarios including one similar to your setup without luck. Just for double checking, after changing mysql_query_rules disabling logging for all your rules but the first one, you issued a later LOAD MYSQL QUERY RULES TO RUNTIME to make the new rules effective right?

To be clear about the expected behavior mysql-eventslog_default_log should not take over the behavior specified in mysql_query_rules for logging. So, if setting the value to 1 causes queries matching a mysql_query_rule with log=0 to be logged, this is unexpected behavior.

If you are able to reproduce this consistently, could you please share a minimal reproduction example? Thank you.

[SteveZhengMe]

Hi @JavierJF,
Here are my steps.

Scenario: Log nothing
Given

• 

When

• 

Then nothing should be logged

Fail
Result: when sudo tail -f dml_audit.log.00000001, the log is growing

---

Scenario: Only log a rule
Given mysql-eventslog_default_log=1

When

• 

Then Only the matched log should be in the file

Fail
Result: when sudo tail -f dml_audit.log.00000001, all logs are in the file;

---

Scenario: Nothing should be logged
Given

• 

When Nothing changed to the mysql_query_rules

• 

Then no log should be in the file

Fail
Result: when sudo tail -f dml_audit.log.00000001, the logs that matched the digest '0x585190DD44F1DEE1' are in the file.

Let me know if any steps are wrong

[JavierJF]

Hi @SteveZhengMe,

I'm going to address each scenario you propose, trying to clarify any ambiguity I see in the description:

---

Scenario: Log nothing

For this scenario you said:

Then nothing should be logged

Fail
Result: when sudo tail -f dml_audit.log.00000001, the log is growing

This is not entirely true, no query matching any of the query rules should still be logged, but any query not matching this rules will still be logged, since mysql-eventslog_default_log=1 is enabled (log enabled globally). Please check that the queries being logged are queries that are not being matched by the rules, if this is the case, then this is expected behavior.

---

Scenario: Only log a rule

For this scenario you said:

Then Only the matched log should be in the file

Fail
Result: when sudo tail -f dml_audit.log.00000001, all logs are in the file;

As in the previous scenario, this isn't entirely true, from the queries matching the query rules only the queries matching the rule with log=1 will be logged into file. So any query not matching this rules, will still be logged into the file due to mysql-eventslog_default_log=1 (log enabled globally).

---

Scenario: Nothing should be logged

Fail
Result: when sudo tail -f dml_audit.log.00000001, the logs that matched the digest '0x585190DD44F1DEE1' are in the file.

This is expected behavior, mysql-eventslog_default_log=0 disables logging globally, but your query rules for digest '0x585190DD44F1DEE1' enable it, so it's expected that the queries matching this rule are logged into the log file.

[SteveZhengMe]

Hi @JavierJF ,
I got a little confused, in your previous reply, you mentioned:

So, if setting the value to 1 causes queries matching a mysql_query_rule with log=0 to be logged, this is unexpected behavior.

does the following two scenarios are correct?

Scenario 1:
Given mysql-eventslog_default_log=1 and there are 10 rules
When set rule_a log is 0 and set rule_b log is 1
Then Rule_a should not be logged and rule_b should be logged

Scenario 2:
Given mysql-eventslog_default_log=1 and there are 10 rules
When All rules log=0, (no rules matched)
Then All the SQL will be logged.