Django + MySQL - Admin Site - Add User - OperationalError - SAVEPOINT does not exist

Question

We're trying to have a custom User model and behaviors, but then we noticed that even the default Django installation has issue when adding a new User via the Django Admin:

The issue happens even in other Django versions (tried it in Django 1.8, and w/ the latest one, Django 1.11.3). Surprisingly, the issue does not happen when using SQLite or PostgreSQL databases. Also, adding user via $./manage.py createuser and programmatically will work. Editing existing uses like the previously created admin superuser via terminal will also work. CRUD mechanisms for Group work as intended, hence only the Add User view is affected.

Possible point of failures include the Django core (any version), MySQL binary (bundled in XAMPP for Mac, tried various versions also), or the MySQL-Python connector (version 1.2.5). Similar issue here, using Django 1.10 and MySQL.

Steps to replicate:

1. Install the latest Django version: $ pip install django

2. Install the Python-MySQL driver: $ pip install MySQL-python

3. Create a new project: $ django-admin.py startproject sandbox

4. Create a new database in MySQL and set the db config in settings.py

5. Migrate the Django apps' models: $ ./manage.py migrate

6. Create an admin superuser: $ ./manage.py createsuperuser

7. Run the Django's bundled server: $ ./manage.py runserver

8. Go to http://127.0.0.1:8000/admin/login, and login w/ the admin superuser credentials.

9. Try clicking the Users' Add button. The attached error in screenshot will be triggered.

Sample Database Query Logs:

Query   SET NAMES utf8
Query   set autocommit=1
Query   SELECT `django_session`.`session_key`, `django_session`.`session_data`, `django_session`.`expire_date` FROM `django_session` WHERE (`django_session`.`session_key` = 'ikql6mk9voxq4g0go9avuvuxxrpvwx9w' AND `django_session`.`expire_date` > '2017-07-10 06:58:15.823513')
Query   SELECT `auth_user`.`id`, `auth_user`.`password`, `auth_user`.`last_login`, `auth_user`.`is_superuser`, `auth_user`.`username`, `auth_user`.`first_name`, `auth_user`.`last_name`, `auth_user`.`email`, `auth_user`.`is_staff`, `auth_user`.`is_active`, `auth_user`.`date_joined` FROM `auth_user` WHERE `auth_user`.`id` = 1
Query   SAVEPOINT `s123145414516736_x1`
Query   RELEASE SAVEPOINT `s123145414516736_x1`
Query   ROLLBACK TO SAVEPOINT `s123145414516736_x1`
Query   rollback
Query   set autocommit=1
Quit

It seems that the SAVEPOINT ROLLBACK has been done after the SAVEPOINT RELEASE causing the SAVEPOINT to be missing. Based from MySQL's SavePoint docs, the natural order seems to be ROLLBACK then RELEASE.

Here's the Traceback messages. No other changes in Django's default settings.py except for the database config/credentials for connecting to MySQL server:

Environment:


Request Method: GET
Request URL: http://127.0.0.1:8000/admin/auth/user/add/

Django Version: 1.11.3
Python Version: 2.7.8
Installed Applications:
['django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware']



Traceback:

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/core/handlers/exception.py" in inner
  41.             response = get_response(request)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/core/handlers/base.py" in _get_response
  187.                 response = self.process_exception_by_middleware(e, request)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/core/handlers/base.py" in _get_response
  185.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/contrib/admin/options.py" in wrapper
  551.                 return self.admin_site.admin_view(view)(*args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
  149.                     response = view_func(request, *args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  57.         response = view_func(request, *args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/contrib/admin/sites.py" in inner
  224.             return view(request, *args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapper
  67.             return bound_func(*args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/views/decorators/debug.py" in sensitive_post_parameters_wrapper
  76.             return view(request, *args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in bound_func
  63.                 return func.__get__(self, type(self))(*args2, **kwargs2)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapper
  67.             return bound_func(*args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
  149.                     response = view_func(request, *args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in bound_func
  63.                 return func.__get__(self, type(self))(*args2, **kwargs2)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/contrib/auth/admin.py" in add_view
  103.             return self._add_view(request, form_url, extra_context)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/contrib/auth/admin.py" in _add_view
  131.                                                extra_context)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/contrib/admin/options.py" in add_view
  1508.         return self.changeform_view(request, None, form_url, extra_context)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapper
  67.             return bound_func(*args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in _wrapped_view
  149.                     response = view_func(request, *args, **kwargs)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/utils/decorators.py" in bound_func
  63.                 return func.__get__(self, type(self))(*args2, **kwargs2)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/contrib/admin/options.py" in changeform_view
  1408.             return self._changeform_view(request, object_id, form_url, extra_context)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/db/transaction.py" in __exit__
  210.                                 connection.savepoint_rollback(sid)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/db/backends/base/base.py" in savepoint_rollback
  348.         self._savepoint_rollback(sid)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/db/backends/base/base.py" in _savepoint_rollback
  308.             cursor.execute(self.ops.savepoint_rollback_sql(sid))

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  80.             return super(CursorDebugWrapper, self).execute(sql, params)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  65.                 return self.cursor.execute(sql, params)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/db/utils.py" in __exit__
  94.                 six.reraise(dj_exc_type, dj_exc_value, traceback)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  63.                 return self.cursor.execute(sql)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute
  101.             return self.cursor.execute(query, args)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/MySQLdb/cursors.py" in execute
  205.             self.errorhandler(self, exc, value)

File "/Users/ranelpadon/.virtualenvs/django__1_11/lib/python2.7/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  36.     raise errorclass, errorvalue

Exception Type: OperationalError at /admin/auth/user/add/
Exception Value: (1305, 'SAVEPOINT s123145452511232_x1 does not exist')

Hiroko

I think there is some IntegrityError. Enable logging and check. – Kendricks

thanks for your advice. I've tried enabling the MySQL logs and got more info: – Hiroko

I've edited the post also to include the MySQL logs and emphasize the SAVEPOINT workflow/order inconsistency. Could be a bug in Django core. – Hiroko

I've found that this can happen on clustered MySQL/Maria Db instances - maybe replication is slow between slaves? – Allveta

Answer 1

This bug haunted me for a long time, so I decided to dig further and try to resolve it once and for all.

Root Cause: The SAVEPOINT issue is a bug that occurs only in MySQL-Python connector.

Fix: Use other MySQL drivers for Python (e.g. mysqlclient).

Details/Findings:

• Tried the MySQL binaries in Homebrew, MAMP, and XAMPP for Mac.
• Tried various MySQL versions, 5.6 (libmysqlclient.18.dylib) and 5.7 (libmysqlclient.20.dylib).
• Tried various Python's MySQL drivers.

No relationships found by varying the MySQL binaries/versions. But I've narrowed down the issue by testing various MySQL drivers commonly used in Python:

1. MySQLdb (widely-used but old database connector, last commit was 7 years ago!):

   $ pip install MySQL-python

2. mysqlclient (modern version of MySQL-python, but w/ lots of bug fixes and improvements):

   $ pip install mysqlclient

3. PyMySQL (pure Python MySQL database driver):

   $ pip install PyMySQL

   Then, add in settings.py (just below the import os):

   try:
       import pymysql
       pymysql.install_as_MySQLdb()
   except:
       pass
   

4. MySQL-Connector-Python by Oracle (pure Python MySQL database driver):

   $ pip install mysql-connector-python-rf

   Then, edit the database's ENGINE configuration in settings.py:

   'ENGINE': 'mysql.connector.django',
   

The SAVEPOINT issue occurs only when using the MySQL-python connector (#1 driver), but not in the others (#2, #3, #4 drivers). On my case, I had chosen the mysqlclient. Issue is gone now.

Hiroko

I am seeing what looks exactly like this error with mysqlclient 1.4.2.post1 – Chinkiang

same problem with mysqlclient 1.4.6 – Luttrell

also have this issue with mysqlclient==2.0.1 – Allveta

Answer 2

I solved it by overriding UserAdmin. It seems like there is a problem with nested atomic transactions in clustered DBs.

Add the following lines to the Custom UserAdmin which inherits from base Django UserAdmin:

@sensitive_post_parameters_m
@csrf_protect_m
def add_view(self, request, form_url='', extra_context=None):
    return self._add_view(request, form_url, extra_context)

Attenuator

Answer 3

I had a project that was just upgraded from python 2 to 3 and this happened because I installed mysqlclient and that's what caused that (on python 3.5) taken from one of the suggestions above:

$ pip install PyMySQL

in settings.py added:

try:
    import pymysql
    pymysql.install_as_MySQLdb()
except:
    pass

Crappie