Opened 5 years ago

Closed 5 years ago

Last modified 4 years ago

#30469 closed Bug (invalid)

Boolean False becomes NULL with recent mysql-connector-python

Reported by: Johannes la Poutre Owned by: nobody
Component: Database layer (models, ORM) Version: 2.2
Severity: Normal Keywords:
Cc: Adam Johnson Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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
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'],
    }
}

Change History (5)

comment:1 by Johannes la Poutre, 5 years ago

Just noticed that reverting to mysql-connector-python 8.0.12 is not a solution as this driver indeed is very buggy with Python 3.7 (foreign key constraints are not properly resolved).

I'm willing to take 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!

Last edited 5 years ago by Johannes la Poutre (previous) (diff)

comment:2 by Carlton Gibson, 5 years ago

Cc: Adam Johnson added

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...

comment:3 by Adam Johnson, 5 years ago

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.

comment:4 by Carlton Gibson, 5 years ago

Resolution: invalid
Status: newclosed

...this is their bug...

OK, thanks Adam. Closing on that basis.

comment:5 by Dima German, 4 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top