dumpdata generate a backup not usable with loaddata using MySQL

[David CHANIAL]

Introduction

Hi !

---

I have two problem :

• The first one is that django produce me a record using DateTimeField with a value of "0000-00-00 00:00:00.000000", i never update database content without using django orm api. For this ticket, i tried 2 hours to reproduce this problem, but i haven't. But i think that is there a different bug than the second one :

• The second one, which permit me to discover the first one, is that dumpdata does not raise an error when dumping this kind of record. So, when using loaddata later, an error happend, failing to restore this record.

---

I'm using a MySQL database with server : 10.1.21-MariaDB and Django-1.9.12
During my tests, it's seems (unconfirmed) that SQLite has not the same behavior.

The bug

• As said previously, no matter how the record in the database is weird or invalid, i think django MUST fail during dumpdata and not during loaddata when django can detect (as here) that produced data are not loadable.

Reproduce the bug

• In an existing MySQL Django project named - for example - bobb :

cd bobb
python manage.py startapp campanella
echo 'INSTALLED_APPS = list(INSTALLED_APPS) + ["campanella"]' >> bobb/settings.py
export DJANGO_SETTINGS_MODULE=bobb.settings

cat >campanella/models.py  <<'EOF'
from __future__ import unicode_literals
from django.db import models

class Interpretation(models.Model):
    player = models.CharField(max_length=200)
    inserted_on = models.DateTimeField(auto_now_add=True)
EOF

python manage.py makemigrations campanella
python manage.py migrate campanella

mysql -udev_mybb -p -S/var/lib/mysql/mysql.sock db_dev_mybb -e 'INSERT INTO campanella_interpretation (player, inserted_on) VALUES ("Lang Lang", "0000-00-00 00:00:00.000000");'

python manage.py dumpdata campanella.interpretation --indent 4 > db.json

cat db.json
[
{
    "model": "campanella.interpretation",
    "pk": 1,
    "fields": {
        "player": "Lang Lang",
        "inserted_on": null
    }
}
]

python -c 'import django;django.setup();from campanella.models import Interpretation; Interpretation.objects.all().delete()'

python manage.py loaddata db.json
Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/core/management/__init__.py", line 353, in execute_from_command_line
    utility.execute()
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/core/management/__init__.py", line 345, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/core/management/base.py", line 348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/core/management/base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/core/management/commands/loaddata.py", line 60, in handle
    self.loaddata(fixture_labels)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/core/management/commands/loaddata.py", line 100, in loaddata
    self.load_label(fixture_label)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/core/management/commands/loaddata.py", line 158, in load_label
    obj.save(using=self.using)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/core/serializers/base.py", line 201, in save
    models.Model.save_base(self.object, using=using, raw=True, **kwargs)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/models/base.py", line 736, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/models/base.py", line 820, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/models/base.py", line 859, in _do_insert
    using=using, raw=raw)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/models/manager.py", line 122, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/models/query.py", line 1039, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 1060, in execute_sql
    cursor.execute(sql, params)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 117, in execute
    six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 112, in execute
    return self.cursor.execute(query, args)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/dev_mybb/virtualenv/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
django.db.utils.IntegrityError: Problem installing fixture '/home/dev_mybb/bobb/db.json': Could not load campanella.Interpretation(pk=1): (1048, "Column 'inserted_on' cannot be null")

[Tim Graham]

Thanks for the detailed report.

My understanding is that the zero datetime values aren't permitted if strict mode is enabled (and it's recommended for Django as of #15940). Given that and that dumpdata isn't meant as a backup tool (databases have their own tool for that), I think this is too much of an edge case to add some workaround in Django.

[Adam Johnson]

I agree with Tim, the zero datetime is a weird mysql historical value. I highly recommend you remove it and turn on strict mode.