[Django] #30469: Boolean False becomes NULL with recent mysql-connector-python
Django
-------------------------------------+-------------------------------------
Reporter: squio | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Bug is triggered from Admin / User / Change Password
{{{
Request URL: http://localhost/admin/auth/user/3/password/
Django Version: 2.1.8
Exception Type:
Exception Value: Column 'is_superuser' cannot be null
Exception Location: /usr/local/lib/python3.7/site-
packages/mysql/connector/cursor_cext.py in statement, line 608
Python Executable: /usr/local/bin/python
Python Version: 3.7.3
Python Path:
['/usr/src/app',
'/usr/local/lib/python37.zip',
'/usr/local/lib/python3.7',
'/usr/local/lib/python3.7/lib-dynload',
'/usr/local/lib/python3.7/site-packages']
}}}
Database is Mariadb latest version from dockerhub.
This bug only occurs when using recent mysql-connector-python > 8.0.12
Tested with the following combinations:
Django 2.2.0, 2.2.1, 2.1.8 with mysql-connector-python == 8.0.15
Django 2.2.0 with mysql-connector-python 8.0.15, 8.0.14, 8.0.13
Django 2.2.0 with mysql-connector-python 8.0.16 fails completely (not
further investigated)
Bug is **NOT** triggered with **Django 2.2.1** with mysql-connector-python
**8.0.12** (or 8.0.11)
Further investigation:
The SQL which is used for the successful database update (copied from
django-debug-toolbar) is:
{{{
UPDATE `auth_user` SET `password` = 'pbkdf2_sha256$...', `last_login` =
NULL, `is_superuser` = 0, `username` = 'a', `first_name` = '', `last_name`
= '', `email` = '', `is_staff` = 0, `is_active` = 1, `date_joined` =
'2019-05-09 14:36:09' WHERE `auth_user`.`id` = 3
}}}
The SQL from failed database update (copied from debug web page) is:
{{{
UPDATE `auth_user` SET `password` = 'pbkdf2_sha256$...=', `last_login` =
NULL, `is_superuser` = NULL, `username` = 'a', `first_name` = '',
`last_name` = '', `email` = '', `is_staff` = NULL, `is_active` = True,
`date_joined` = '2019-05-09 14:36:09' WHERE `auth_user`.`id` = 3
}}}
The difference is value 0 (zero) for False (correct) vs NULL for False
(not correct obviously)
The database schema for `auth_user` as created by running the migrations:
{{{
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| password | varchar(128) | NO | | NULL | |
| last_login | datetime | YES | | NULL | |
| is_superuser | tinyint(1) | NO | | NULL | |
| username | varchar(150) | NO | UNI | NULL | |
| first_name | varchar(30) | NO | | NULL | |
| last_name | varchar(150) | NO | | NULL | |
| email | varchar(254) | NO | | NULL | |
| is_staff | tinyint(1) | NO | | NULL | |
| is_active | tinyint(1) | NO | | NULL | |
| date_joined | datetime | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
}}}
While this mysql-connector-python version 8.0.12 appears to work I feel
that this is a high risk solution as the
[https://dev.mysql.com/doc/relnotes/connector-python/en/news-8-0-13.html
changelog for mysql-connector-python] states that this version is updated
for Python 3.7 compatibility.
my pip3 requirements.txt (mysql connector downgraded):
{{{
Django==2.2.1
gunicorn==19.9.0
mysql-connector-python==8.0.12
djangorestframework~=3.9
markdown~=3.1
django-filter~=2.1
django-allauth~=0.39
coreapi==2.3.3
PyYAML==5.1
django-tables2~=2.0
django-debug-toolbar~=1.1
}}}
Database in `settings.py`:
{{{
DATABASES = {
'default': {
'ENGINE': 'mysql.connector.django',
'NAME': os.environ['MYSQL_DATABASE'],
'USER': os.environ['MYSQL_USER'],
'PASSWORD': os.environ['MYSQL_PASSWORD'],
'HOST': os.environ['DB_SERVICE'],
'PORT': os.environ['DB_PORT'],
}
}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30469>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Johannes la Poutre):
Quick note: I'm willing to tale this issue but need some hints where to
look, e.g.
- how does resolving default values for field types work in connection
with the database column type?
- how are DB specific column types mapped to model field types?
- which django packages and modules are involved?
Thanks!
--
Ticket URL: <https://code.djangoproject.com/ticket/30469#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Adam (Chainz) Johnson (added)
Comment:
> I'm willing to take this issue but need some hints where to look...
I guess we may need to document what versions to use but, is this not an
issue with mysql-connector-python rather than Django?
What do they say on their issue tracker? Is there a related issue there?
cc-ing Adam, who knows more here...
--
Ticket URL: <https://code.djangoproject.com/ticket/30469#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Adam (Chainz) Johnson):
I've never used the Oracle driver and I trust it less than mysqlclient (or
PyMySQL, which it would be nice to get Django to support officially).
I do believe this is their bug, you should be able to create a test case
with a simple query like `cursor.execute("SELECT %s", [False])`. The
connector page ( https://dev.mysql.com/downloads/connector/python/ ) says
to report through the MySQL bug tracker.
--
Ticket URL: <https://code.djangoproject.com/ticket/30469#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
> ...this is their bug...
OK, thanks Adam. Closing on that basis.
--
Ticket URL: <https://code.djangoproject.com/ticket/30469#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Dima German):
A workaround for this bug: add {{{ 'use_pure': True }}} to database
OPTIONS.
This forces mysql-connector-python to use pure python connection instead
of C extension, where I believe the bug lives.
{{{
DATABASES = {
'default': {
'ENGINE': 'mysql.connector.django',
'NAME': os.environ['MYSQL_DATABASE'],
'USER': os.environ['MYSQL_USER'],
'PASSWORD': os.environ['MYSQL_PASSWORD'],
'OPTIONS': {
'use_pure': True,
}
}
}}}
P.S. the bug in the mysql bugtracker:
https://bugs.mysql.com/bug.php?id=92001
--
Ticket URL: <https://code.djangoproject.com/ticket/30469#comment:5>