[MXS-885] Query parsing failing for certain queries when invoked from database firewall filter Created: 2016-10-07  Updated: 2016-10-11  Resolved: 2016-10-10

Status: Closed
Project: MariaDB MaxScale
Component/s: qc_sqlite
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Dipti Joshi (Inactive) Assignee: Johan Wikman
Resolution: Not a Bug Votes: 0
Labels: None

Attachments: Text File blacklist-rules.txt     File maxscale.cnf    

 Description   

MaxScale is configured with database firewall filter and readwrite split service. Configuration files are attached

Parsing for following query fails when invoked from dbfwfilter

MySQL [tpcc]> select * from orders having count(*) > 10 group by o_carrier_id limit 10;
 
ERROR 1141 (HY000): Access denied for user 'appuser'@'172.19.0.1' to database 'tpcc': dbfwfilter: 
Query could not be parsed completely and will hence be rejected.

Parsing of following query does not fail.

MySQL [tpcc]> select * from orders where o_id > 1000 limit 10;
 
+------+--------+--------+--------+--------------+----------+-------------+---------------------+
 
| o_id | o_w_id | o_d_id | o_c_id | o_carrier_id | o_ol_cnt | o_all_local | o_entry_d           |
 
+------+--------+--------+--------+--------------+----------+-------------+---------------------+
 
| 1001 |      1 |      1 |   2350 |            9 |        8 |           1 | 2016-10-06 02:31:53 |
 
| 1002 |      1 |      1 |   2977 |            4 |        5 |           1 | 2016-10-06 02:31:53 |
 
| 1003 |      1 |      1 |   1863 |            7 |        8 |           1 | 2016-10-06 02:31:53 |
 
| 1004 |      1 |      1 |   2684 |            6 |        6 |           1 | 2016-10-06 02:31:53 |
 
| 1005 |      1 |      1 |   2378 |           10 |        5 |           1 | 2016-10-06 02:31:53 |
 
| 1006 |      1 |      1 |   2689 |            5 |       14 |           1 | 2016-10-06 02:31:53 |
 
| 1007 |      1 |      1 |   2116 |            6 |        9 |           1 | 2016-10-06 02:31:53 |
 
| 1008 |      1 |      1 |   1521 |            1 |        8 |           1 | 2016-10-06 02:31:53 |
 
| 1009 |      1 |      1 |   1249 |           10 |       15 |           1 | 2016-10-06 02:31:53 |
 
| 1010 |      1 |      1 |   2502 |            2 |       12 |           1 | 2016-10-06 02:31:53 |
 
+------+--------+--------+--------+--------------+----------+-------------+---------------------+
 
10 rows in set (0.01 sec)



 Comments   
Comment by Johan Wikman [ 2016-10-10 ]

According to https://mariadb.com/kb/en/sql-99/select-statement HAVING follows GROUP BY.

And indeed, MariaDB 10.0.22 rejects that statement:

MySQL [testdb]> select * from orders having count(*) > 10 group by o_carrier_id limit 10;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'group by o_carrier_id limit 10' at line 1

The following works fine:

MySQL [testdb]> select * from orders group by o_carrier_id having count(*) > 10 limit 10;
 
Empty set (0.00 sec)

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

johan.wikman In that case the same error message as that reported by MariaDB should be reported by MaxScale.

MaxScale error reported is "ERROR 1141 (HY000): Access denied for user 'appuser'@'172.19.0.1' to database 'tpcc': dbfwfilter:
Query could not be parsed completely and will hence be rejected." vs. MariaDB error reported is "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'group by o_carrier_id limit 10' at line 1"

MariaDB error gives more useful information for user to correct their query - while MaxScale's error message is not informative enough for user to correct the query - it leads user to believe that it is an internal error rather than user error.

Comment by Johan Wikman [ 2016-10-11 ]

The error cannot be the same, as the parser is not the same; it's not possible to know whether the statement could not be parsed due to it being invalid or due to the parser being deficient. Consequently, it is an access denied error, since unless the statement can be parsed completely we cannot know with certainty whether it matches a rule or not.

However, the error message can suggest to verify the syntax of the statement.

Further, the actual sqlite error will be logged as a warning.

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