RDS - Unable to change Parameter groups for Mariadb v.10.4
Hi,
According to MariaDB docs, 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=offthrows the same error. - Pasting whole config value without
rowid_filterturn on that feature - because of defaults like mentioned in docs above - Putting only value
rowid_filter=offthrows error - Engine version: 10.4.24
How can i turn one specific option off?
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";
Relevant questions
Migrating from RDS MariaDB 10.2 to Aurora MySQL 5.6
Accepted Answerasked 3 years agoRDS - Unable to change Parameter groups for Mariadb v.10.4
asked 2 months agoRDS mariadb performance on update queries
asked 4 months agoAnnouncement: Amazon Relational Database Service (Amazon RDS) for MariaDB 10.2 End-of-Life date is October 15, 2022
asked 3 months agoHow to change parameter partial_revokes
Accepted Answerasked 8 months ago[Announcement] Extending end-of-life Process for Amazon RDS for MariaDB 10.0 and 10.1
asked a year agoWhy does default max_connections differ in the default parameter groups for RDS MySQL and RDS MariaDB?
asked 6 months agoWhat is the benefit of using the MyRocks storage engine on RDS for MariaDB?
asked 4 months agoMissing parameter aria_pagecache_buffer_size for rds mariadb
asked 3 years agoMariaDB 10.2 and php7.3 not possible at the same time?
Accepted Answerasked 3 years ago