Upsert failing - possibly due to the delimiter

[danbuntu]

SUMMARY

I'm trying to run an upsert mysql statement. This is essentially two commands with a delimiter between and looks like

INSERT INTO config  (name, value) VALUES ('name2', 'value2')
ON DUPLICATE KEY UPDATE name='name2';
UPDATE config SET value='value2' WHERE name='name2';

This is failing with 'You have an error in your SQL syntax;'. The SQL runs fine though when run directly against the server.

I believe this might be failing due to the delimiter needed after the Insert part.

Is this a bug or is a different way that could be suggested to solve the problem?

ISSUE TYPE

• Possible Bug or incompetence on a part of the user

COMPONENT NAME

mysql_db

ANSIBLE VERSION

ansible [core 2.15.9]
  config file = /home/ubuntu/skillset-ansible/ansible.cfg
  configured module search path = ['/home/ubuntu/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  ansible collection location = /home/ubuntu/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.10.12 (main, Nov 20 2023, 15:14:05) [GCC 11.4.0] (/usr/bin/python3)
  jinja version = 3.0.3
  libyaml = True

COLLECTION VERSION

# /usr/lib/python3/dist-packages/ansible_collections
Collection        Version
----------------- -------
community.general 8.5.0  

[danbuntu]

And literally just solved it thanks to [#613 ]

[laurent-indermuehle]

Hi @danbuntu, could you please give us an example of your solution?
The issue you mention is a feature request so I fail to see how it solved your issue.

Which module did you use? mysql_query?

[laurent-indermuehle]

HI @danbuntu, as I understand, you have find a way of doing your upsert query using a transaction as demonstrated in #613. So I close this issue as it seems to be resolved and not related to the collection.

[Andersson007]

thanks folks!