Bug #91281 Mysql Connector Python doesn't convert cDecimal properly
Submitted: 15 Jun 2018 11:41 Modified: 26 Apr 2022 16:28
Reporter: Marcin Lulek Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S1 (Critical)
Version:8.x, 2.x, 8.0.11, 2.17 OS:Any
Assigned to: CPU Architecture:Any

[15 Jun 2018 11:41] Marcin Lulek
Description:
Hello,

It seems that 8.x connector does not support cDecimal conversion properly.

The posted code in 2.1.7 driver will return [(None,)] as the result.
With 8.x driver exception will be thrown.

Traceback (most recent call last):
  File "test.py", line 13, in <module>
    cursor.execute(query, (Decimal('1.5'),))
  File "/home/vagrant/env/local/lib/python2.7/site-packages/mysql/connector/cursor_cext.py", line 246, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/home/vagrant/env/local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 514, in prepare_for_mysql
    result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Python type cdecimal.Decimal cannot be converted

How to repeat:
import mysql.connector
from decimal import Decimal # this works
from cdecimal import Decimal #this doesn't
import datetime

cnx = mysql.connector.connect(
    user='test', password='test',
    host='127.0.0.1',
    database='test')

query = ("SELECT %s")
cursor = cnx.cursor()
cursor.execute(query, (Decimal('1.5'),))
print(cursor.fetchall())

cnx.close()

Suggested fix:
Support cDecimal type same way as Decimal type.
[15 Jun 2018 12:37] Marcin Lulek
2.x versions are affected too
[15 Jun 2018 12:37] Marcin Lulek
I should note that 2.x returning None is also a bug in this regard?
[18 Jun 2018 9:07] MySQL Verification Team
Hello Marcin,

Thank you for the report.
I'm not seeing any exceptions but [(None,)] in both the cases.

Thanks,
Umesh
[18 Jun 2018 9:07] MySQL Verification Team
##
root@ArtfulAardvark:/home/ushastry# pip3 install m3-cdecimal
Collecting m3-cdecimal
  Downloading https://files.pythonhosted.org/packages/38/48/f971b928e29d7c163dd01f265c294417dfe898bd07a1... (639kB)
    100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 645kB 192kB/s 
Building wheels for collected packages: m3-cdecimal
  Running setup.py bdist_wheel for m3-cdecimal ... done
  Stored in directory: /root/.cache/pip/wheels/a9/4e/f3/0b1acc4efe5587ac08806ed5d82ec784d8c4c1c6b06ff15c67
Successfully built m3-cdecimal
Installing collected packages: m3-cdecimal
Successfully installed m3-cdecimal-2.3
root@ArtfulAardvark:/home/ushastry# 

## 8.0.11
tar -zxvf mysql-connector-python-8.0.11.tar.gz
/usr/bin/python3.6 setup.py install --prefix=/home/ushastry/Downloads/connector-python-8_0_11/
cd /home/ushastry/Downloads/connector-python-8_0_11/
vi 91281.py
PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py

-- from cdecimal import Decimal #this doesn't
root@ArtfulAardvark:/home/ushastry/Downloads/connector-python-8_0_11# PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py
[(None,)]

-- from decimal import Decimal # this works
root@ArtfulAardvark:/home/ushastry/Downloads/connector-python-8_0_11# PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py
[('1.5',)]

## 2.1.7

tar -zxvf mysql-connector-python-2.1.7.tar.gz
/usr/bin/python3.6 setup.py install --prefix=/home/ushastry/Downloads/connector-python-2_1_7/
cd /home/ushastry/Downloads/connector-python-2_1_7
vi 91281.py
PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py

-- from cdecimal import Decimal #this doesn't
root@ArtfulAardvark:/home/ushastry/Downloads/connector-python-2_1_7# PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py
[(None,)]

-- from decimal import Decimal # this works
root@ArtfulAardvark:/home/ushastry/Downloads/connector-python-2_1_7# PYTHONPATH="./lib/python3.6/site-packages/" /usr/bin/python3.6 91281.py
[('1.5',)]
[18 Jun 2018 16:06] Marcin Lulek
Interesting, this is what I get on ubuntu 17.10 and python Python 3.6.3

ergo@ergo-desktop:~/workspace/oracle$ python3 -m venv env3
ergo@ergo-desktop:~/workspace/oracle$ env3/bin/pip install mysql-connector-python m3-cdecimal
..... removed ....
Failed to build m3-cdecimal
Installing collected packages: six, protobuf, mysql-connector-python, m3-cdecimal
  Running setup.py install for m3-cdecimal ... done
Successfully installed m3-cdecimal-2.3 mysql-connector-python-8.0.11 protobuf-3.6.0 six-1.11.0
ergo@ergo-desktop:~/workspace/oracle$ env3/bin/python test.py
Traceback (most recent call last):
  File "test.py", line 13, in <module>
    cursor.execute(query, (Decimal('1.5'),))
  File "/home/ergo/workspace/oracle/env3/lib/python3.6/site-packages/mysql/connector/cursor_cext.py", line 246, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/home/ergo/workspace/oracle/env3/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 514, in prepare_for_mysql
    result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Python type cdecimal.Decimal cannot be converted

For Python 2.7:

ergo@ergo-desktop:~/workspace/oracle$ virtualenv env27
Running virtualenv with interpreter /usr/bin/python2
New python executable in /home/ergo/workspace/oracle/env27/bin/python2
Also creating executable in /home/ergo/workspace/oracle/env27/bin/python
Installing setuptools, pkg_resources, pip, wheel...done.
ergo@ergo-desktop:~/workspace/oracle$ env27/bin/pip install mysql-connector-python m3-cdecimal
Collecting mysql-connector-python
  Using cached https://files.pythonhosted.org/packages/dc/48/32c715d2cef42d0791c5b2f21b4f1f280c8e45afa66a...
Collecting m3-cdecimal
Collecting protobuf>=3.0.0 (from mysql-connector-python)
  Using cached https://files.pythonhosted.org/packages/27/e7/bf96130ebe633b08a3913da4bb25e50dac5779f1f68e...
Collecting six>=1.9 (from protobuf>=3.0.0->mysql-connector-python)
  Using cached https://files.pythonhosted.org/packages/67/4b/141a581104b1f6397bfa78ac9d43d8ad29a7ca43ea90...
Requirement already satisfied: setuptools in ./env27/lib/python2.7/site-packages (from protobuf>=3.0.0->mysql-connector-python) (39.2.0)
Installing collected packages: six, protobuf, mysql-connector-python, m3-cdecimal
Successfully installed m3-cdecimal-2.3 mysql-connector-python-8.0.11 protobuf-3.6.0 six-1.11.0
ergo@ergo-desktop:~/workspace/oracle$ env27/bin/python test.py
Traceback (most recent call last):
  File "test.py", line 13, in <module>
    cursor.execute(query, (Decimal('1.5'),))
  File "/home/ergo/workspace/oracle/env27/local/lib/python2.7/site-packages/mysql/connector/cursor_cext.py", line 246, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/home/ergo/workspace/oracle/env27/local/lib/python2.7/site-packages/mysql/connector/connection_cext.py", line 514, in prepare_for_mysql
    result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Python type cdecimal.Decimal cannot be converted
[19 Jun 2018 20:20] Lukasz Fidosz
Hello Umesh,
It appears you have installed connector-python without C extensions, C extensions are default in latest version but you still need to pass --with-mysql-capi=/path_to_your/mysql_config to enable them, hence you experiencing different results than Marcin - None instead of errors, which is incorrect behaviour anyway in my opinion.

The error seems to be caused by the way we are testing for decimal: https://github.com/mysql/mysql-connector-python/blob/8.0.11/src/mysql_capi.c#L1804 I would propose to use PyObject_IsInstance instead, example patch:

diff --git a/src/mysql_capi.c b/src/mysql_capi.c
index c8839a8..dec304e 100644
--- a/src/mysql_capi.c
+++ b/src/mysql_capi.c
@@ -1719,7 +1719,7 @@ MySQL_ping(MySQL *self)
 PyObject*
 MySQL_convert_to_mysql(MySQL *self, PyObject *args)
 {
-    PyObject *value, *new_value;
+    PyObject *value, *new_value, *mod_decimal;
     PyObject *prepared, *quoted;
     int i;
     Py_ssize_t size;
@@ -1795,24 +1795,24 @@ MySQL_convert_to_mysql(MySQL *self, PyObject *args)
         else if (PyDelta_CheckExact(value))
         {
             new_value= pytomy_timedelta(value);
-#ifndef PY3
-        }
-        else if (strcmp((value)->ob_type->tp_name, "Decimal") == 0)
-        {
-#else
-        }
-        else if (strcmp((value)->ob_type->tp_name, "decimal.Decimal") == 0)
-        {
-#endif
-            new_value= pytomy_decimal(value);
         }
         else
-        {
-            PyOS_snprintf(error, 100,
-                          "Python type %s cannot be converted",
-                          (value)->ob_type->tp_name);
-            PyErr_SetString(MySQLInterfaceError, (const char*)error);
-            goto error;
+       {
+           mod_decimal= PyImport_ImportModule("decimal");
+            if ((mod_decimal) && (PyObject_IsInstance(value, PyObject_GetAttrString(mod_decimal, "Decimal"))))
+            {
+               new_value= pytomy_decimal(value);
+            }
+           else
+            {
+               Py_XDECREF(mod_decimal);  // i don't like this being repeated twice :|
+                PyOS_snprintf(error, 100,
+                   "Python type %s cannot be converted",
+                   (value)->ob_type->tp_name);
+                   PyErr_SetString(MySQLInterfaceError, (const char*)error);
+                   goto error;
+            }
+            Py_XDECREF(mod_decimal);
         }
 
         if (!new_value)

That will fix it in python 2 in scenarios when cdecimal is used as drop in replacement for decimal, as in by doing:  sys.modules['decimal'] = cdecimal

Such approach is for example suggested by sqlalchemy: http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Numeric

In python3 decimal is based on cdecimal implementation so no need to use external package, but this also makes use of the same code base for both lines.

Example code ran after following patch is applied:
import sys
import cdecimal
sys.modules['decimal'] = cdecimal
from decimal import Decimal
import mysql.connector
cnx = mysql.connector.connect(user='test', password='test', host='127.0.0.1', database='test')
query = ("SELECT %s")
cursor = cnx.cursor()
cursor.execute(query, (Decimal('1.5'),))
print(cursor.fetchall())
[(u'1.5',)]
[20 Jun 2018 4:48] MySQL Verification Team
Thank you,Lukasz.  
Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA).
For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team - https://dev.mysql.com/community/

Regards,
Umesh
[26 Apr 2022 16:19] Nuno Mariz
Posted by developer:
 
cdecimal has been integrated into CPython 3.3, where it supersedes the pure Python version: import decimal will automatically import the C version.