Opened 8 years ago

Closed 8 years ago

#24616 closed Cleanup/optimization (wontfix)

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

Reported by: Ben Lee Owned by: nobody
Component: contrib.admin Version: 1.6
Severity: Normal Keywords: pytz, mysql_tzinfo_to_sql, USE_TZ
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (4)

comment:1 Changed 8 years ago by 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.

comment:2 Changed 8 years ago by Tim Graham

Component: Uncategorizedcontrib.admin
Summary: Django 1.6 admin with USE_TZ=True won't work with Amazon RDSdate_hierarchy with USE_TZ=True won't work with Amazon RDS
Triage Stage: UnreviewedAccepted
Type: UncategorizedCleanup/optimization

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?

comment:3 Changed 8 years ago by 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'@'%';

comment:4 Changed 8 years ago by Tim Graham

Resolution: wontfix
Status: newclosed

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.

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