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]

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!

[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