Django Mysql Database returned an invalid datetime value

Question

I have a Django app. I am using MySql server running in docker container as a database. After just moved to a custom User model. Now i am getting those errors: Environment:

Request Method: GET
Request URL: http://127.0.0.1:8000/dashboard/

Django Version: 1.10.3
Python Version: 3.5.2
Installed Applications:
['grappelli',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.messages',
 'django.contrib.sessions',
 'django.contrib.staticfiles',
 'django.contrib.sites',
 'corsheaders',
 'rest_framework',
 'rest_framework.authtoken',
 'oauth2_provider',
 'social.apps.django_app.default',
 'rest_framework_social_oauth2',
 'rest_framework_swagger',
 'accounts',
 'dashboard',
 'items',
 'storages',
 'userprofile',
 'common',
 'registration',
 'debug_toolbar',
 'django_extensions']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'corsheaders.middleware.CorsMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.auth.middleware.SessionAuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'debug_toolbar.middleware.DebugToolbarMiddleware']



Traceback:

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/core/handlers/exception.py" in inner
  39.             response = get_response(request)

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/core/handlers/base.py" in _legacy_get_response
  249.             response = self._get_response(request)

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/core/handlers/base.py" in _get_response
  187.                 response = self.process_exception_by_middleware(e, request)

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/core/handlers/base.py" in _get_response
  185.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/contrib/auth/decorators.py" in _wrapped_view
  23.                 return view_func(request, *args, **kwargs)

File "/home/kuba/code/VeeU/Server/dashboard/views.py" in index
  66.                         Bookmark.objects.datetimes('date_bookmarked', 'day')]

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/db/models/query.py" in __iter__
  256.         self._fetch_all()

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/db/models/query.py" in _fetch_all
  1087.             self._result_cache = list(self.iterator())

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/db/models/query.py" in __iter__
  155.         for row in compiler.results_iter():

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/db/models/sql/compiler.py" in results_iter
  795.                     row = self.apply_converters(row, converters)

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/db/models/sql/compiler.py" in apply_converters
  779.                 value = converter(value, expression, self.connection, self.query.context)

File "/home/kuba/.virtualenvs/VeeU/lib/python3.5/site-packages/django/db/models/functions/datetime.py" in convert_value
  181.                         "Database returned an invalid datetime value. "

Exception Type: ValueError at /dashboard/
Exception Value: Database returned an invalid datetime value. Are time zone definitions for your database and pytz installed?

I tryed to run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql but it does not work. I also tried to set default-time-zone = 'UTC' in /etc/mysql/my.cnf in container.

Edit:

I also have pytz installed

pip freeze | grep pytz
pytz==2016.7

Answer 1

I had the same issue with django and mysql on Debian Linux and was able to solve it by running this command, which populates the time_zone table in the (config) database named mysql:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Answer 2

The MySQL database has tables of timezone information that seems to be missing. You can re-populate the database with:

mysql_tzinfo_to_sql /usr/share/zoneinfo | sed -e "s/Local time zone must be set--see zic manual page/local/" | mysql -u root mysql

Then,

mysql -u root -p -e "flush tables;" mysql