Database returned an invalid value in QuerySet.dates()
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
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
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
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 inC:\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.
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