decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>] with mysqlclient > 1.4.6

[coleifer]

Describe the bug

A user reported an issue on Peewee when attempting to create tables here: coleifer/peewee#2812

The SQL that triggered the error:

SELECT table_name
FROM information_schema.tables 
WHERE table_schema = DATABASE() AND table_type != 'VIEW' 
ORDER BY table_name

The traceback:

Exception has occurred: InvalidOperation       (note: full exception trace is shown but execution is paused at: _run_module_as_main)
[<class 'decimal.ConversionSyntax'>]
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\MySQLdb\cursors.py", line 328, in _fetch_row
    return self._result.fetch_row(size, self._fetch_type)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\MySQLdb\cursors.py", line 355, in _post_get_result
    self._rows = self._fetch_row(0)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\MySQLdb\cursors.py", line 321, in _query
    self._post_get_result()
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    res = self._query(query)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 3252, in execute_sql
    cursor.execute(sql, params or ())
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 4214, in get_tables
    return [table for table, in self.execute_sql(query, ('VIEW',))]
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 3419, in table_exists
    return table_name in self.get_tables(schema=schema)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 6934, in table_exists
    return cls._schema.database.table_exists(M.table.__name__, M.schema)
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 6944, in create_table
    and cls.table_exists():
  File "C:\Users\tom\AppData\Local\Programs\Python\Python39\Lib\site-packages\peewee.py", line 3441, in create_tables
    model.create_table(**options)

A similar issue has been reported on saltstack: saltstack/salt#65414

Environment

• Windows 11
• Python 3.9.5 x64
• MariaDB 11.1
• mysqlclient 2.0.3

How did you install libmysqlclient libraries?

No response

What version of mysqlclient do you use?

2.0.3

Minimum but complete code to reproduce

import peewee as pw


db = pw.MySQLDatabase('testdb',
                    user='testdb',
                    #password='',
                    host='127.0.0.1',
                    port=3306)

# db = pw.SqliteDatabase('people.db')

class BaseModel(pw.Model):
    class Meta:
        database = db

class User(BaseModel):
    username = pw.CharField(unique=True)
    email = pw.CharField(unique=True)
    password = pw.CharField()


class Tweet(BaseModel):
    user = pw.ForeignKeyField(User, backref='tweets')
    message = pw.TextField()
    created_date = pw.DateTimeField()

db.connect()
db.create_tables([User, Tweet])

Commands, and any other step required to reproduce your issue.

No other information was provided to me beyond the versions the user had. The query I pasted above was issued using pymysql. No schema should be necessary, as the issue seemed to manifest before any schema was created.

[methane]

I can not reproduce it. Your script runs successfully.

[methane]

• Is it reproducible in Linux or mac? I use Windows a few times a year. So Windows-only bug will need very long time to fix.
• Is it reproducible with mysqlclient 2.2.0?
• Please provide complete step to reproduce. All step is important. Especially, you didn't write "How did you install libmysqlclient libraries?" part. It is essential to this library because this library is just a wrapper of C library.

[coleifer]

@tomtechnologies come answer these questions.

[zeromind]

I can reproduce the issue using the oraclelinux:8 Docker image, and a manually installed Oracle Linux 8 in a VM, and the code from above running in Python 3.9. As well as with SQLAlchemy instead of peewee.

• Oracle Linux 8 using mysqlclient 2.2.4 + mariadb-connector-c-3.1.11-2.el8_3.x86_64 connecting to mariadb:latest with version 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 => ERROR
• Oracle Linux 8 using mysqlclient 2.2.4 + mariadb-connector-c-3.1.11-2.el8_3.x86_64 connecting to mariadb (via OS package) with version 10.3.39-MariaDB => OK
• oraclelinux:9 using mysqlclient 2.2.4 + mariadb-connector-c-3.2.6-1.el9_0.x86_64 connecting to mariadb:latest with version 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 => OK

Maybe something trips over the weird version string? or 10.x vs 11.x

EDIT: using mariadb:lts (currently 10.11) for our automated tests instead of mariadb:latest (11.3 atm) via ionos-cloud/dim#273 now, and the issue is gone.

Dockerfile:

FROM oraclelinux:8

RUN dnf install --assumeyes gcc python39-devel mariadb-devel git python39 make tar gzip
RUN python3 -m venv virtual_env
RUN ./virtual_env/bin/pip3 install 'peewee' 'mysqlclient>=1.4.6'

as well as using SQLAlchemy:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine('mysql+mysqldb://dim:[email protected]/dim', echo=True)
Base.metadata.create_all(engine) # fails
Session = sessionmaker(bind=engine)
db_session = Session()

using Dockerfile

FROM oraclelinux:8

RUN dnf install --assumeyes gcc python39-devel mariadb-devel git python39 make tar gzip
RUN python3 -m venv virtual_env

RUN ./virtual_env/bin/pip3 install 'SQLAlchemy~=1.4' 'Flask~=2.1' 'Flask-SQLAlchemy~=2.5' 'mysqlclient>=1.4.6'

Using database from mariadb:latest Docker image.

MariaDB [(none)]> SELECT VERSION();
+---------------------------------------+
| VERSION()                             |
+---------------------------------------+
| 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 |
+---------------------------------------+
1 row in set (0.001 sec)

Using oraclelinux:9 works with Dockerfile:

FROM oraclelinux:9

RUN dnf install --assumeyes gcc python3-devel git python3 make tar gzip mariadb-connector-c-devel
RUN python3 -m venv virtual_env

RUN ./virtual_env/bin/pip3 install 'SQLAlchemy~=1.4' 'Flask~=2.1' 'Flask-SQLAlchemy~=2.5' 'mysqlclient>=1.4.6'

Version info:
oraclelinux:8

[root@dim_test /]# rpm -qa | grep -e mariadb -e mysql
mariadb-connector-c-devel-3.1.11-2.el8_3.x86_64
mariadb-connector-c-config-3.1.11-2.el8_3.noarch
mariadb-devel-10.3.39-1.module+el8.8.0+21165+f6462f70.x86_64
mariadb-connector-c-3.1.11-2.el8_3.x86_64
[root@dim_test /]# ./virtual_env/bin/pip3 freeze
blinker==1.8.2
click==8.1.7
flask==2.3.3
Flask-SQLAlchemy==2.5.1
greenlet==3.0.3
importlib-metadata==7.1.0
itsdangerous==2.2.0
jinja2==3.1.4
MarkupSafe==2.1.5
mysqlclient==2.2.4
SQLAlchemy==1.4.52
werkzeug==3.0.3
zipp==3.19.0

oraclelinux:9

[root@dim_test /]# rpm -qa | grep -e mariadb -e mysql
mariadb-connector-c-config-3.2.6-1.el9_0.noarch
mariadb-connector-c-3.2.6-1.el9_0.x86_64
mariadb-connector-c-devel-3.2.6-1.el9_0.x86_64
[root@dim_test /]# ./virtual_env/bin/pip3 freeze
blinker==1.8.2
click==8.1.7
Flask==2.3.3
Flask-SQLAlchemy==2.5.1
greenlet==3.0.3
importlib_metadata==7.1.0
itsdangerous==2.2.0
Jinja2==3.1.4
MarkupSafe==2.1.5
mysqlclient==2.2.4
SQLAlchemy==1.4.52
Werkzeug==3.0.3
zipp==3.19.0

[methane]

Thank you for detailed information. I can reproduce the issue. But I am not sure what is happening.
It seems bug in mariadb client lib or server.

# Regular case.

In [1]: import MySQLdb
In [2]: con = MySQLdb.connect(host="127.0.0.1", port=3306, database="hoge", user="root")
In [3]: con.query("SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type != 'VIEW' ORDER BY table_name")
In [4]: result = con.store_result()
In [6]: result.describe()
Out[6]: (('table_name', 253, 5, 256, 256, 0, 0),)
In [7]: result.fetch_row()
Out[7]: (('tweet',),)

The second item in the nested tuple is 253, it means VAR_STRING. So, it decodes "tweet". On the other hand:

# error case
>>> import MySQLdb
>>> con = MySQLdb.connect(host="10.88.0.2")
>>> con.query("SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type != 'VIEW' ORDER BY table_name")
>>> result = con.store_result()
>>> result.describe()
(('table_name', 0, 0, 16384, 16384, 16, 0),)
>>> result.fetch_row()
()

The second item in the tuple is 0. It means Decimal. Cursor.fetchall() try to call Decimal('tweet') and cause error.

>>> from decimal import Decimal
>>> Decimal("tweet")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>]

I don't know why this happens. Maybe, mariadb client/server protocol mismatch? But I am not sure.
mysqlclient just uses mysql_store_result() and mysql_fetch_fields().

Please ask support for MariaDB or Oracle. I don't have time to dig into this.
My only advice is don't mix legacy software and latest, non-LTS software. It may cause this type of troubles.