Boolean False becomes NULL with recent mysql-connector-python

[Johannes la Poutre]

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

[Johannes la Poutre]

Quick note: 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!

[Carlton Gibson]

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

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

[Carlton Gibson]

...this is their bug...

OK, thanks Adam. Closing on that basis.

[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