MySQL backend's has_zoneinfo_database check requires read access to mysql.time_zone

[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:

    @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.

[Claude Paroz]

Your proposal makes sense.

[Andrew Williams]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3346b78a:

Fixed #30786 -- Used CONVERT_TZ to check if the time zone definitions are installed on MySQL.

Replaced a timezone check in the MySQL backend with one that doesn't
require access to the mysql.time_zone database.

[Mariusz Felisiak <felisiak.mariusz@…>]

In def1857d:

[3.0.x] Fixed #30786 -- Used CONVERT_TZ to check if the time zone definitions are installed on MySQL.

Replaced a timezone check in the MySQL backend with one that doesn't
require access to the mysql.time_zone database.

Backport of 3346b78a8a872286a245d1e77ef4718fc5e6be1a from master