date_hierarchy with USE_TZ=True won't work with Amazon RDS

[Ben Lee]

I got this error in the Django admin when upgrading Django to 1.6:

ValueError: Database returned an invalid value in QuerySet.datetimes(). Are time zone definitions
for your database and pytz installed?

when rendering this:

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

From to Django 1.6 release notes:

Time zone-aware day, month, and week_day lookups
Django 1.6 introduces time zone support for day, month, and week_day lookups when USE_TZ is
True. These lookups were previously performed in UTC regardless of the current time zone.
This requires time zone definitions in the database. If you’re using SQLite, you must install pytz. If
you’re using MySQL, you must install pytz and load the time zone tables with mysql_tzinfo_to_sql.

You're supposed to run this command to load tz data into the "mysql" db.

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

but if you're hosted with Amazon RDS, you don't have root access and the user (created when launching the RDS instance) doesn't have access to the "mysql" database.

ERROR 1044 (42000) at line 1: Access denied for user '<user>'@'%' to database 'mysql'

FWIW, the Django admin worked fine with Amazon RDS in Django 1.4, and now RDS users may be effectively blocked from upgrading Django versions.

[Aymeric Augustin]

FWIW, the Django admin worked fine with Amazon RDS in Django 1.4, and now RDS users may be effectively blocked from upgrading Django versions.

Actually ;-) one feature of the Django admin, date_hierarchy, didn't work correctly on Django 1.4 when USE_TZ was enabled. This limitation was documented.

For users of MySQL, the fix introduced in Django 1.6 requires a ​generally available, documented feature which unfortunately happens not to be available on Amazon RDS.

You may be able to account for this limitation of MySQL on Amazon RDS by removing the date_hierarchy from affected admins. However that may be impractical if third-party apps are affected.

I'm not sure how we could improve the situation. I wouldn't like to return invalid results silently, like Django 1.4 did, for all MySQL users just because those who host their database on RDS suffer from some limitations.

[Tim Graham]

Some sort of toggle to disable date_hierarchy for an AdminSite (or globally) could be an option. I guess we already have this through the ability to override the admin template. Would overriding the template and making {% block date_hierarchy %} an empty block fix the issue? If so, should we document it as a solution?

[Adam Johnson]

I've been running Django 1.6+ fine on RDS MySQL (5.5) for over a year and a half, in two separate deployments. The timezone tables are preloaded for you, iirc. If you log in with your master user and run SHOW TABLES IN `mysql`; you should see them. You may need to run the following grants to your application's username to give it access to read the data:

GRANT SELECT ON `mysql`.`time_zone` TO 'application'@'%';
GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'application'@'%';
GRANT SELECT ON `mysql`.`time_zone_name` TO 'application'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition` TO 'application'@'%';
GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'application'@'%';

[Tim Graham]

Closing as "won't fix" given Adam's comment. Feel free to reopen if you can provide more details to indicate that he's incorrect.