CODEWITHSUNDEEP
mysqldjangotimezonemezzanine
qliq
qliq

Reputation: 11807

Database returned an invalid value in QuerySet.dates()

I get this error on my Ubuntu 12.04 machine with mysql 5.5 after I imported some Wordpress content to Mezzanine's blog_blogpost.

ValueError at /admin/blog/blogpost/
Database returned an invalid value in QuerySet.dates(). Are time zone definitions and pytz installed?

Request Method: GET
Request URL:    http://127.0.0.1:8000/admin/blog/blogpost/
Django Version: 1.6.1
Exception Type: ValueError
Exception Value:    
Database returned an invalid value in QuerySet.dates(). Are time zone definitions and pytz installed?
Exception Location: /home/me/.mezenv/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py in results_iter, line 1107
Python Executable:  /home/me/.mezenv/bin/python
Python Version: 2.7.3
Python Path:    
[u'/home/me',
 '/home/me/sai',
 '/home/me/.mezenv/local/lib/python2.7/site-packages/distribute-0.6.24-py2.7.egg',
 '/home/me/.mezenv/local/lib/python2.7/site-packages/pip-1.1-py2.7.egg',
 '/home/me/.mezenv/lib/python2.7',
 '/home/me/.mezenv/lib/python2.7/plat-linux2',
 '/home/me/.mezenv/lib/python2.7/lib-tk',
 '/home/me/.mezenv/lib/python2.7/lib-old',
 '/home/me/.mezenv/lib/python2.7/lib-dynload',
 '/usr/lib/python2.7',
 '/usr/lib/python2.7/plat-linux2',
 '/usr/lib/python2.7/lib-tk',
 '/home/me/.mezenv/local/lib/python2.7/site-packages']
Server time:    Sat, 25 Jan 2014 13:44:11 +0100


Error during template rendering

In template /home/me/.mezenv/local/lib/python2.7/site-packages/grappelli_safe/templates/admin/change_list.html, error at line 140

I added to my local_settings.py 

import pytz
from pytz import *

and also defined timezone in my.cnf [mysqld]

default-time-zone = "+01:00"

but I still get the error unitl I get the line in template:

the tag is:

140 {% block date_hierarchy %}{% date_hierarchy cl %}{% endblock %}

Appreciate your help to resolve it.

Upvotes: 60

Views: 25592

Answers (8)

Tushar Das
Tushar Das

Reputation: 1

10-04-2024

Today I faced this problem:

“ValueError: Database returned an invalid datetime value. Are time zone definitions for your database installed?”

After a bit of digging, I understood how should I install time zone definitions for my MySql database.

Here I present a solution for Windows users:

Step1:

Download a package from https://dev.mysql.com/downloads/timezones.html, it contains time zone description tables which can be used with the MySQL server by users whose systems do not include zoneinfo files (e.g. Microsoft Windows, HP-UX).

Step2:

Extract the Zip file, inside it you will find a folder containing a .sql file.

Step3:

Open CMD.

Run cd C:\Program Files\MySQL\MySQL Server 8.0\bin\ (If you have installed MySql in your machine)

Or,

Run cd C:\xampp\mysql\bin\ (If you are using XAMPP)

Step4:

Run mysql -u root -p mysql < Absolute_path_to_downloaded_.sql_file

Step5:

(Restart your MySql server)

If you have installed MySql separately, follow 3 steps below,

  • Run “services.msc” in CMD.
  • Look for the service called MySQL80.
  • Right click on it, click on restart.

For XAMPP users, use XAMPP control panel.

Upvotes: 0

Alexander Klimenko
Alexander Klimenko

Reputation: 2280

Looks like error caused Django's 1.6 timezone functionality changes. The docs now mention this error specifically (bug report, link to docs).

You have to load timezone tables into mysql (http://dev.mysql.com/doc/refman/5.6/en/mysql-tzinfo-to-sql.html). Try execute on your database server:

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

And then run "flush tables" or "flush query cache", otherwise the problem may not disappear even though you've loaded the correct timezone data:

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

updated by @qris

Upvotes: 123

Tom
Tom

Reputation: 22831

For Windows, take the following steps:

  • stop the service
  • download the files provided by MySQL but note they go in
    C:\ProgramData\MySQL\MySQL Server VERSION\data\mysql
    not in
    C:\Program Files\MySQL\MySQL Server VERSION\data
    at least on Windows 7 as of 2014.
  • I also modified C:\Program Files\MySQL\MySQL Server VERSION\my.ini to add default-time-zone = 'UTC' per the tips here. Then restart the service.

Upvotes: 9

eliksir jouvence
eliksir jouvence

Reputation: 563

Loading Time Zone for Mysql solved the problem for me. If you are on windows, follow these instructions :

Step #1: Download the package that contains the data files of pre-built time zone tables. http://dev.mysql.com/downloads/timezones.html

Step #2: Unzip the downloaded ZIP file to a folder on your Desktop.

Step #3: Stop MySQL Server.

For Xampp and Wamp use their GUI or stop the mysql service from the Task Manager.

Step #4: Open mysql subdirectory of your MySQL server's data directory.

For me it is C:\xampp\mysql\data\mysql

Step #5: Overwrite time zone data files with the downloaded version.

Copy all 15 data files from your unzipped folder and paste into data folder for the mysql system database. Overwrite all these .frm, .MYD, and .MYI files.

Step #6: Restart MySQL server.

And the job is done :-)

source : http://www.geeksengine.com/article/populate-time-zone-data-for-mysql.html

Upvotes: 1

Artur Barseghyan
Artur Barseghyan

Reputation: 14152

What worked for me:

1. Populate the timezone definitions in the 'mysql' table

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

2. Flush tables

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

3. Restart mysql

sudo service mysql restart

Upvotes: 20

Flimm
Flimm

Reputation: 150493

The Django docs actually talk about this problem:

I get an error “Are time zone definitions for your database and pytz installed?” pytz is installed, so I guess the problem is my database?

If you are using MySQL, see the Time zone definitions section of the MySQL notes for instructions on loading time zone definitions.

If you follow that link, you get this advice:

If you plan on using Django’s timezone support, use mysql_tzinfo_to_sql to load time zone tables into the MySQL database. This needs to be done just once for your MySQL server, not per database.

Here's the command:

mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql mysql

Upvotes: 2

markwalker_
markwalker_

Reputation: 12849

For anyone else passing through here with the same issue on Mavericks I was getting the error mentioned by Anton whereby I kept getting;

ERROR 1406 (22001) at line 38981: Data too long for column 'Abbreviation' at row 1

So I used mysql_tzinfo_to_sql tz_file tz_name from the MySQL docs to load the specific time zones that I wanted;

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

And now Django isn't returning errors so I'm happy :D

Upvotes: 6

Anton Danylchenko
Anton Danylchenko

Reputation: 2358

For MacOS users I found solution here (in comment):

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

Because on MacOS we have error looks like this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u roomysql

Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
ERROR 1406 (22001) at line 38981: Data too long for column 'Abbreviation' at row 1

Upvotes: 16

Related Questions