Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#26113 closed Bug (invalid)

Django 1.9 DateTimeField issue with MySQL 5.6+ and MySQL Connector/Python

Reported by: Ziad Badawi Owned by: nobody
Component: Utilities Version: 1.9
Severity: Normal Keywords: STRICT_TRANS_TABLES, Data truncated
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Running migrate on a fresh Django 1.9 install with the default apps generates 

C:\code\project>python manage.py migrate
Operations to perform:
  Apply all migrations: contenttypes, auth, sessions, admin
Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial...Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\mysql\connector\django\base.py", line 177, in _execute_wrapper
    return method(query, args)
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 488, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DataError: 1292 (22007): Incorrect datetime value: '2016-01-21 08:57:19.442777+00:00' for column 'applied' at row 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "C:\Python34\lib\site-packages\django\core\management\__init__.py", line 353, in execute_from_command_line
    utility.execute()
  File "C:\Python34\lib\site-packages\django\core\management\__init__.py", line 345, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Python34\lib\site-packages\django\core\management\base.py", line 348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Python34\lib\site-packages\django\core\management\base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "C:\Python34\lib\site-packages\django\core\management\commands\migrate.py", line 200, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "C:\Python34\lib\site-packages\django\db\migrations\executor.py", line 92, in migrate
    self._migrate_all_forwards(plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "C:\Python34\lib\site-packages\django\db\migrations\executor.py", line 121, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "C:\Python34\lib\site-packages\django\db\migrations\executor.py", line 204, in apply_migration
    self.recorder.record_applied(migration.app_label, migration.name)
  File "C:\Python34\lib\site-packages\django\db\migrations\recorder.py", line 73, in record_applied
    self.migration_qs.create(app=app, name=name)
  File "C:\Python34\lib\site-packages\django\db\models\query.py", line 401, in create
    obj.save(force_insert=True, using=self.db)
  File "C:\Python34\lib\site-packages\django\db\models\base.py", line 700, in save
    force_update=force_update, update_fields=update_fields)
  File "C:\Python34\lib\site-packages\django\db\models\base.py", line 728, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "C:\Python34\lib\site-packages\django\db\models\base.py", line 812, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "C:\Python34\lib\site-packages\django\db\models\base.py", line 851, in _do_insert
    using=using, raw=raw)
  File "C:\Python34\lib\site-packages\django\db\models\manager.py", line 122, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "C:\Python34\lib\site-packages\django\db\models\query.py", line 1039, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "C:\Python34\lib\site-packages\django\db\models\sql\compiler.py", line 1060, in execute_sql
    cursor.execute(sql, params)
  File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "C:\Python34\lib\site-packages\mysql\connector\django\base.py", line 227, in execute
    return self._execute_wrapper(self.cursor.execute, query, new_args)
  File "C:\Python34\lib\site-packages\mysql\connector\django\base.py", line 195, in _execute_wrapper
    utils.DatabaseError(err.msg), sys.exc_info()[2])
  File "C:\Python34\lib\site-packages\django\utils\six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "C:\Python34\lib\site-packages\mysql\connector\django\base.py", line 177, in _execute_wrapper
    return method(query, args)
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 488, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
django.db.utils.DatabaseError: Incorrect datetime value: '2016-01-21 08:57:19.442777+00:00' for column 'applied' at row 1

By default, MySQL 5.6+ has STRICT_TRANS_TABLES enabled in the sql_mode configuration. The DateTime field in MySQL does not support the format used in models.DateTimeField which is basically MySQL format +TIMEZONE OFFSET.

So in this case, disabling STRICT_TRANS_TABLES should fix the issue, but it didn't.
After another migrate, another error appeared

C:\code\project>python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial...Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\mysql\connector\django\base.py", line 177, in _execute_wrapper
    return method(query, args)
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 434, in _handle_result
    self._handle_noresultset(result)
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 404, in _handle_noresultset
    self._warnings[0][1], self._warnings[0][2])
mysql.connector.errors.DatabaseError: 1265: Data truncated for column 'applied' at row 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "C:\Python34\lib\site-packages\django\core\management\__init__.py", line 353, in execute_from_command_line
    utility.execute()
  File "C:\Python34\lib\site-packages\django\core\management\__init__.py", line 345, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Python34\lib\site-packages\django\core\management\base.py", line 348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Python34\lib\site-packages\django\core\management\base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "C:\Python34\lib\site-packages\django\core\management\commands\migrate.py", line 200, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "C:\Python34\lib\site-packages\django\db\migrations\executor.py", line 92, in migrate
    self._migrate_all_forwards(plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "C:\Python34\lib\site-packages\django\db\migrations\executor.py", line 121, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "C:\Python34\lib\site-packages\django\db\migrations\executor.py", line 204, in apply_migration
    self.recorder.record_applied(migration.app_label, migration.name)
  File "C:\Python34\lib\site-packages\django\db\migrations\recorder.py", line 73, in record_applied
    self.migration_qs.create(app=app, name=name)
  File "C:\Python34\lib\site-packages\django\db\models\query.py", line 401, in create
    obj.save(force_insert=True, using=self.db)
  File "C:\Python34\lib\site-packages\django\db\models\base.py", line 700, in save
    force_update=force_update, update_fields=update_fields)
  File "C:\Python34\lib\site-packages\django\db\models\base.py", line 728, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "C:\Python34\lib\site-packages\django\db\models\base.py", line 812, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "C:\Python34\lib\site-packages\django\db\models\base.py", line 851, in _do_insert
    using=using, raw=raw)
  File "C:\Python34\lib\site-packages\django\db\models\manager.py", line 122, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "C:\Python34\lib\site-packages\django\db\models\query.py", line 1039, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "C:\Python34\lib\site-packages\django\db\models\sql\compiler.py", line 1060, in execute_sql
    cursor.execute(sql, params)
  File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "C:\Python34\lib\site-packages\django\db\backends\utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "C:\Python34\lib\site-packages\mysql\connector\django\base.py", line 227, in execute
    return self._execute_wrapper(self.cursor.execute, query, new_args)
  File "C:\Python34\lib\site-packages\mysql\connector\django\base.py", line 195, in _execute_wrapper
    utils.DatabaseError(err.msg), sys.exc_info()[2])
  File "C:\Python34\lib\site-packages\django\utils\six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "C:\Python34\lib\site-packages\mysql\connector\django\base.py", line 177, in _execute_wrapper
    return method(query, args)
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 515, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 434, in _handle_result
    self._handle_noresultset(result)
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 404, in _handle_noresultset
    self._warnings[0][1], self._warnings[0][2])
django.db.utils.DatabaseError: Data truncated for column 'applied' at row 1

Change History (8)

comment:1 by Tim Graham, 9 years ago

Which specific version of MySQL 5.6 is it? I'm using the default on Ubuntu 14.04 (5.6.27) without any problems and our continuous integration also uses this version. Thanks.

comment:2 by Ziad Badawi, 9 years ago

I am currently using 5.7.10

I just mentioned 5.6+ as having the STRICT_TRANS_TABLES enabled by default.

comment:3 by Tim Graham, 9 years ago

Can you reproduce the issue with django.db.backends.mysql instead of mysql.connector.django? The latter is a third-party project, so if not, the bug report should go to its authors.

comment:4 by Ziad Badawi, 9 years ago

Since I am on Windows 7, I was not able to get any MySQL connector other than mysql.connector.django to work.

comment:5 by Tim Graham, 9 years ago

Resolution: → invalid
Severity: Release blocker → Normal
Status: new → closed
Summary: Django 1.9 DateTimeField issue with MySQL 5.6+ → Django 1.9 DateTimeField issue with MySQL 5.6+ and MySQL Connector/Python

According to ​the release notes for MySQL Connector/Python 2.1.3 (latest release as of now), support for Django 1.8 was added in that version. It seems like Django 1.9 isn't supported yet. I tried running Django's test suite with it and it wouldn't start with Django 1.9 or later.

comment:6 by Ziad Badawi, 9 years ago

OK, what can I do now in order to start a project with Django 1.9 and MySQL 5.7.10 on Windows 7 ?

comment:7 by Claude Paroz, 9 years ago

Isn't ​mysqlclient available on Windows 7?

comment:8 by Tim Graham, 9 years ago

The Windows wheel files are provided only for Python 2. There's ​an issue about Python 3 support.

Note: See TracTickets for help on using tickets.
Back to Top