mysql_query: To include the extra-vars

[tarunm97]

SUMMARY

Hi Team, can we add the extra-vars field in the mysql_query ansible module, just like we have in the mysql_db module for dump state(dump_extra_args)

- name: Dump databases without including triggers
  community.mysql.mysql_db:
    state: dump
    name: foo
    target: /tmp/dump.sql
    dump_extra_args: --skip-triggers

ISSUE TYPE

• Feature Idea

COMPONENT NAME

mysql_query

ADDITIONAL INFORMATION

will be good if we add this feature for example i got stucked while automating this mysql query

mysql  -uroot -p'' --skip-column-names -A -e"SELECT CONCAT('truncate table ',table_schema,'.',table_name,';') FROM information_schema.tables  WHERE table_schema='plparchivetest'"

I tried to automate the query, everything is fine apart from the "-skip-column-names -A", how to pass this.

- name: truncate the tables
  community.mysql.mysql_query:
       login_user: root
       login_password: "{{ mysql_password }}"
       login_unix_socket: /var/run/mysqld/mysqld.sock
       query: "SELECT CONCAT('truncate table ',table_schema,'.',table_name,';') FROM information_schema.tables  WHERE table_schema='plparchivetest'"

[Andersson007]

@tarunm97 hi, thanks for the feedback!
The module works via a Python driver (mysql_db state dump works via the CLI utility), so I think it's impossible to pass extra args to mysql_query.
Could you please provide:

1. output / errors you get
2. output you're trying to achieve
   Thanks!

[tarunm97]

hi @Andersson007 ,

need to include the --skip-column-names -A in the above command, how to add this.

[Andersson007]

@tarunm97 hi, we can't add the arguments as I explained.
I'm trying to figure out what you got using the module and what you wish to achieve.
That's why I'm asking the actual and desired output / result. Maybe we could solve it somehow another way.

[Andersson007]

cc @tarunm97 ^

[tarunm97]

Hi @Andersson007,

Sorry i just missed it.

when i am executing the command:-

mysql -uroot -p'' --skip-column-names -A -e"SELECT CONCAT('truncate table ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE table_schema='plparchivetest'"

I am getting this output:-

Output:-

+--------------------------------------+
| truncate table plparchivetest.test; |
| truncate table plparchivetest.test1; |
+--------------------------------------+

and when I am executing these commands in the MySQL prompt (Mysql >), I am able to truncate the tables.

so by merging both commands using Linux we can do it like this:-

mysql -uroot -p'' --skip-column-names -A -e"SELECT CONCAT('truncate table ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE table_schema='plparchivetest'" | mysql -uroot -p'' --skip-column-names -A

For now, I have done this using an ansible shell module.

Thanks for the assistance, I understand that it is not possible because of that python driver.

[Andersson007]

@tarunm97 hi, ok, I'll close the issue then, thanks