mysql_variables option to remove set variables

[rafi0101]

SUMMARY

mysql_variables should support the option to remove already set variables (and the mysql/mariadb default value should be applied)

ISSUE TYPE

• Feature Idea

COMPONENT NAME

community.mysql.mysql_variables

ADDITIONAL INFORMATION

You have set a variable that you no longer need. How can I remove it without searching for the default value and setting it myself or editing mysqld-auto.cnf manually?

Should work for all available modes.

[Andersson007]

@rafi0101 hello, do you mean something like setting it to its default using the DEFAULT keyword?
I guess using DEFAULT has a flaw that this will be a not idempotent solution (at least in check mode)
Or there are any reliable ways to get a default value from some system tables for both MySQL and MariaDB? I don't see.
I'm not an expert though.
If it's possible to implement it in idempotent way, I would see another choice state: default.

@laurent-indermuehle and other maintainers any thoughts from you too?

[rafi0101]

Yes, that would be an option with the DEFAULT keyword. But of course what should happen if there is no default value.

That might be an option for the check mode, if you wrap the whole thing in a transaction and discard it again after validating if changed: https://stackoverflow.com/a/58499162/10403502

Yes for example, add state: present as default for adding/changing vars and state: default to set the default value?

[rafi0101]

side question: does mysql_variables support MariaDB 10.11? Because I get the following error message: Server version must be 8.0 or greater. On MariaDB 10.6 is the table performance_schema.persisted_variables also not available

[Andersson007]

@rafi0101

1. Would you like to work on the implementation yourself or someone else can give it a try? Here's a quick-start guide
2. I see we test against the following versions:

        db_engine_version:
          - 5.7.40
          - 8.0.31
          - 10.4.27
          - 10.5.18
          - 10.6.11

Anyway, could you please open a separate issue? Needs to be checked

[laurent-indermuehle]

I never used community.mysql.mysql_variables to persist settings.

Thank you @rafi0101 to made me aware that you can do `set @@global.some_var = DEFAULT. That's a great feature!

I read here that MariaDB can do the same:

Setting a global variable to DEFAULT will restore it to the server default, and setting a session variable to DEFAULT will restore it to the current global value.

You're right. We must test MariaDB 10.11 since it's a long term service. But as noted above, they may be some tests to add and issues that need fixing when adding this version to our CI's workflow. So I think it should be a separate PR.

Now. Instead of state: default, I prefer something like this:

community.mysql.mysql_variables:
  variable: some_var
  value: DEFAULT
  state: present

But what to do if a user pass both value: DEFAULT and state: absent? I don't see the point, so I guess we should forbid this.

Also, the module lake documentation for the state option.

This seems like a cool PR to work on. Adding MariaDB 10.11 may be trickier. Somebody wants to take those PR? I can't right now :(

[Andersson007]

@laurent-indermuehle thanks for the feedback!

But what to do if a user pass both value: DEFAULT and state: absent? I don't see the point, so I guess we should forbid this.

or we maybe could just ignore it

This seems like a cool PR to work on. Adding MariaDB 10.11 may be trickier. Somebody wants to take those PR? I can't right now :(

@rafi0101 the question is still relevant :) If there are any other volunteers, welcome! Please put it explicitly that you're working on it.

[rafi0101]

Since I only use MariaDB and I/we have now found out that the persist function is not available there, this module is unfortunately less interesting for me :(
If this would have worked with MariaDB, I would have loved to support you but unfortunately I don't have enough time for it