MySQL 8.0 RDS – Fixing Error 1022 Duplicate Key in Table

MySQLmysql-8.0

Documenting this error and solution here in case it could help anyone.
We have a MYSQL 8 database on Amazon RDS.
The below group by used to work just fine on Mysql 5.7 but it started giving
1022 Can't write; duplicate key in table '/rdsdbdata/tmp/#sqlf80_1656bc_0'
error once we upgraded to Mysql 8.

GROUP BY DATE_FORMAT(CONCAT(YEAR(performance_stats_date),'-',  
MONTH(performance_stats_date),'-',DAY(performance_stats_date)),
 '%Y-%m-%d')

Best Answer

That's the wrong way to use DATE_FORMAT. It is given a DATE or DATETIME and a string providing the 'formatting'. Example:

mysql> SELECT DATE_FORMAT(NOW(), "%Y-%m-%d"), DATE(NOW());
+--------------------------------+-------------+
| DATE_FORMAT(NOW(), "%Y-%m-%d") | DATE(NOW()) |
+--------------------------------+-------------+
| 2019-01-30                     | 2019-01-30  |
+--------------------------------+-------------+

In your case, it would be something like 

  GROUP BY DATE_FORMAT(performance_stats_date, "%Y-%m-%d")

But this is even simpler:

  GROUP BY DATE(performance_stats_date)

For further discussion, please provide

SHOW VARIABLES LIKE 'char%'
SHOW CREATE TABLE` for each table.
the entire query
Related Question