date_hierarchy in admin reports an error with mysql-connector-python 8.0.28.

[Eric Pan]

\site-packages\django\db\backends\mysql\operations.py line 58

def date_trunc_sql(self, lookup_type, field_name, tzname=None):
        field_name = self._convert_field_to_tz(field_name, tzname)
        fields = {
            'year': '%%Y-01-01',
            'month': '%%Y-%%m-01',
        }  # Use double percents to escape.

the problem is here.

   'year': '%%Y-01-01',
            'month': '%%Y-%%m-01',

this will generate the SQL.

SELECT DISTINCT CAST(DATE_FORMAT([table name], '%%Y-01-01') AS DATE) AS `datefield

the format ''%%Y-01-01'' is not right for mysql.

[Tim Graham]

This might be a bug in mysql-connector-python and/or mysql.connector.django which aren't part of Django itself.

What does "the format ... is not right" mean? What's the error?

[Eric Pan]

the error message is:

django.db.utils.DatabaseError: (1292, "1292: Incorrect datetime value: '%Y-01-01'", None)

and the sql text sent to mysql db is 'SELECT DISTINCT CAST(DATE_FORMAT([table name], '%%Y-01-01') AS DATE) AS `datefield

it seems the double %% not be escaped to single % in the final sql text sent to mysql.

I don not know it's the issue of mysql-connector-python or Django it self.

because the sql was generated in django module.
\site-packages\django\db\backends\mysql\operations.py line 58

[Mariusz Felisiak]

Everything works fine with the recommended ​mysqlclient driver. mysql-connector-python includes its ​own adapter 'mysql.connector.django' which may not support the most recent releases of Django (as ​documented). You can try to report this issue in MySQL bugtracker.