UTF-8 characters in SQL Query result to errors

itcultus commented 2 years ago

SUMMARY

The issue is very similar to #140 . When I use the mysql_query module with some UTF-8 characters it fails.

The SQL Query I use is the following:

INSERT INTO `lang` (`l_id`, `l_code`, `l_name`) VALUES (1,  'en_GB.UTF-8', 'English'),   
(2,  'sl_SI.UTF-8', 'Slovenščina'), (3,  'fr_FR.UTF-8', 'Français'), (4,  'nl_NL.UTF-8', 'Nederlands'), 
(5,  'de_DE.UTF-8', 'Deutsch'), (6,  'pt_BR.UTF-8', 'Brazil'), (7,  'es_ES.UTF-8', 'Español'), 
(8,  'cs_CZ.UTF-8', 'Czech'),  (9,  'en_US.UTF-8', 'English (US)'), (10, 'ru_RU.UTF-8', 'Russian'),  
(11, 'zh_CN.UTF-8', 'Chinese'),(12, 'ja_JP.UTF-8', 'Japanese');

The SQL query is part of the phpipam installation process that I try to automate with ansible.

The query is executed successfully when I use the mysql client without issues. However, when I execute it via ansible with the mysql_query module, it fails with the error message:

  msg: 'Cannot execute SQL ''INSERT INTO `lang` (`l_id`, `l_code`, `l_name`) VALUES 
(1,  ''en_GB.UTF-8'', ''English''),   (2,  ''sl_SI.UTF-8'', ''Slovenščina''), (3,  ''fr_FR.UTF-8'', ''Français''),
(4,  ''nl_NL.UTF-8'', ''Nederlands''),
(5,  ''de_DE.UTF-8'', ''Deutsch''), (6,  ''pt_BR.UTF-8'', ''Brazil''), 
(7,  ''es_ES.UTF-8'', ''Español''), (8,  ''cs_CZ.UTF-8'', ''Czech''),  
(9,  ''en_US.UTF-8'', ''English (US)''), (10, ''ru_RU.UTF-8'', ''Russian''),
(11, ''zh_CN.UTF-8'', ''Chinese''), 
(12, ''ja_JP.UTF-8'', ''Japanese'');'' 
args [None]: ''charmap'' codec can''t encode character ''\u010d'' in position 124: character maps to <undefined>'

The character that creates the issue is the č.

ISSUE TYPE

Bug Report

COMPONENT NAME

mysq_query

ANSIBLE VERSION

ansible 2.9.27
  config file = 
  configured module search path = 
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.9.9 (main, Nov 19 2021, 00:00:00) [GCC 11.2.1 20210728 (Red Hat 11.2.1-1)]

COLLECTION VERSION

ansible-collection-community-mysql-2.1.0-1.fc34.noarch

OS / ENVIRONMENT

Target OS:

RHEL 8.5
python3-PyMySQL-0.10.1-2.module+el8.4.0+9657+a4b6a102.noarch
platform-python-3.6.8-41.el8.x86_64
mariadb-server-10.3.28-1.module+el8.3.0+10472+7adc332a.x86_64

STEPS TO REPRODUCE

Just run the following task in an relevant DB.

    - name:  Execute the queries
      mysql_query:
        login_host: "{{ db_host }}"
        login_port: "{{ db_host_port if pc_db_host != 'localhost' else omit }}"
        login_unix_socket: "{{ db_login_socket | default(omit) }}"
        login_user: "{{ db_admin_user }}"
        login_password: "{{ db_admin_pass }}"
        login_db: "{{ db_name }}"
        query: "INSERT IGNORE INTO `lang` (`l_id`, `l_code`, `l_name`) VALUES (1,  'en_GB.UTF-8', 'English'),   (2,  'sl_SI.UTF-8', 'Slovenščina'), (3,  'fr_FR.UTF-8', 'Français'), (4,  'nl_NL.UTF-8', 'Nederlands'), (5,  'de_DE.UTF-8', 'Deutsch'), (6,  'pt_BR.UTF-8', 'Brazil'), (7,  'es_ES.UTF-8', 'Español'), (8,  'cs_CZ.UTF-8', 'Czech'),  (9,  'en_US.UTF-8', 'English (US)'), (10, 'ru_RU.UTF-8', 'Russian'),  (11, 'zh_CN.UTF-8', 'Chinese'),(12, 'ja_JP.UTF-8', 'Japanese');"

EXPECTED RESULTS

SQL Query should be executed without issue.