RDS - Unable to change Parameter groups for Mariadb v.10.4

Question

Hi,
According to [MariaDB docs](https://mariadb.com/kb/en/optimizer-switch/), i should be able to change every possible option on or off. When i try to switch value for optimizer_switch `rowid_filter=off` in Amazon RDS -> Parameter groups, i got an error like below:

> Error saving: Invalid parameter value: rowid_filter=off for: optimizer_switch allowed values are: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: 5f849923-efbc-4edc-822d-8648a0f86b9b; Proxy: null)

* Pasting whole config value with `rowid_filter=off` throws the same error.
* Pasting whole config value without `rowid_filter` turn on that feature - because of defaults like mentioned in docs above
* Putting only value `rowid_filter=off` throws error
* Engine version: 10.4.24

How can i turn one specific option off?

Answer

Hi! Thanks for the question and posting the details of you research. Seems this indeed is a defect in the configuration system. We are researching it with the goal of having it fixed in the next RDS for MariaDB 10.4 series release.

Meanwhile, we tested that the following workarounds can successfully turn off the rowid_filter, which you can use before a fix is available:

```
Either ONE of the following would work. Note: 1st approach only needs to be done once, while the 2nd approach affects only the current session and needs to repeat for every time a session starts.

1. Set the ‘init_connect’ in parameter group to SET optimizer_switch="rowid_filter=off"
mysql> show variables like 'init_connect'; 
+---------------+-----------------------------------------+
| Variable_name | Value                                   |
+---------------+-----------------------------------------+
| init_connect  | SET optimizer_switch="rowid_filter=off" |
+---------------+-----------------------------------------+

2. Alternatively, after logging into a session, set the session optimizer_switch parameter with rowid_filter=off.
mysql> set @@session.optimizer_switch="rowid_filter=off";
```

Answer

Any further update if it had been fixed?
I understand "workaround", but it only changes the setting for the current session, which i can not control through the application.

RDS - Unable to change Parameter groups for Mariadb v.10.4

相关内容