id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
30786	MySQL backend's has_zoneinfo_database check requires read access to mysql.time_zone	Andrew Williams	Andrew Williams	"The MySQL backend checks whether timezone information has been loaded to the database by running the query `SELECT 1 FROM mysql.time_zone LIMIT 1`:

https://github.com/django/django/blob/bae05bcf68710cb6dafa51325c3ec83ddda83c39/django/db/backends/mysql/features.py#L70-L75

This code raises the following exception when run using our production database user and host, though, because the user account doesn't have read access to the `mysql.time_zone` database table.

`OperationalError: (1142, ""SELECT command denied to user '<dbuser>'@'<dbhost>' for table 'time_zone'"")`

I encountered this issue because if the database doesn't have time zones loaded, the `CONVERT_TZ` calls that Django inserts to certain queries when `USE_TZ` is True will return NULL, causing queries to silently fail.  After loading in time zones, these CONVERT_TZ calls work as intended, even without access to `mysql.time_zone`.  See https://code.djangoproject.com/ticket/30726#comment:4 for more info.

Given that `CONVERT_TZ` works without explicit `mysql.time_zone` access, it'd probably make sense to replace the existing implementation of `has_zoneinfo_database` with:

{{{
#!python
    @cached_property
    def has_zoneinfo_database(self):
        # Test if the time zone definitions are installed.
        with self.connection.cursor() as cursor:
            cursor.execute(""SELECT CONVERT_TZ('2019-09-19 1:00:00', 'UTC', 'UTC')"")
            return cursor.fetchone()[0] is not None
}}}

I can submit a PR with this change if you'd like.  Alternatively, with the existing code, it'd be worth documenting somewhere that for MySQL backends, having access to `mysql.time_zone` is required."	Bug	closed	Database layer (models, ORM)	3.0	Normal	fixed			Ready for checkin	1	0	0	0	0	0