Opened 11 years ago

Closed 10 years ago

#21163 closed Bug (duplicate)

MySQL backend: when settings.DEBUG is True, and the model instance's creation leads to a query that triggers a warning, the transaction stays uncommitted.

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I'm using Django 1.5.2, Python 3.2.3 and MySQL-python 1.2.3.
My test application's models.py is as follows:

from django.db import models

class Foo(models.Model):
    fff = models.CharField(max_length=10)


class Bar(models.Model):
    fff = models.CharField(max_length=10)
    foos = models.ManyToManyField(Foo, related_name='bars')

I create an empty DB for the project, then enter the shell and do the following:

└─[$] <git:(master*)> ./manage.py shell                         
Python 3.2.3 (default, Apr 10 2013, 06:11:55) 
Type "copyright", "credits" or "license" for more information.

IPython 0.13.2 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: from djangofoo.core import models

In [2]: foo = models.Foo.objects.create(fff='aaa')

In [3]: for f in models.Foo.objects.all():
   ...:     print(f.id, f.fff)
   ...:     
1 aaa

In [4]: foo = models.Foo.objects.create(fff='1234567890')

In [5]: for f in models.Foo.objects.all():
    print(f.id, f.fff)
   ...:     
1 aaa
2 1234567890

In [6]: foo = models.Foo.objects.create(fff='12345678901')
--------------------------------------------------------------------------
Warning                                   Traceback (most recent call last)
/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/core/management/commands/shell.py in <module>()
----> 1 foo = models.Foo.objects.create(fff='12345678901')

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/models/manager.py in create(self, **kwargs)
    147 
    148     def create(self, **kwargs):
--> 149         return self.get_query_set().create(**kwargs)
    150 
    151     def bulk_create(self, *args, **kwargs):

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/models/query.py in create(self, **kwargs)
    414         obj = self.model(**kwargs)
    415         self._for_write = True
--> 416         obj.save(force_insert=True, using=self.db)
    417         return obj
    418 

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/models/base.py in save(self, force_insert, force_update, using, update_fields)
    544 
    545         self.save_base(using=using, force_insert=force_insert,
--> 546                        force_update=force_update, update_fields=update_fields)
    547     save.alters_data = True
    548 

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/models/base.py in save_base(self, raw, cls, origin, force_insert, force_update, using, update_fields)
    648 
    649                 update_pk = bool(meta.has_auto_field and not pk_set)
--> 650                 result = manager._insert([self], fields=fields, return_id=update_pk, using=using, raw=raw)
    651 
    652                 if update_pk:

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/models/manager.py in _insert(self, objs, fields, **kwargs)
    213 
    214     def _insert(self, objs, fields, **kwargs):
--> 215         return insert_query(self.model, objs, fields, **kwargs)
    216 
    217     def _update(self, values, **kwargs):

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/models/query.py in insert_query(model, objs, fields, return_id, raw, using)
   1673     query = sql.InsertQuery(model)
   1674     query.insert_values(fields, objs, raw=raw)
-> 1675     return query.get_compiler(using=using).execute_sql(return_id)
   1676 
   1677 

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/models/sql/compiler.py in execute_sql(self, return_id)
    935         cursor = self.connection.cursor()
    936         for sql, params in self.as_sql():
--> 937             cursor.execute(sql, params)
    938         if not (return_id and cursor):
    939             return

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/backends/util.py in execute(self, sql, params)
     39         start = time()
     40         try:
---> 41             return self.cursor.execute(sql, params)
     42         finally:
     43             stop = time()

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/django/db/backends/mysql/base.py in execute(self, query, args)
    118     def execute(self, query, args=None):
    119         try:
--> 120             return self.cursor.execute(query, args)
    121         except Database.IntegrityError as e:
    122             six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py in execute(self, query, args)
    185             self.errorhandler(self, exc, value)
    186         self._executed = query
--> 187         if not self._defer_warnings: self._warning_check()
    188         return r
    189 

/home/foobar/.virtualenvs/env1/lib/python3.2/site-packages/MySQL_python-1.2.3-py3.2-linux-x86_64.egg/MySQLdb/cursors.py in _warning_check(self)
     89                     self.messages.append((self.Warning, w))
     90                 for w in warnings:
---> 91                     warn(w[-1], self.Warning, 3)
     92             elif self._info:
     93                 self.messages.append((self.Warning, self._info))

Warning: Data truncated for column 'fff' at row 1

In [7]: for f in models.Foo.objects.all():
    print(f.id, f.fff)
   ...:     
1 aaa
2 1234567890
3 1234567890

In [8]: foo = models.Foo.objects.create(fff='321')

In [9]: for f in models.Foo.objects.all():
    print(f.id, f.fff)
   ...:     
1 aaa
2 1234567890
3 1234567890
4 321

As you see, the code in the shell reads all the entities from the DB just fine.

But, when I queried the database via the console client before "In [8]", I got:

mysql> select * from core_foo;
+----+------------+
| id | fff        |
+----+------------+
|  1 | aaa        |
|  2 | 1234567890 |
+----+------------+
2 rows in set (0.00 sec)

And after "In [8]":

mysql> select * from core_foo;
+----+------------+
| id | fff        |
+----+------------+
|  1 | aaa        |
|  2 | 1234567890 |
|  3 | 1234567890 |
|  4 | 321        |
+----+------------+
4 rows in set (0.00 sec)

As you see, the entity for which warning was issued is unavailable until another one is saved properly. I think this happens because teh transaction never gets commited after the INSERT-query issued after models.Foo.objects.create(fff='12345678901') triggers an error. This only happens when settings.DEBUG is True. When it's switched to False, the text gets silently truncated, and the new data can be seen in the console client right away.

Change History (4)

comment:1 by Aymeric Augustin, 11 years ago

Triage Stage: UnreviewedAccepted

comment:2 by Ilja Maas, 10 years ago

This actually is 'on purpose' or 'by design'. (at least it seams like)
see lines 53-55 of django/db/backends/mysql/base.py

# Raise exceptions for database warnings if DEBUG is on
if settings.DEBUG:
    warnings.filterwarnings("error", category=Database.Warning)

However I think we could question this. Why should a warning ever be promoted to an Exception?
changing "error" to "default" stops the warnings from being promoted.

In case of being truncation warnings like this, I even think the warninglevel should be 'always' and not suppressing any sequential warning, as each warning is actually truncating another record.

comment:3 by Ilja Maas, 10 years ago

Will be fixed by ticket #23871

comment:4 by Tim Graham, 10 years ago

Resolution: duplicate
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top