community.mysql
community.mysql copied to clipboard
ansible-collections
UTF-8 characters in SQL Query result to errors
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.
ACTUAL RESULTS
SQL Execution fails.
a part of the discussion is in https://github.com/ansible-collections/community.mysql/pull/142
@itcultus thanks for reporting this! As a workaround i would suggest trying the mysqlclient connector instead of PyMySQL. Could you please try it and let us know that it helps?
mysqlclient is an actively maintained connector (by the same folks who are developing PyMSQL IIRC).
Absolutely! How ? :) Apologies for this so basic question, but I think I need some guidance on this. (And I promise to update the documentation with it!)
It's just a regular pypi package available for installation via pip https://pypi.org/project/mysqlclient/. So i would remove PyMySQL first (via pip also), then would install mysqlclient and try
https://github.com/ansible-collections/community.mysql/pull/142#issuecomment-819333437 and below explains the result of our investigation
I don't use pip for system packages, but there is an RPM from EPEL, so, I will try this one. It's not the latest version (1.4.6) instead of 2.1, but let's give it a try.
Sounds good, thanks. The essence of the result is that PyMySQL works a bit strange with Python3